MySQL-04b-SQL加锁分析

分类于
字数统计: 745

在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。
分析SQL语句加锁情况，还要考虑几种情况：当前用什么引擎（InnoDB/MyISAM）？你要当前系统的隔离级别是什么？ id列是不是主键？ id列如果不是主键，那么id列上有索引吗？id列上如果有二级索引，那么这个索引是唯一索引吗？

➤ SQL语句在不同隔离级别的加锁分析:

  • 如果是 Serializable 级别, 防止了脏读, 读加S锁, 写加X锁;
  • 如果是RC级别, 当前读允许有幻读, 所以上面的”非唯一索引的情形没有加Gap锁;
  • 如果是RR级别: (RR级别解决了幻读问题, 事务中两次当前读, 读出来的数目一致), 以 update T1 set id = 100 where name = ‘d’ 为例, 下面为了简化分析, 只考虑 where =的更新, 不考虑 where between范围更新 :
    • 如果 where条件是=主键: 聚簇索引上加X锁, 由于主键索引的唯一性, 只有一行加X锁;
    • 如果 where条件是=唯一索引, 但非主键: 普通索引上加X锁(只有一行), 聚簇索引对应的行加X锁;
    • 如果 where条件是=非唯一索引, 但非主键: 由于where索引是非唯一, where索引上可能有多行符合条件, 每行都需要加X锁, 同时行之间还需要加 Gap锁 (间隙锁, 解决幻读), 聚簇索引对应的行都要加X锁;
    • 如果 where条件上没有索引: 会导致聚簇索引上每行都加X锁( 相当于锁住了整个表), 并且聚簇索引每行之间都加 Gap锁;
    • 综上, update语句的条件是where=主键时, 需要的锁最少, 如果 update语句的 where条件是无索引列, 会引起整个主键索引树的每行都加X锁, 性能消耗非常大

➤ 锁优化建议

  1. 合理设计索引，让 InnoDB 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行。
  2. 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
  3. 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
  4. 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本。

@ref: