MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁

为什么需要gap lock呢?gap lock存在的唯一目的就是阻止其他事务向gap中插入数据行，它用于在隔离级别为RR时，阻止幻影行(phantom row)的产生;隔离级别为RC时，搜索和索引扫描时，gap lock是被禁用的，只在 外键约束检查 和 重复key检查时gap lock才有效，正是因为此，RC时会有幻影行问题。

gap lock是如何阻止其他事务向gap中插入数据行的呢?看下图

索引是B+树组织的，因此索引是从小到大按序排列的，如果要插入10，那么能插入的位置只能是上图中标红的区间。在10和10之间插入时，我们就认为是插入在最后面的10的后面。如果封锁了标红的区间，那么其他事务就无法再插入10啦。

问题一：当T2要插入 10时，上图哪些地方允许插入(注意：索引是有序的哦)?

答：(8, 10)和(10,11)。在10和10之间插入，我们就认为是插入在最后的10后面。

只要封锁住图中标红的区间，T2就无法再插入10啦。上面这两个区间有什么特点吗?对，这两个区间就是：满足条件的每一条记录前面的间隙，及，最后一条不满足条件的记录前面的间隙。InnoDB使用下一个键锁(Next-Key Locks)或间隙锁(Gap Locks)来封锁这种区间。

问题二：gap lock是用来阻塞插入新数据行的，那么，T2, insert into g values('z', 9) 会被阻塞吗?插入('z', 8)，('z', 10)，('z', 11)呢?

答：上图中，T1的update设置的gap lock是 (8, 10)和(10,11)，而，insert intention lock的范围是(插入值, 向下的一个索引值)。insert intention lock的详细介绍请见下面的6. 插入意向锁(Insert Intention Locks)。

于是，对于上面这些插入值，得到的insert intention lock如下：

插入 ('z', 8)时，insert intention lock 是 (8, 10) -- 冲突，与gap lock (8, 10)重叠了

插入 ('z', 9)时，insert intention lock 是 (9, 10) -- 冲突，与gap lock (8, 10)重叠了

插入 ('z', 10)时，insert intention lock 是 (10, 11) -- 冲突，与gap lock (10, 11)重叠了

插入 ('z', 11)时，insert intention lock 是 (11, 15) -- 不冲突

事实是不是这样呢，看下图

是的，和我们分析的一致，为了看的更清楚，我们把结果列成图表如下

问题三：“gap是解决phantom row问题的”，插入会导致phantom row，但更新也一样也会产生phantom row啊。

例如，上图的T1和T2，T1把所有i=8的行更新为108，T2把i=15的行更新为8，如果T2不被阻塞，T1的WHERE条件岂不是多出了一行，即：T1出现了phantom row?

答：nice question。我们自己来分析下T1和T2分别加了哪些锁

T1加的锁：idx_i上的next-key lock (5, 8]，PRIMARY上的'b'，以及idx_i上的gap lock (8,10)

T2加的锁：idx_i上的next-key lock (11, 15]，PRIMARY上的'f'，以及idx_i上的gap lock (15,108)，最后这个gap lock是因为T1在idx_i上加了新值108

根据上面的分析，T1和T2的锁并没有重叠，即我们分析的结果是：T2不会被阻塞。

但，上图清楚的表明T2确实被阻塞了，原因竟然是：T2 insert intention lock和T1 gap lock(8, 10)冲突了。很奇怪，T2是更新语句，为什么会有insert intention lock呢?

我不知道确切的原因，因为我没找到文档说这事。根据我的推断，update ... set 成功找到结果集然后执行更新时，在即将被更新进入行的新值上设置了insert intention lock(如果找不到结果集，则就不存在insert intention lock啦)，因此，T2在idx_i上的新值8上设置了insert intention lock(8, 10)。最终，T2 insert intention lock(8, 10) 与 T1 gap lock(8, 10)冲突啦，T2被阻塞。

因此，update ... set 成功找到结果集时，会在即将被更新进入行的新值上设置 index record lock 以及 insert intention lock。如前所述，insert intention lock的范围是(插入值，下一个值)，如果T2是 update g set i=9 where i=15; 那么update ... set 所设置的新值是9，则T2 insert intention lock就是(9, 10)啦，它依然会和 T1 gap lock(8, 10)冲突，是这样吗?确实是的，感兴趣的同学可以试试。

5、 下一个键锁(Next-Key Locks)

next-key lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体，它锁定索引记录以及该索引记录前面的间隙。有shard或exclusive两种模式。

LOCK_MODE分别是：S或X。

当InnoDB 搜索或扫描索引时，InnoDB在它遇到的索引记录上所设置的锁就是next-key lock，它会锁定索引记录本身以及该索引记录前面的gap("gap" immediately before that index record)。即：如果事务T1 在索引记录r 上有一个next-key lock，则T2无法在 紧靠着r 前面的那个间隙中 插入新的索引记录(gap immediately before r in the index order)。

next-key lock还会加在“supremum pseudo-record”上，什么是supremum pseudo-record呢?它是索引中的伪记录(pseudo-record)，代表此索引中可能存在的最大值，设置在supremum pseudo-record上的next-key lock锁定了“此索引中可能存在的最大值”，以及 这个值前面的间隙，“此索引中可能存在的最大值”在索引中是不存在的，因此，该next-key lock实际上锁定了“此索引中可能存在的最大值”前面的间隙，也就是此索引中当前实际存在的最大值后面的间隙。例如，下图中，supremum pseudo-record上的next-key lock锁定了区间(18, 正无穷)，正是此next-key lock阻止其他事务插入例如19, 100等更大的值。

（编辑：厦门网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!