之前我们已经了解了InnoDB各种不同类型的锁,那么,不同SQL语句各加了什么样的锁呢?话不啰嗦,请看下文:

一、不同的SQL加了什么样的锁?

前面我们说过“加什么样的锁”与以下因素相关

1. 当前事务的隔离级别

2. SQL是一致性非锁定读(consistent nonlocking read)还是DML或锁定读(locking read)

3. SQL执行时是否使用了索引,所使用索引的类型(主键索引,辅助索引、唯一索引)

我们来看一下,不同的隔离级别下,使用不同的索引时,分别加什么锁。在讨论之前,我们先剔除无需讨论的情况

首先,普通SELECT 使用一致性非锁定读,因此根本不存在锁。无需讨论;

再者,作为开发者,我们几乎从来不会使用到隔离级别RU和Serializable。这两个隔离级别无需讨论。

于是,剩下的就是 给定锁定读SELECT或DML(INSERT/UPDATE/DELETE)语句,在不同隔离级别下,使用不同类型的索引时,分别会加什么样的锁?直接给出答案,其加锁原则如下

一、RR时,如果使用非唯一索引进行搜索或扫描,则在所扫描的每一个索引记录上都设置next-key lock

这里“所扫描的每一个索引记录”是指当扫描执行计划中所使用的索引时,搜索遇到的每一条记录。WHERE条件是否排除掉某个数据行并没有关系,InnoDB并不记得确切的WHERE条件,InnoDB倔强的只认其扫描的索引范围(index range) 。

你可能觉得InnoDB在设置锁时蛮不讲理,竟然不管WHERE条件排除掉的某些行,这不是大大增加了锁的范围了嘛。不过,等我们了解了MySQL执行SQL时的流程,这就好理解了。MySQL的执行计划只会选择一个索引,使用一个索引来进行扫描,MySQL执行SQL语句的流程是,先由InnoDB引擎执行索引扫描,然后,把结果返回给MySQL服务器,MySQL服务器会再对该索引条件之外的其他查询条件进行求值,从而得到最终结果集,而加锁时只考虑InnoDB扫描的索引,由MySQL服务器求值的其他WHERE条件并不考虑。当然,MySQL使用index_merge优化时会同时使用多个索引的,不过,这个时候设置锁时也并不特殊,同样,对于所用到的每一个索引,InnoDB在所扫描的每一个索引记录上都设置next-key lock。

加的锁一般是next-key lock,这种锁住了索引记录本身,还锁住了每一条索引记录前面的间隙,从而阻止其他事务 向 索引记录前面紧接着的间隙中插入记录。

如果在搜索中使用了辅助索引(secondary index),并且在辅助索引上设置了行锁,则,InnoDB还会在 相应的 聚集索引 上设置锁;表未定义聚集索引时,InnoDB自动创建隐藏的聚集索引(索引名字是GEN_CLUST_INDEX),当需要在聚集索引上设置锁时,就设置到此自动创建的索引上。

二、RR时

如果使用了唯一索引的唯一搜索条件,InnoDB只在满足条件的索引记录上设置index record lock,不锁定索引记录前面的间隙;如果用唯一索引作范围搜索,依然会锁定每一条被扫描的索引记录前面的间隙,并且再在聚集索引上设置锁。

三、RR时,在第一个不满足搜索条件的索引记录上设置gap lock或next-key lock。

一般,等值条件时设置gap lock,范围条件时设置next-key lock。此gap lock或next-key lock锁住第一个不满足搜索条件的记录前面的间隙。

四、RR时,INSERT在插入新行之前,必须首先为表上的每个索引设置insert intention lock

每个insert intention lock的范围都是(待插入行的某索引列的值, 此索引上从待插入行给定的值向下的第一个索引值)。只有当insert intention lock与某个gap lock或next-key lock冲突时,才能在performance_schema.data_locks看到insert intention lock。

五、RC时

InnoDB只在完全满足WHERE条件的行上设置index record lock。

六、RC时,禁用了gap lock

