引言

锁分析过程中经常会发现不同场景对应不同类型的锁，本文在前文《MySQL 并发 delete 后 insert 导致死锁》的基础上，通过分析另一个相关死锁案例介绍锁相关原理与加锁规则。

介绍

《MySQL 并发 delete 后 insert 导致死锁》中的死锁原因是 delete 不存在的中间数据持有间隙锁未释放，insert 在间隙中插入数据等待插入意向锁，最终导致死锁。

本文中介绍一个相关案例，update 末尾不存在的数据持有 next-key lock 未释放，insert 在间隙中插入数据等待插入意向锁，最终导致死锁。

可以发现，使用非唯一索引更新不存在的中间数据与末尾数据时分别需要获取间隙锁与 next-key lock。

因此具体场景下的加锁情况与很多因素有关，如是否使用索引、使用的索引类型、查询的记录是否存在。

实际上可以参考丁奇大佬总结的加锁规则，包括两个原则、两个优化、一个bug。

分析行级锁的种类时发现不同事务隔离级别下，行级锁的种类不同。因此锁与事务隔离级别、MVCC 有关。本文进行简单的原理分析，并测试不同类型索引对应的加锁规则。

复现

初始化

创建表并初始化，表中包括主键索引与非唯一索引。

mysql> show create table t_three G
*************************** 1. row ***************************
       Table: t_three
Create Table: CREATE TABLE `t_three` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) NOT NULL COMMENT 'student_id 学生Id',
  `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_s_id` (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t_three;                       
+----+------+--------+
| id | s_id | name   |
+----+------+--------+
|  1 |    1 | name1  |
|  2 |    2 | name2  |
|  3 |    3 | name3  |
|  4 |    4 | name4  |
|  5 |    5 | name5  |
|  6 |    6 | name6  |
|  7 |    7 | name7  |
|  8 |    8 | name8  |
|  9 |    9 | name9  |
| 10 |   10 | name10 |
+----+------+--------+
10 rows in set (0.00 sec)

步骤

两个事务分别先 update 后 inesrt，insert 过程中事务 1 发生锁等待，事务 2 发生死锁。

session 1	session 2
begin;
	begin;
update t_three set name=’cname11′ where s_id=11;
	update t_three set name=’cname12′ where s_id=12;
insert into t_three(s_id,name) values(11, ‘cname11’); blocked
	insert into t_three(s_id,name) values(12, ‘cname12’); Deadlock found

根据死锁日志：

事务1，等待插入意向锁；
事务2，持有 next-key lock，等待插入意向锁。

死锁

死锁日志

完整死锁日志如下所示。

*** (1) TRANSACTION:
TRANSACTION 11309129, ACTIVE 1655 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1171, OS thread handle 140689374668544, query id 13805023 127.0.0.1 admin update
insert into t_three(s_id,name)  values(11, 'cname11')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 322 page no 4 n bits 80 index idx_s_id of table `test_zk`.`t_three` trx id 11309129 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 11309130, ACTIVE 280 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1173, OS thread handle 140689374934784, query id 13805025 127.0.0.1 admin update
insert into t_three(s_id,name)  values(12, 'cname12')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 322 page no 4 n bits 80 index idx_s_id of table `test_zk`.`t_three` trx id 11309130 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 322 page no 4 n bits 80 index idx_s_id of table `test_zk`.`t_three` trx id 11309130 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

下面详细分析死锁日志。

事务1

事务 11309129，执行 insert into t_three(s_id,name) values(11, ‘cname11’)。

锁链表长度为 3，有 2 把行锁，其中等待插入意向锁。

*** (1) TRANSACTION:
TRANSACTION 11309129, ACTIVE 1655 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1171, OS thread handle 140689374668544, query id 13805023 127.0.0.1 admin update
insert into t_three(s_id,name)  values(11, 'cname11')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 322 page no 4 n bits 80 index idx_s_id of table `test_zk`.`t_three` trx id 11309129 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

由于插入意向锁是特殊的间隙锁，显示 Record lock, heap no 1, supremum，表明锁定范围为 (10, +∞)。

伪记录 Infimum 的 heap_no 值为 0，Supremum 的 heap_no 值为 1，之后每插入一条记录，heap_no 值就增 1。

事务2

事务 11309130，执行 insert into t_three(s_id,name) values(12, ‘cname12’)。

锁链表长度为 3，有 2 把行锁，其中持有 next-key lock，等待插入意向锁。

*** (2) TRANSACTION:
TRANSACTION 11309130, ACTIVE 280 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1173, OS thread handle 140689374934784, query id 13805025 127.0.0.1 admin update
insert into t_three(s_id,name)  values(12, 'cname12')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 322 page no 4 n bits 80 index idx_s_id of table `test_zk`.`t_three` trx id 11309130 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 322 page no 4 n bits 80 index idx_s_id of table `test_zk`.`t_three` trx id 11309130 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

next-key lock 与插入意向锁的 Record lock 均显示 Record lock, heap no 1, supremum，表明锁定范围分别为 (10, +∞]、(10, +∞)。

锁

update

update 一条不存在的大于最大值的记录。

update t_three set name='cname11' where s_id=11;

查看锁，其中有 1 把行锁，LOCK_MODE: X，LOCK_DATA: supremum pseudo-record。

mysql> select * from performance_schema.data_locks G
...
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:16:5:1:140122972537552
ENGINE_TRANSACTION_ID: 2032360
            THREAD_ID: 123
             EVENT_ID: 29
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_three
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_s_id
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)

