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

supremum pseudo-record上的next-key lock锁定了“比索引中当前实际存在的最大值还要大”的那个间隙，“比大还大”，“bigger than bigger”

6、 插入意向锁(Insert Intention Locks)

一种特殊的gap lock。INSERT操作插入成功后，会在新插入的行上设置index record lock，但，在插入行之前，INSERT操作会首先在索引记录之间的间隙上设置insert intention lock，该锁的范围是(插入值, 向下的一个索引值)。有shard或exclusive两种模式，但，两种模式没有任何区别，二者等价。

LOCK_MODE分别是：S,GAP,INSERT_INTENTION或X,GAP,INSERT_INTENTION。

insert intention lock发出按此方式进行插入的意图：多个事务向同一个index gap并发进行插入时，多个事务无需相互等待。

假设已存在值为4和7的索引记录，事务T1和T2各自尝试插入索引值5和6，在得到被插入行上的index record lock前，俩事务都首先设置insert intention lock，于是，T1 insert intention lock (5, 7)，T2 insert intention lock (6, 7)，尽管这两个insert intention lock重叠了，T1和T2并不互相阻塞。

如果gap lock或next-key lock 与 insert intention lock 的范围重叠了，则gap lock或next-key lock会阻塞insert intention lock。隔离级别为RR时正是利用此特性来解决phantom row问题;尽管insert intention lock也是一种特殊的gap lock，但它和普通的gap lock不同，insert intention lock相互不会阻塞，这极大的提供了插入时的并发性。总结如下：

1. gap lock会阻塞insert intention lock。事实上，gap lock的存在只是为了阻塞insert intention lock
2. gap lock相互不会阻塞
3. insert intention lock相互不会阻塞
4. insert intention lock也不会阻塞gap lock

INSERT插入行之前，首先在索引记录之间的间隙上设置insert intention lock，操作插入成功后，会在新插入的行上设置index record lock。

我们用下面三图来说明insert intention lock的范围和特性

上图演示了：T1设置了gap lock(13, 18)，T2设置了insert intention lock(16, 18)，两个锁的范围重叠了，于是T1 gap lock(13, 18)阻塞了T2 insert intention lock(16, 18)。

上图演示了：T1设置了insert intention lock(13, 18)、index record lock 13;T2设置了gap lock(17, 18)。尽管T1 insert intention lock(13, 18) 和 T2 gap lock(17, 18)重叠了，但，T2并未被阻塞。因为 insert intention lock 并不阻塞 gap lock。

上图演示了：T1设置了insert intention lock(11, 18)、index record lock 11;T2设置了next-key lock(5, 11]、PRIMARY上的index record lock 'b'、gap lock(11, 18)。此时：T1 index record lock 11 和 T2 next-key lock(5, 11]冲突了，因此，T2被阻塞。

7、自增锁(AUTO-INC Locks)

表锁。向带有AUTO_INCREMENT列 的表时插入数据行时，事务需要首先获取到该表的AUTO-INC表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁(注意：是语句结束后，而不是事务结束后)。

你可能会想，日常开发中，我们所有表都使用AUTO_INCREMENT作主键，所以会非常频繁的使用到该锁。不过，事情可能并不像你想的那样。在介绍AUTO-INC表级锁之前，我们先来看下和它密切相关的SQL语句以及系统变量innodb_autoinc_lock_mode

INSERT-like语句

1. insert
2. insert ... select
3. replace
4. replace ... select
5. load data

外加，simple-inserts, bulk-inserts, mixed-mode-inserts

simple-inserts

待插入记录的条数，提前就可以确定(语句初始被处理时就可以提前确定)因此所需要的自增值的个数也就可以提前被确定。

包括：不带嵌入子查询的 单行或多行的insert, replace。不过，insert ... on duplicate key update不是

bulk-inserts

待插入记录的条数，不能提前确定，因此所需要的自增值的个数 也就无法提前确定

包括：insert ... select, replace ... select, load data

在这种情况下，InnoDB只能每次一行的分配自增值。每当一个数据行被处理时，InnoDB为该行AUTO_INCREMENT列分配一个自增值

mixed-mode-inserts

也是simple-inserts语句，但是指定了某些(非全部)自增列的值。也就是说，待插入记录的条数提前能知道，但，指定了部分的自增列的值。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT ... ON DUPLICATE KEY UPDATE也是mixed-mode，最坏情况下，它就是INSERT紧跟着一个UPDATE，此时，为AUTO_INCREMENT列所分配的值在UPDATE阶段可能用到，也可能用不到。

再看一下系统变量innodb_autoinc_lock_mode，它有三个候选值0，1，和2

8.0.3之前，默认值是1，即“连续性的锁定模式(consecutive lock mode)”;8.0.3及之后默认值是2，即“交织性锁定模式(interleaved lock mode)”

a. 当innodb_autoinc_lock_mode=0时，INSERT-like语句都需要获取到AUTO-INC表级锁;

（编辑：厦门网）

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