正因为此,RC时不存在gap lock或next-key lock。这是为什么呢?我们想一想啊,gap lock是用来解决phantom row问题的,gap lock封锁的区间内不能插入新的行,因为插入时的insert intention lock会和gap lock冲突,从而阻止了新行的插入。但,隔离级别RC是允许phantom row的,因此RC时gap lock是被禁用的。

七、RR或RC时,对于主键或唯一索引,当有重复键错误(duplicate-key error)时,会在 重复的索引记录上 设置 shared next-key lock或shared index record lock。这可能会导致死锁

假设T1, T2, T3三个事务,T1已经持有了X锁,T2和T3发生了重复键错误,因此T2和T3都在等待获取S锁,这个时候,当T1回滚或提交释放掉了X锁,则T2和T3就都获取到了S锁,并且,T2和T3都请求X锁,“T2和T3同时持有S锁,且都在请求X锁”,于是死锁就产生了。

好了,规则都列出来了,是时候实践下。下面在展示锁时,我们同时指出了当前所使用的隔离级别,表上的索引以及事务的SQL语句。

实践一:

搜索时无法使用索引,即全表扫描时,InnoDB在表的全部行上都加锁

图13:full_table_scan.png

上图演示了:搜索条件无法使用索引时,InnoDB不得不在表的全部行上都加锁。所以,索引实在太重要了,查询时,它能加快查询速度;更新时,除了快速找到指定行,它还能减少被锁定行的范围,提高插入时的并发性。

实践二:

唯一索引和非唯一索引、等值查询和范围查询加锁的不同

图14:locks_of_kinds_of_indices.png

示例1演示了:使用非唯一索引 idx_c 搜索或扫描时,InnoDB要锁住索引本身,还要锁住索引记录前面的间隙,即next-key lock: X 和 gap lock: X,GAP。next-key lock既锁住索引记录本身,还锁住该索引记录前面的间隙,gap lock只锁住索引记录前面的间隙。等值条件时,在最后一个不满足条件的索引记录上设置gap lock。

示例2演示了:使用唯一索引 iux_b 的唯一搜索条件,即,使用唯一索引执行等值查找时,InnoDB只需锁住索引本身,即index record lock: X, REC_NOT_GAP,并不锁索引前面的间隙。

示例3演示了:使用唯一索引 iux_b 进行范围扫描时,依然需要锁定扫描过的每一个索引记录,并且锁住每一条索引记录前面的间隙,即next-key lock: X。范围条件时,在最后一个不满足条件的索引记录上设置next-key lock。

实践三    :

不同隔离级别加锁的不同

无论何种隔离级别,SQL语句执行时,都是先由InnoDB执行索引扫描,然后,返回结果集给MySQL服务器,MySQL服务器再对该索引条件之外的其他查询条件进行求值,从而得到最终结果集。

图15:isolation_level_locks.png

上图中,在不同的隔离级别下,执行了相同的SQL。无论何种隔离级别,PRIMARY上的index record lock总是会加的,我们不讨论它。在idx_b上,隔离级别为RC时,InnoDB加了index record lock,即:X,REC_NOT_GAP,隔离级别为RR时,InnoDB加了next-key lock,即X。注意:RC时没有gap lock或next-key lock哦。

上图演示了:事务的隔离级别也会影响到设置哪种锁。如我们前面所说,gap lock是用来阻止phantom row的,而RC时是允许phantom row,所以,RC时禁用了gap lock。因此,上图中,RC时没有在索引上设置gap lock或next-key lock。

实践四:

操作不存在的索引记录时,也需要加锁

图16:index_record_value_not_exists.png

上图中,idx_b上并不存在b=266的索引记录,那么,当更新b=266的记录时,是否需要加锁呢?是的,也需要加锁

上图演示了:操作不存在的索引记录时,也需要加锁。

实践五:

重复键错误(duplicate-key error)时,会加共享锁。这可能会导致死锁。

图17:rollback_deadlock.png

对于主键或唯一索引,当有重复键错误(duplicate-key error)时,会在 重复的索引记录上 设置 shared next-key lock或shared index record lock。这可能会导致死锁。

上图演示了:T1在主键1上设置exclusive index record lock。T2和T3插入时,会产生重复键错误,于是T2和T3都在主键1上设置了shared next-key lock。如上图所示