insert

插入一条记录。

insert into t_three(s_id,name) values(11, 'cname11');

查看锁，显示插入意向锁等待，没有行锁。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1078:140122972540608
ENGINE_TRANSACTION_ID: 2032365
            THREAD_ID: 123
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_three
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140122972540608
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
1 row in set (0.00 sec)

可见，如果死锁的两个事务都是在等待插入意向锁，很有可能在此之前每个事务中有执行 update 或 delete 操作，导致插入意向锁等待间隙锁释放。

下面结合原理分析加锁规则。

原理

锁的概念

锁的持有周期

锁用于保证数据一致性。

传统 RDBMS 加锁的一个原则，就是 2PL（二阶段锁，Two-Phase Locking）。2PL 中将锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

关于 2PL 中加锁与解锁的细节，需要注意以下两点：

加锁，逐行加锁，而非一次性加全部锁，只有当实际访问到某个待更新的行时，才会对其加锁；

解锁，所有加锁的资源会在事务提交或回滚时释放，而不是使用完就释放。

行级锁的种类

首先，需要明确行级锁的种类。

不同事务隔离级别下，行级锁的种类不同。

其中：

RC 隔离级别下行级锁的种类只有记录锁，即仅仅把一条记录锁上；
RR 隔离级别下行级锁主要分为三类：

记录锁（Record Lock），对索引项加锁，即锁定一条记录；
间隙锁（Gap Lock），对索引项之间的间隙、对第一条记录前的间隙或最后一条记录后间隙加锁，即锁定一个范围，不包含记录本身，是左开右开的区间；
Next-key Lock，锁定一个范围的记录包括记录本身，因此 Next-key Lock = Record Lock + Gap Lock，因此 Next-key Lock 是左开右闭的区间。

然后，需要明确行级锁的加锁规则。

加锁的对象是索引，加锁的基本单位是 Next-key Lock，由记录锁和间隙锁组成。

但是，Next-key Lock 在一些场景下会退化成记录锁或间隙锁。

具体是在能使用记录锁或者间隙锁就能避免幻读现象的场景下， Next-key Lock 就会退化成退化成记录锁或间隙锁。

行级锁的详细加锁规则与很多因素有关，如是否使用索引、使用的索引类型、查询的记录是否存在。

