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

发布时间：2019-06-25 18:19:39 所属栏目：建站来源：java互联网架构

导读：副标题#e# 阅读提示本文所参考的MySQL文档版本是8.0，做实验的MySQL版本是8.0.13 本文主要参考了MySQL官方文档 InnoDB锁定和事务机制本文还参考了何登成的 MySQL加锁处理分析、一个最不可思议的MySQL死锁分析以及阿里云RDS-数据库内核组的常用SQL语句的

假设已存在值为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相互不会阻塞，这极大的提供了插入时的并发性。总结如下：

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

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

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

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

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

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

上图演示了：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。

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

上图演示了：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语句

insert
insert ... select
replace
replace ... select
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表级锁;

b. 当innodb_autoinc_lock_mode=1时，如果插入行的条数可以提前确定，则无需获得AUTO-INC表级锁;如果插入行的条数无法提前确定，则就需要获取AUTO-INC表级锁。因此，simple-inserts和mixed-mode inserts都无需AUTO-INC表级锁，此时，使用轻量级的mutex来互斥获得自增值;bulk-inserts需要获取到AUTO-INC表级锁;

c. 当innodb_autoinc_lock_mode=2时，完全不再使用AUTO-INC表级锁;

我们生产数据库版本是5.6.23-72.1，innodb_autoinc_lock_mode=1，而且，我们日常开发中用到大都是simple-inserts，此时根本就不使用AUTO-INC表级锁，所以，AUTO-INC表级锁用到的并不多哦。

LOCK_MODE：AUTO-INC表级锁用到的并不多，且，AUTO-INC锁是在语句结束后被释放，较难在performance_schema.data_locks中查看到，因此，没有进行捕获。感兴趣的同学可以使用INSERT ... SELECT捕获试试。

8、空间索引(Predicate Locks for Spatial Indexes)

我们平时很少用到MySQL的空间索引。所以，本文忽略此类型的锁

（编辑：西安站长网）

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

8/15

首页

尾页