如果此时,T1 rollback释放掉其所持有的index record lock,则T2和T3等待获取的shared next-key lock都成功了,然后,T2和T3争夺主键1上的index record lock,于是T2和T3就死锁了,因为它俩都持有shard next-key lock,双方谁都不会放弃已经得到的shared next-key lock,于是,谁都无法得到主键1的index record lock。

需要明确的是死锁的可能性并不受隔离级别的影响,因为隔离级别改变的是读操作的行为,而死锁是由于写操作产生的。死锁并不可怕,MySQL会选择一个牺牲者,然后,在系统变量innodb_lock_wait_timeout指定的秒数达到后,自动回滚牺牲者事务;从MySQL5.7开始,新加入了系统变量innodb_deadlock_detect(默认ON),如果开启此变量,则MySQL不会再等待,一旦探测到死锁,就立即回滚牺牲者事务。

图18:commit_deadlock.png

上图演示了:在上图的状态下,当T1 commit时,T1释放了主键1上的index record lock,于是T2和T3等待获取的shared next-key lock都成功了,然后,T2和T3争夺主键1上的index record lock,于是T2和T3死锁了,因为它俩都持有shard next-key lock,双方谁都不会放弃已经得到的shared next-key lock,于是,谁都无法得到主键1的index record lock。

二、performance_schema.data_locks中能看到全部的锁吗?

显而易见,performance_schema.data_locks并未显示全部的锁,那么,它显示了哪些锁呢?文档(-information-schema-transactions.html)说:“事务持有的每一个锁以及事务被阻塞的每一个锁请求,都在该表中占据一行”,但,我们很多例子都表明,它并未显示全部的锁。根据试验猜测performance_schema.data_locks显示的是WHERE条件所触碰到的索引上的锁,“WHERE条件所触碰到的索引”是指SQL实际执行时所使用的索引,也就是SQL执行计划的key列所显示的索引,正因为此,INSERT时看不到任何锁,update g set a=a+1 where b=22时只看到idx_b上的锁。需要强调的是,这是我处自己试验并猜测的,并未在文档中看到这种说法。

假设T1和T2两个事务操作同一个表,先执行T1,此时尽管performance_schema.data_locks中只显示T1的WHERE条件所触碰到的索引上的锁,但是,事实上在T1的WHERE条件触碰不到的索引上,也是会设置锁的。尽管表的索引idx并未被T1所触碰到,即performance_schema.data_locks显示T1在索引idx并没有设置任何锁,但,当T2执行 锁定读/插入/更新/删除 时触碰到了索引idx,T2才恍然发现,原来T1已经在索引idx上加锁了。

我们来看下面的三个例子

示例一:

“performance_schema.data_locks无法看到全部锁”

图19:which_locks_to_display.png

上图演示了:T1执行时,只触碰到了索引idx_b,T1执行完后,在performance_schema.data_locks中只能看到idx_b上的锁,看起来T1并未在idx_a上设置任何锁;但,当T2执行触碰到了索引idx_a时,T2才恍然发现,原来T1已经在idx_a上设置了index record lock啦。

示例二:

“performance_schema.data_locks无法看到全部锁”

图20:index_record_lock_for_insertion.png

插入新行时,会先设置insert intention lock,插入成功后再在插入完成的行上设置index record lock。

上图演示了:T1插入了新行,但,在performance_schema.data_locks中,我们既看不到T1设置的insert intention lock,也看不到T1设置的index record lock。这是因为T1的WHERE条件并未触碰到任何索引(T1根本不存在WHERE条件),因此我们看不到T1的这两个锁;但,当T2要删除T1新插入的行时,T2才恍然发现,原来T1已经在索引c2上设置了index record lock。

示例三:

“performance_schema.data_locks无法看到全部锁”

图21:insert_intention_lock_for_insertion.png

插入新行时,本来是不会在performance_schema.data_locks中显示insert intention lock的,因为插入时WHERE条件并未触碰到任何索引(插入时根本不存在WHERE条件)。

上图演示了:T2插入新行时的insert intention lock 和 T1的gap lock冲突了,于是,我们得以在performance_schema.data_locks中观察到T2插入新行时需要请求insert intentin lock。

敲门砖:“途牛技术”