读写操作与锁

读写操作对应的锁是有区别的，原因是读操作有可能允许读取历史数据，而写操作一定要求最新数据。

实际上部分业务场景也不允许读取记录的旧版本，因此可以将读操作进一步分为快照读与当前读，而写操作作为当前读处理。

快照读允许读取历史数据，读不需要加锁，为并发读写提供了可能，快照读基于 MVCC 实现。

分读写操作依次介绍需要的锁。

读操作：

快照读，一致性读，Consistent Read，普通的 select 语句不会对记录加锁，通过 MVCC 实现；
锁定读，当前读，Locking Read，读取过程中对记录加锁：

SELECT … LOCK IN SHARE MODE，加 S 锁，可读不可写；
SELECT … FOR UPDATE，加 X 锁，不可读写。

写操作：

DELETE，获取 X 锁的锁定读；
UPDATE，获取 X 锁的锁定读；
INSERT，一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中生成对应的锁结构。

由于加锁的对象是索引，加锁的基本单位是 Next-key Lock，因此上面提到的锁定读均需要获取 Next-key Lock。

不过对于唯一索引，Next-key Lock 会退化为记录锁。

Only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

事务隔离级别与 MVCC

事务隔离级别

为什么不同事务隔离级别中锁的种类有区别，或者说为什么需要事务隔离级别呢？

事务是并发控制的最小单元，如果事务之间不是隔离的，事务的并发执行就可能导致一致性问题。

并发事务访问相同记录可以分为以下三种场景：

读-读操作：并发事务相继读取相同的记录，读操作不改动原纪录，因此允许该操作；
写-写操作：并发事务相继改动相同的记录，会导致脏写，因此不允许该操作，通过加锁实现；
读-写或写-读操作，一个事务读取记录时另一个事务改动该记录，可能导致以下三种问题：

脏读，一个事务处理过程中读取到了另一个事务未提交的数据；
不可重复读，一个事务范围内多次查询某个数据，得到不同的值，针对更新操作；
幻读，一个事务两次查询同一范围时，读到的记录数不同，针对插入或删除操作。

要避免脏读、不可重复读、幻读现象，有两种解决方案：

读操作使用多版本并发控制（MVCC），写操作加锁，读写操作可以并发执行；
读写操作均加锁，读写操作不可以并发执行。

MySQL 中选择了【MVCC + 锁】的方式实现并发控制，并引入事务隔离级别的概念。事务隔离级别定义了事务之间按照什么规则进行隔离，将事务隔离到什么程度。

不同事务隔离级别和并发问题矩阵见下表，其中 Y 表示存在该问题，N 表示不存在该问题。

事务隔离级别	脏读	不可重复读	幻读	锁机制
读未提交（Read-Uncommitted）	Y	Y	Y	事务读取均不加锁
读已提交（Read-Committed）	N	Y	Y	当前读加锁，事务结束时释放
可重复读（Repeatable-Read）	N	N	Y	当前读加锁，有间隙锁
串行化（Serializable）	N	N	N	事务读取均加锁

隔离级别越高，安全性越高，数据库高并发的吞吐性能越低。

业内普遍使用的事务隔离级别为 RC 或 RR。

RR 与 RC 中加锁的区别是什么呢？

答案是相比于 RR，RC 中锁的范围更小，锁的时间更短。

主要原因有两点：

RC 中只有记录锁，锁的对象都是记录，RR 中引入间隙锁，用于解决幻读现象，锁的对象也可以是间隙。
在 RC 中还有一个优化，语句执行过程中加上的行锁，在语句执行完成后就可以将“不满足条件的行”上的行锁释放掉，而不需要等到事务提交，称为 semi consistent read。

锁的范围越大，并发能力越差，常见现象是高并发下发生死锁。因此，如果业务允许，可以默认使用 RC。

实际上，MySQL 中默认隔离级别为 RR，而 Oracle、SQL Server、PostgreSQL 中默认使用 RC。

MVCC

MVCC（Multi Version Concurrency Control）是一种并发控制的方法，用于支持并发的读写操作，可以在保证数据一致性的前提下可以降低锁之间的竞争。

MVCC 通过提供基于时间点的一致性读（快照读），使读操作在不加锁的前提下实现数据一致性。

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.

MVCC 的实现基于 undo log 与 ReadView，其中 undo log 用于提供数据的版本链，ReadView 用于判断版本链中的哪个版本是当前事务可见的。

当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo log 读取之前的行版本信息，以此实现非锁定读取。

需要明确以下两点：

MVCC 仅针对读操作，读不加锁，用于支持并发的读写操作；
MVCC 仅针对 RC 与 RR，原因是读未提交事务隔离中使用当前读，串行化事务隔离中读取加锁。

RC 与 RR 中 MVCC 的主要区别在于 ReadView 的生成时机：

RC，每次读取数据前都生成一个 ReadView；
RR，在第一次读取数据时生成一个 ReadView。

因此，MVCC 指的就是在使用 RC、RR 这两种事务隔离级别的事务执行普通 select 操作时，访问记录的版本链的过程。

加锁分析

锁的边界

加锁的对象是索引，因此锁的对象是记录，但是这样无法避免幻读。原因是事务在第一次执行读取操作时，这些幻影记录还不存在，因此无法给这些记录加锁。RR 中为解决幻读，引入间隙锁，通过给区间加锁防止在记录前面的间隙中插入新记录，因此锁的对象也可以是间隙。

但是这样就出现了另一个问题，如果间隙位于第一条记录之前或最后一条记录之后如何给记录加间隙锁呢？

实际上，InnoDB 给每个索引加了一个不存在的最大值与最小值：

infimum record，该页面中最小的记录；
supremum record，该页面中最大的记录。

因此，如果要阻止其他事务在当前索引最大值后插入记录，可以给 supremum record 加锁。

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum”pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

当 update 比最大值更大的记录时，当前索引中最大值为 10，为阻止其他事务在区间 (10, +∞) 中插入新记录，可以给索引中最后一条记录所在页面的 supremum 记录加上间隙锁。

update t_three set name='cname11' where s_id=11;

查看锁，显示持有 next-key lock。被锁的最大行为 supremum pseudo-record，它具有大于所有索引的值，不是真正的索引记录。此时，锁的范围扩大到正无穷。

           INDEX_NAME: idx_s_id
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)

需要注意的是对于 supremum record，next-key lock 并没有退化为间隙锁。next-key lock 本身是一个左开右闭区间，而这条记录并不存在，并不存在记录锁，因此没有必要再退化了。

可以为什么阻止其他事务在当前索引最小值前插入记录时 LOCK_DATA 不显示 infimum record 呢？

原因是间隙锁的 LOCK_DATA 字段仅标识右边界，因此不会显示出 infimum record，左边界是上一条记录。

加锁规则

参考丁奇大佬的《MySQL 实战 45 讲》，加锁规则可以简单总结为如下两个原则、两个优化、一个bug。

1）两个原则

原则 1：加锁的基本单位是 next-key lock，next-key lock 是左开右闭区间；
原则 2：查找过程中访问到的对象才会加锁。

2）两个优化

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为记录锁；
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

3）一个bug

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。注意是范围查询，不包括等值查询。

因此不需要死记硬背不同场景下的加锁情况，而需要熟练使用这几条加锁规则。

具体这 5 条规则又可以合并为如下 3 条规则：

查询过程中访问到的对象才会加锁，而加锁的基本单位是 next-key lock 左开右闭区间；
等值查询，索引上的等值查询优化，如果是唯一索引，next-key lock 会退化为行锁，如果不是唯一索引，需要向右遍历到不满足条件的第一个值，next-key lock 会退化为间隙锁；
范围查询，无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止。等值查询中不存在该问题。

唯一索引上的范围查询会访问到不满足条件的第一个值为止可以认为是 MySQL 的 bug，并在 8.0.18 中修复。

An unnecessary next key lock was taken when performing a SELECT…FOR [SHARE|UPDATE] query with a WHERE condition that specifies a range, causing one too many rows to be locked. The most common occurrences of this issue have been addressed so that only rows and gaps that intersect the searched range are locked. (Bug #29508068)

测试

准备数据

创建测试表并初始化数据，注意其中有主键索引、唯一索引、非唯一索引、无索引字段。

mysql> show create table t_lock_test G
*************************** 1. row ***************************
       Table: t_lock_test
Create Table: CREATE TABLE `t_lock_test` (
  `id` int NOT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uk_mobile` (`mobile`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from t_lock_test;
+----+-------------+------+------+
| id | mobile      | name | age  |
+----+-------------+------+------+
|  2 | 17118168721 | Bob  |   31 |
|  4 | 15373838350 | Bob  |   30 |
|  5 | 13785078432 | Kara |   20 |
|  9 | 18901970832 | Anna |   30 |
| 12 | 17837938413 | Kara |   25 |
+----+-------------+------+------+
5 rows in set (0.00 sec)

索引类型

注意测试过程中使用锁定读语法，查看锁时仅展示行锁，不展示表锁。

主键索引

执行如下 SQL，返回一条记录。

mysql> select * from t_lock_test where id=2 for update;
+----+-------------+------+------+
| id | mobile      | name | age  |
+----+-------------+------+------+
|  2 | 17118168721 | Bob  |   31 |
+----+-------------+------+------+
1 row in set (0.00 sec)

分析加锁规则：

根据原则 1，加锁的单位是主键索引的 next-key lock，加锁范围为 (2,4]；
根据优化 1，唯一索引等值查询时 next-key lock 退化为记录锁，因此最终加锁为 id=2 的记录锁。

查看锁，有 1 把行锁，具体是主键记录锁。

mysql> select * from performance_schema.data_locks G
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:2:140122972537552
ENGINE_TRANSACTION_ID: 2032278
            THREAD_ID: 101
             EVENT_ID: 22
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
2 rows in set (0.00 sec)

唯一索引

执行如下 SQL，返回一条记录。

mysql> select * from t_lock_test where mobile='17118168721' for update;
+----+-------------+------+------+
| id | mobile      | name | age  |
+----+-------------+------+------+
|  2 | 17118168721 | Bob  |   31 |
+----+-------------+------+------+
1 row in set (0.00 sec)

分析加锁规则：

根据原则 1，加锁的单位是唯一索引的 next-key lock，加锁范围为 (15373838350,17118168721]；
根据原则 2，由于 select *，需要回表，因此也会锁主键索引，加锁范围为 (2,4]；
根据优化 1，唯一索引等值查询时 next-key lock 退化为记录锁，因此最终加锁为 id=2、 mobile=17118168721 的记录锁。

注意索引的顺序可能与主键顺序不同，因此分析间隙锁之前需要先排序才可以确定前一条记录。

查看锁，其中有 2 把行锁，包括唯一索引记录锁、主键记录锁。

*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:7:4:140122972537552
ENGINE_TRANSACTION_ID: 2032293
            THREAD_ID: 102
             EVENT_ID: 18
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_uk_mobile
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: '17118168721', 2
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:2:140122972537896
ENGINE_TRANSACTION_ID: 2032293
            THREAD_ID: 102
             EVENT_ID: 18
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537896
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
3 rows in set (0.00 sec)

非唯一索引

执行如下 SQL，返回两条记录。

mysql> select * from t_lock_test where name='Bob' for update;
+----+-------------+------+------+
| id | mobile      | name | age  |
+----+-------------+------+------+
|  2 | 17118168721 | Bob  |   31 |
|  4 | 15373838350 | Bob  |   30 |
+----+-------------+------+------+
2 rows in set (0.00 sec)

分析加锁规则：

根据原则 1，加锁的单位是非唯一索引的 next-key lock，加锁范围为 (Anna,Bob]；
由于 name 是普通索引，因此可能对应多行数据，查询继续向右遍历，直到 name=’Kara’ 才停止。根据原则 2，访问到的都需要加锁，因此要给 (Bob,Bob]、 (Bob,Kara] 加 next-key lock；
根据原则 2，由于 select *，需要回表，因此也会锁主键索引，加锁范围为 (-∞,2]、(2,4]。由于优化 1，唯一索引等值查询退化为行锁，因此加锁范围为 id=2、id=4 的记录锁；
根据优化 2，等值查询向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。因此最终加锁范围为 (Anna,Bob]、(Bob,Bob]、 (Bob,Kara)、id=2、id=4。

查看锁，其中有 5 把行锁，包括非唯一索引 2 把 next-key lock、不满足条件的第一条记录的间隙锁、主键索引 2 把对应的记录锁。

*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:6:2:140122972537552
ENGINE_TRANSACTION_ID: 2032294
            THREAD_ID: 102
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_name
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'Bob', 2
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:6:3:140122972537552
ENGINE_TRANSACTION_ID: 2032294
            THREAD_ID: 102
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_name
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'Bob', 4
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:2:140122972537896
ENGINE_TRANSACTION_ID: 2032294
            THREAD_ID: 102
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537896
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:3:140122972537896
ENGINE_TRANSACTION_ID: 2032294
            THREAD_ID: 102
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537896
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 4
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:6:4:140122972538240
ENGINE_TRANSACTION_ID: 2032294
            THREAD_ID: 102
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_name
OBJECT_INSTANCE_BEGIN: 140122972538240
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'Kara', 5
6 rows in set (0.00 sec)

无索引

执行如下 SQL，返回一条记录。

mysql> select * from t_lock_test where age=31 for update;
+----+-------------+------+------+
| id | mobile      | name | age  |
+----+-------------+------+------+
|  2 | 17118168721 | Bob  |   31 |
+----+-------------+------+------+
1 row in set (0.00 sec)

分析加锁规则：

由于没有使用索引列作为查询条件，导致全表扫描，每一条记录的索引上都会加 next-key lock；
根据原则 2，访问到的都需要加锁，因此加锁范围为 (-∞,2]、(2,4]、(4,5]、(5,9]、(9,12]、(12,-∞)。

查看锁，其中有 6 把行锁，主键索引每行都是 next-key lock，包括 supremum pseudo-record，相当于锁表。

注意：

主键索引的 next-key lock 没有退化为记录锁，原因是需要防止插入幻影记录；
supremum pseudo-record 的 next-key lock 没有退化为间隙锁。

*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:1:140122972537552
ENGINE_TRANSACTION_ID: 2032296
            THREAD_ID: 102
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:2:140122972537552
ENGINE_TRANSACTION_ID: 2032296
            THREAD_ID: 102
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:3:140122972537552
ENGINE_TRANSACTION_ID: 2032296
            THREAD_ID: 102
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 4
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:4:140122972537552
ENGINE_TRANSACTION_ID: 2032296
            THREAD_ID: 102
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:5:140122972537552
ENGINE_TRANSACTION_ID: 2032296
            THREAD_ID: 102
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 9
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:14:4:6:140122972537552
ENGINE_TRANSACTION_ID: 2032296
            THREAD_ID: 102
             EVENT_ID: 34
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_lock_test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 12
7 rows in set (0.00 sec)

查看执行计划，type: ALL 表明全表扫描，甚至没有使用主键索引。当前该表上，除了不加锁的快照读，其他任何加锁的并发 SQL，都不能执行，不能更新，不能删除，不能插入，全表被锁死。这种 SQL 是一定要避免出现的。

mysql> explain select * from t_lock_test where age=31 for update G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_lock_test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

对比

查询条件	锁	备注
主键索引	2 把行锁，包括唯一索引记录锁、主键记录锁	唯一索引等值查询时 next-key lock 退化为记录锁
唯一索引	2 把行锁，包括唯一索引记录锁、主键记录锁	唯一索引等值查询时 next-key lock 退化为记录锁
非唯一索引	5 把行锁，包括非唯一索引 2 把 next-key lock、不满足条件的第一条记录的间隙锁、主键索引 2 把对应的记录锁	等值查询向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁
无索引	6 把行锁，主键索引每行都是 next-key lock，包括 supremum pseudo-record	主键索引的 next-key lock 没有退化为记录锁，需要防止插入幻影记录

结论

两个事务的 update 操作均持有 next-key lock，insert 操作均在等待插入意向锁，而插入意向锁等待对方释放间隙锁，所以导致发生死锁。

可见，如果死锁的两个事务都是在等待插入意向锁，很有可能在此之前每个事务中有执行 update 或 delete 操作，导致插入意向锁等待间隙锁释放。

事务是并发控制的最小单元，如果事务之间不是隔离的，事务的并发执行就可能导致一致性问题。

数据的读取操作可以分为快照读与当前读，快照读允许读取历史数据，读不需要加锁，为并发读写提供了可能。

为解决并发读写操作可能导致的脏读、不可重复读、幻读现象，InnoDB 中基于【MVCC + 锁】的方式实现快照读，实现在不加锁的条件下支持并发读写。

事务隔离级别定义了事务之间按照什么规则进行隔离，将事务隔离到什么程度。

隔离级别越高，安全性越高，数据库高并发的吞吐性能越低。

业内普遍使用的事务隔离级别为 RC 或 RR。MySQL 中默认隔离级别为 RR。

相比于 RR，RC 中锁的范围更小，锁的时间更短。

此外，需要明确加锁原则 2PL（二阶段锁，Two-Phase Locking）。2PL 中将锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。简单来说，加锁是逐行加锁，解锁是事务结束时统一释放。

加锁规则可以简单总结为如下两个原则、两个优化、一个bug。

1）两个原则

原则 1：加锁的基本单位是 next-key lock，next-key lock 是左开右闭区间；
原则 2：查找过程中访问到的对象才会加锁。

2）两个优化

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为记录锁；
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

3）一个bug

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。注意是范围查询，不包括等值查询。

待办

MVCC
事务
范围查询加锁规则

参考教程

mysql死锁特征_Mysql死锁分析案例（一）

https://blog.csdn.net/weixin_42314448/article/details/113147992

30 张图搞懂 MySQL行锁
《MySQL 实战 45 讲》为什么我只改一行的语句，锁这么多？

https://time.geekbang.org/column/article/75659

《MySQL 是怎样运行的：从根儿上理解 MySQL》
MySQL 加锁处理分析

https://github.com/hedengcheng/tech/blob/master/database/MySQL/MySQL%20%E5%8A%A0%E9%94%81%E5%A4%84%E7%90%86%E5%88%86%E6%9E%90.pdf

原文始发于微信公众号（丹柿小院）：MySQL update 后 insert 导致死锁

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/178644.html

MySQL update 后 insert 导致死锁

引言

介绍

复现

初始化

步骤

死锁

死锁日志

事务1

事务2

锁

update

insert

原理

锁的概念

锁的持有周期

行级锁的种类

读写操作与锁

事务隔离级别与 MVCC

事务隔离级别

MVCC

加锁分析

锁的边界

加锁规则

测试

准备数据

索引类型

主键索引

唯一索引

非唯一索引

无索引

对比

结论

待办

参考教程

相关推荐

发表回复

分享到: