MySQL 锁三部曲（2） - InnoDB 锁上一篇：MySQL 锁三部曲（1） - MDL 锁主要介绍了MDL锁，

上一篇：MySQL 锁三部曲（1） - MDL 锁主要介绍了MDL锁，今天继续看第二部分，InnoDB 锁。

1. 概述

我们说到锁，从开发或者运维层面InnoDB锁碰到的会比较多，上一篇文章开篇提出问题，并且讲了MDL相关的内容，所以这次我们先整体看一下MySQL的锁体系，然后在深入了解InnoDB锁结构。

MySQL 采用插件化存储引擎，整体结构分为 Server 层 和 存储引擎层。基于这两层结构，MySQL 的锁机制也分为两类：Server 层的元数据锁（MDL） 和 存储引擎层的锁。

Server 层的元数据锁（MDL） 用于保护数据库对象的元数据，防止 DDL 操作（如修改表结构）与 DML 操作（如数据增删改）冲突，保证元数据的一致性和安全性。

存储引擎层的锁 取决于所使用的具体存储引擎。InnoDB 存储引擎支持多种锁，包括 表级锁 和 行级锁，以实现更细粒度的并发控制。

1.1. 锁分类

InnoDB 的 表级锁 包括：

共享锁（S） ：允许多个事务同时读取表数据，但不允许写入。
排他锁（X） ：独占访问表，禁止其他事务读写。
意向锁：用于标识事务对表中数据行的加锁意图，加速锁兼容性判断。

- 意向共享锁（IS） 表示事务将对行加共享锁。
- 意向排他锁（IX） 表示事务将对行加排他锁。

AUTO-INC 锁：用于保护自增列，避免并发插入导致的自增值冲突。

- 表锁：
- 轻量级互斥锁（Mutex）

InnoDB 的 行级锁 包括：

共享锁（S） ：允许多个事务并发读取同一行数据。
排他锁（X） ：事务独占行，禁止其他事务访问。

行锁进一步细分为：

普通记录锁（LOCK_REC_NOT_GAP） ：只锁定目标记录，不锁定间隙。
间隙锁（LOCK_GAP） ：锁定索引记录之间的间隙，防止其他事务插入数据。
Next-Key 锁（LOCK_ORDINARY） ：结合记录锁和间隙锁，既锁定目标记录，也锁定相邻间隙，用于防止幻读。

还有比较特殊的：

插入意向锁（LOCK_INSERT_INTENTION） ：用于插入操作，避免多个事务并发插入同一区间数据导致的冲突。本质上也是间隙锁（Gap Lock）
隐式锁：InnoDB 实现了一个延迟加锁的机制，来减少加锁的数量

综合来看，MySQL 的锁机制通过 Server 层的 MDL 锁 保证元数据安全，通过 InnoDB 的表锁和行锁 提供高效的并发控制和事务隔离，有效防止数据冲突和一致性问题。

InnoDB引擎锁兼容性列表

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

1.2. 意向锁

首先我们介绍意向锁，因为在InnoDB加锁流程中，是自顶向下的。比如一条update t set col=1 where id=2这条SQL语句执行时，优先在表 t 上加一个 IX，其次会加行级锁（Record Lock）。

意向锁的主要作用是解决表级锁与行级锁并存时的冲突检测问题。InnoDB 设计了意向共享锁（IS）和意向排他锁（IX）作为一种“登记机制”，用于快速判断某个表中是否存在行级别的锁，避免遍历所有记录锁，提升加锁效率。

意向共享锁（IS）是在对表中某些记录加行级共享锁（S）之前，加在表级的锁，用于表示事务即将对表中部分记录加共享锁。意向排他锁（IX）则是在对表中某些记录加行级排他锁（X）之前，加在表级的锁，用于表示事务即将对表中部分记录加排他锁。

在具体操作中，执行 SELECT ... LOCK IN SHARE MODE 时，InnoDB 会在加行级共享锁（S）的同时，加上表级的意向共享锁（IS）；执行 SELECT ... FOR UPDATE、UPDATE 或 DELETE 时，会在加行级排他锁（X）的同时，加上表级的意向排他锁（IX）；执行 INSERT 操作虽然不涉及行锁，但也会加表级的意向排他锁（IX），以保证操作的安全性。

意向锁的引入显著优化了锁冲突检测的性能。InnoDB 不再需要遍历所有记录锁来判断是否存在冲突，而是通过查看表级的意向锁，快速判断是否存在潜在冲突。这种设计有效降低了锁冲突检测的开销，简化了锁机制。

需要注意的是，意向锁与表级共享锁或排他锁并不等同。它们只是为了配合行级锁而存在的辅助锁。在表级锁和行级锁的兼容性检查中，InnoDB 会优先检查表级的意向锁，从而提高锁管理的效率。

意向锁可以理解为也是一种表锁。

1.3. 表锁

InnoDB表锁在实际场景中使用并不多，不过需要了解一下。加锁语句如下：

lock tables t1 read; -- 加只读表锁

执行之后会发现，这个语句是锁不住表的。在performance_schema.data_locks表中记录为空。原因是从 MySQL 4.1.9 开始，如果系统变量 autocommit 的值为 ON，lock tables 语句不会给表加表级别的共享锁或排他锁。

其实，只有同时满足以下两个条件，lock tables 语句才会给表加表级别的共享锁或排他锁：

innodb_table_locks = ON。
autocommit = OFF。

默认情况下innodb_table_locks、autocommit都为ON，所以我们将autocommit修改为OFF，就可以对表加表锁。

此时会有一个疑问，为什么默认参数下InnoDB不加表锁？原因是Server层已经有MDL锁了，比如上面的SQL语句会加上TABLE-TRANSACTION-SRO锁。

那么InnoDB表锁在如下两种特殊场景下需要用到：

外键检查。
崩溃恢复过程中收集未提交完成的事务。

1.4. 自增锁（AUTO-INC）

自增锁在 MySQL 中的实现分为两种模式：

表级锁（Table-Level Locking） ：传统的自增锁模式（innodb_autoinc_lock_mode = 0），整个表在生成自增值时加锁，直到插入操作完成。
轻量级的互斥锁（Mutex） ：为了优化并发性能，InnoDB 后续引入了更高效的方式，通过轻量级的互斥锁控制自增值的生成，而不必锁定整个表（innodb_autoinc_lock_mode != 0）。

在 MySQL 中，通常将主键字段定义为整型，并设置为递增的数字序列。为了避免手动指定主键值导致重复插入的风险，常使用 AUTO_INCREMENT 关键字将主键字段设置为自增字段，让 MySQL 自动生成递增的主键值。MySQL 不限制自增字段只能用于主键或唯一索引，普通索引也可以使用，但并不推荐。

为了保证自增字段值在高并发下不会重复，MySQL 引入了 AUTO-INC 锁。该锁的行为由系统变量 innodb_autoinc_lock_mode 控制，取值包括 0（传统模式）、1（连续模式）和 2（交错模式）。

在传统模式（innodb_autoinc_lock_mode = 0）下，MySQL 在生成自增字段值前，会对目标表加表级 AUTO-INC 锁，直到插入操作完成后才释放。这种模式确保同一条插入语句生成的自增值是连续的，主从复制是安全的。但缺点是同一时间只能有一个事务持有该锁，导致插入操作只能串行执行，并发能力较差。

连续模式（innodb_autoinc_lock_mode = 1）是 MySQL 8.0 之前的默认模式。该模式根据 SQL 类型选择加锁策略：对于无法预估插入记录数量的 INSERT ... SELECT 语句，会加表级 AUTO-INC 锁；对于 INSERT ... VALUES 这种简单的插入语句，则使用轻量锁，在生成自增值后立即释放。此模式仍能保证自增值连续，且主从复制安全，同时并发能力优于传统模式。

交错模式（innodb_autoinc_lock_mode = 2）是 MySQL 8.0 的默认模式。此模式下，所有插入语句在生成自增值时仅使用轻量锁，不会加表级锁。尽管插入多条记录时自增值可能不连续，但这种模式的并发能力最强。由于生成的自增值可能与主库不一致，交错模式对基于语句（STATEMENT）的主从复制不安全，但与默认的行级（ROW）复制模式配合使用时是安全的。

MySQL 8.0 将默认的 innodb_autoinc_lock_mode 从 1（连续模式）调整为 2（交错模式），是因为默认的 binlog_format 已由 STATEMENT 改为 ROW，不再需要依靠连续模式来保证主从复制中自增值的一致性。

1.5. InnoDB 行锁

数据准备，隔离级别为RR

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t1` (`id`, `i1`) VALUES 
(10, 101), (20, 201), (30, 301), (40, 401);

#查询锁SQL
select
  engine_transaction_id, object_name,
  lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't1' and lock_type = 'RECORD';

1.5.1. 普通记录锁（LOCK_REC_NOT_GAP）

制造锁；

begin;
select * from t1 where id = 10 lock in share mode;

查询锁

mysql> select
    ->   engine_transaction_id, object_name,
    ->   lock_type, lock_mode, lock_status, lock_data
    -> from performance_schema.data_locks
    -> where object_name = 't1' and lock_type = 'RECORD';
+-----------------------+-------------+-----------+---------------+-------------+-----------+
| engine_transaction_id | object_name | lock_type | lock_mode     | lock_status | lock_data |
+-----------------------+-------------+-----------+---------------+-------------+-----------+
|       284330980665936 | t1          | RECORD    | S,REC_NOT_GAP | GRANTED     | 10        |
+-----------------------+-------------+-----------+---------------+-------------+-----------+
1 row in set (0.00 sec)

1.5.2. 间隙锁（LOCK_GAP）

制造锁：

begin;
select * from t1 where id < 10 lock in share mode;

查询锁

mysql> select
    ->   engine_transaction_id, object_name,
    ->   lock_type, lock_mode, lock_status, lock_data
    -> from performance_schema.data_locks
    -> where object_name = 't1' and lock_type = 'RECORD';
+-----------------------+-------------+-----------+-----------+-------------+-----------+
| engine_transaction_id | object_name | lock_type | lock_mode | lock_status | lock_data |
+-----------------------+-------------+-----------+-----------+-------------+-----------+
|       284330980665936 | t1          | RECORD    | S,GAP     | GRANTED     | 10        |
+-----------------------+-------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

1.5.3. Next-Key 锁（LOCK_ORDINARY）

制造锁：

begin;
select * from t1 where id <= 10 lock in share mode;

查询锁：

mysql> select
    ->   engine_transaction_id, object_name,
    ->   lock_type, lock_mode, lock_status, lock_data
    -> from performance_schema.data_locks
    -> where object_name = 't1' and lock_type = 'RECORD';
+-----------------------+-------------+-----------+-----------+-------------+-----------+
| engine_transaction_id | object_name | lock_type | lock_mode | lock_status | lock_data |
+-----------------------+-------------+-----------+-----------+-------------+-----------+
|       284330980665936 | t1          | RECORD    | S         | GRANTED     | 10        |
+-----------------------+-------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

1.5.4. 插入意向锁（`LOCK_INSERT_INTENTION`）

制造锁：

-- session 1
begin;
select * from t1 where id <= 10 lock in share mode;

-- session 2
begin;
insert into t1(id, i1) values (5, 51);

查询锁：

mysql> select
    ->   engine_transaction_id, object_name,
    ->   lock_type, lock_mode, lock_status, lock_data
    -> from performance_schema.data_locks
    -> where object_name = 't1' and lock_type = 'RECORD';
+-----------------------+-------------+-----------+------------------------+-------------+-----------+
| engine_transaction_id | object_name | lock_type | lock_mode              | lock_status | lock_data |
+-----------------------+-------------+-----------+------------------------+-------------+-----------+
|                  5658 | t1          | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10        |
|       284330980665936 | t1          | RECORD    | S                      | GRANTED     | 10        |
+-----------------------+-------------+-----------+------------------------+-------------+-----------+
2 rows in set (0.00 sec)

2. 案例

MySQL 在RR隔离级别下，锁比较难理解。今天结合《MySQL45讲》，以及MySQL 8.0提供的data_locks视图研究一下行锁到底是怎么加锁的。

本次实验使用的版本是8.0.33。不同的版本对于锁的表现可能有所不同。

案例直接抄的45讲，部分结论也是直接抄的。

行锁规则：

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。

1. RR基本单元是next-key lock
2. RC基本单元是record lock

原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

引用 MySQL 45讲。

加锁机制：InnoDB 行锁锁住的是索引页，回表时，主键聚簇索引也会加上锁。

data_locks视图字段含义：

锁类型	含义
X	Next-Key Lock，锁定记录本身和记录之前的间隙（X）
S	Next-Key Lock，锁定记录本身和记录之前的间隙（S）
X,REC_NOT_GAP	只锁定记录本身（X）
S,REC_NOT_GAP	只锁定记录本身（S）
X,GAP	间隙锁，不锁定记录本身（X）
S,GAP	间隙锁，不锁定记录本身（S）
X,GAP_INSERT_INTENTION	插入意向锁

造测试数据

drop database if exists lk;
create database lk;

use lk;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

注意每个实验之间建议重建测试数据。

那么根据上面提供的行锁规则和加锁机制，通过案例学习和研究InnoDB是如何加锁的。

2.1. 案例一：主键等值查询间隙锁

修改不存在的值

session A	session B	sessin C
begin;update t set d=d+1 where id=7;
	insert into t values(8,8,8);(blocked)
		update t set d=d+1 where id=10;(Qeury OK)

执行Session A语句之后，观察Session A持有的锁。

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139851353582808:1068:139851248750272
ENGINE_TRANSACTION_ID: 2395
            THREAD_ID: 6093
             EVENT_ID: 23
        OBJECT_SCHEMA: lk
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139851248750272
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139851353582808:6:4:4:139851248747216
ENGINE_TRANSACTION_ID: 2395
            THREAD_ID: 6093
             EVENT_ID: 23
        OBJECT_SCHEMA: lk
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139851248747216
            LOCK_TYPE: RECORD -- 锁类型为行锁
            LOCK_MODE: X,GAP -- 排他、间隙锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
2 rows in set (0.00 sec)

LOCK_DATA: 10 表示锁住的数据，不过因为GAP锁并不会锁住行本身，结合2+2+1（2原则、2优化、1BUG）解释：

根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

验证插入小于4的值，发现也可以插入。

mysql> insert into t values(4,4,4);
Query OK, 1 row affected (0.01 sec)

还原测试数据

drop database if exists lk;
create database lk;

use lk;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

2.2. 案例二：非唯一索引等值锁

Session A	Session B	Session C
begin;select id from t where c=5 lock in share mode;
	update t set d=d+1 where id=5;(Query OK)
		insert into t values(7,7,7);(blocked)

执行Session A语句之后，观察Session A持有的锁。

mysql> select index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+------------+-----------+-----------+-----------+
| index_name | lock_type | lock_mode | lock_data |
+------------+-----------+-----------+-----------+
| NULL       | TABLE     | IS        | NULL      |
| c          | RECORD    | S         | 5, 5      |
| c          | RECORD    | S,GAP     | 10, 10    |
+------------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。
要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。
根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

所以加锁范围是(0,5]，(5,10).

尝试插入(4,4,4)记录，也会被(0,5] next-key lock阻塞。

2.3. 案例三：主键索引范围锁

思考：如下SQL加锁范围一样么？

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

主键索引上范围查询的锁：

Session A	Session B	Session C
begin;select * from t where id>=10 and id<11 for update;
	insert into t values(8,8,8);(Query OK)insert into t values(13,13,13);(blocked)
		update t set d=d+1 where id=15;(blocked)

根据（2+2+1）分析：

开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。
根据优化 1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

所以加锁范围是：对于id=10加行锁，以及(10,15]加next-key lock。

下面查看data_locks：

mysql> select index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+------------+-----------+---------------+-----------+
| index_name | lock_type | lock_mode     | lock_data |
+------------+-----------+---------------+-----------+
| NULL       | TABLE     | IX            | NULL      |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 10        |
| PRIMARY    | RECORD    | X,GAP         | 15        |
+------------+-----------+---------------+-----------+
3 rows in set (0.00 sec)

我们发现id=10加了行锁没问题（X,REC_NOT_GAP），不过15这一行只是加了GAP Lock，并没有按2+2+1添加next-key lock。

通过实验也会发update t set d=d+1 where id=15;这个SQL语句并不会被阻塞。猜测8.0版本做了优化。

所以加锁范围是：对于id=10加行锁，以及(10,15)加gap lock。

2.4. 案例四：非唯一索引范围锁

需要注意的是，与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c。

非唯一索引范围锁：

Session A	Session B	Session C
begin;select * from t where c>=10 and c<11 for update;
	insert into t values(8,8,8);(blocked)
		update t set d=d+1 where c=15;(blocked)

根据（2+2+1）分析：

这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

下面查看data_locks：

mysql> select index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+------------+-----------+---------------+-----------+
| index_name | lock_type | lock_mode     | lock_data |
+------------+-----------+---------------+-----------+
| NULL       | TABLE     | IX            | NULL      |
| c          | RECORD    | X             | 10, 10    |
| c          | RECORD    | X             | 15, 15    |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 10        |
+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

第一行：表示意向锁，属于表锁。

第二行：next-key lock，范围是(5,10]，锁的对象是c索引

第三行：next-key lock，范围是(10,15]，锁的对象是c索引

第四行：record lock X，范围是[10]，锁的对象是主键

2.5. 案例五：唯一索引范围锁 bug

这里指的BUG是45讲中加锁规则提炼出来的2+2+1中的BUG：

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

当时45讲书写背景可能是5.6，所以本次案例并没有复现。

唯一索引范围锁的 bug：

Session A	Session B	Session C
begin;select * from t where id >10 and id<=15 for update;
	update t set d=d+1 where id=20;(blocked)
		insert into t values(16,16,16);(blocked)

根据（2+2+1）分析：

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

下面查看data_locks：

mysql> select index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+------------+-----------+-----------+-----------+
| index_name | lock_type | lock_mode | lock_data |
+------------+-----------+-----------+-----------+
| NULL       | TABLE     | IX        | NULL      |
| PRIMARY    | RECORD    | X         | 15        |
+------------+-----------+-----------+-----------+
2 rows in set (0.01 sec)

第一行：表示意向锁，属于表锁。

第二行：next-key lock，范围是(10,15]

8.0.33估计改了这个BUG，并不会阻塞Session B和Session C的SQL语句。

2.6. 案例六：非唯一索引上存在"等值"的例子

接下来的例子，是为了更好地说明“间隙”这个概念。这里，我给表 t 插入一条新记录。

mysql> insert into t values(30,10,30);

索引数据分布如下：

非唯一索引等值的例子

构造例子：

delete 示例

Session A	Session B	Session C
begin;delete from t where c=10;
	insert into t values(12,12,12);(blocked)
		update t set d=d+1 where c=15;(Query OK)

根据（2+2+1）分析：

这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。

delete 加锁效果示例

下面查看data_locks：

mysql> select index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+------------+-----------+---------------+-----------+
| index_name | lock_type | lock_mode     | lock_data |
+------------+-----------+---------------+-----------+
| NULL       | TABLE     | IX            | NULL      |
| c          | RECORD    | X             | 10, 10    |
| c          | RECORD    | X             | 10, 30    |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 10        |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 30        |
| c          | RECORD    | X,GAP         | 15, 15    |
+------------+-----------+---------------+-----------+
6 rows in set (0.00 sec)

第一行：表示意向锁，属于表锁。

第二行：next-key lock、锁范围是(c=5,id=5) 到 (c=10,id=10)，锁定对象是索引C

第三行：next-key lock、锁范围是(c=10,id=10) 到 (c=30,id=10)，锁定对象是索引C

第四行：record lock、锁范围是id=10行本身，，锁定对象是主键

第五行：record lock、锁范围是id=30行本身，锁定对象是主键

第六行：gap lock、锁范围是(c=30,id=10) 到 (c=315,id=15)，锁定对象是索引C

2.7. 案例七：limit 语句加锁

例子 6 也有一个对照案例，场景如下所示：

limit 语句加锁

Session A	Session B
begin;delete from t where c=10 limit 2;
	insert into t values(12,12,12);(Query OK)

根据（2+2+1）分析：

这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。

这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。

因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

带 limit 2 的加锁效果

下面查看data_locks：

mysql> select index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+------------+-----------+---------------+-----------+
| index_name | lock_type | lock_mode     | lock_data |
+------------+-----------+---------------+-----------+
| NULL       | TABLE     | IX            | NULL      |
| c          | RECORD    | X             | 10, 10    |
| c          | RECORD    | X             | 10, 30    |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 10        |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | 30        |
+------------+-----------+---------------+-----------+
5 rows in set (0.00 sec)

第一行：表示意向锁，属于表锁。

第二行：next-key lock、锁范围是(c=5,id=5) 到 (c=10,id=10)

第三行：next-key lock、锁范围是(c=10,id=10) 到 (c=30,id=10)

第四行：record lock、锁范围是id=10行本身

第五行：record lock、锁范围是id=30行本身

#第六行：gap lock、锁范围是(c=30,id=10) 到 (c=315,id=15)

发现第六行 gap lock不见了。所以insert可以成功。

2.8. 案例八：一个死锁的例子

案例八的操作序列

Session A	Session B
begin;select id from t where c=10 lock in share mode;
	update t set d=d+1 where c=10;(blocked)
insert into t values(8,8,8);
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactio

按操作时间序解析：

session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

你可能会问，session B 的 next-key lock 不是还没申请成功吗？

其实是这样的，session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

所以，RR相比RC隔离级别出现死锁的概率会更大，因为按照上面的案例以及分析，next-key lock加锁分两阶段，先加gap lock后加record lock，并且gap lock之间并不冲突，所以出现死锁的概率会相对RC隔离级别更大。

上面案例都是基于RR隔离级别下，如果是RC隔离级别会有什么变化？我们需要了解锁的持续周期，也就是需要了解锁什么时候会释放。在MDL锁中锁持有周期如下：

MDL_STATEMENT
MDL_TRANSACTION
MDL_EXPLICIT

InnoDB中锁的持有周期与MDL锁类似，不过有两类特殊情况：

事务运行过程中释放
事务提交时在2PC的prepare阶段就释放

下面是对于锁释放时机的总结：

表锁释放时机

事务执行过程中加的表锁（手动加锁除外），在事务提交或回滚即将完成时释放。

行锁释放时机（根据事务隔离级别）

可重复读（REPEATABLE-READ） 和 可串行化（SERIALIZABLE） ：

- 所有行锁需等到事务提交或回滚即将完成时才释放。

读未提交（READ-UNCOMMITTED） 和 读已提交（READ-COMMITTED） ：

- 不匹配 WHERE 条件的记录：发现不匹配后，Server 层或 InnoDB 层会立即释放行锁（取决于查询条件是否下推到引擎层）。
- 间隙锁（Gap Lock）或 Next-Key 锁：在二阶段提交的 prepare 阶段会释放记录前面间隙的锁，但保留记录本身的锁（在外键约束和唯一索引重复值检查时产生）。
- 其余行锁：需等到事务提交或回滚即将完成时才释放。

AUTO-INC（自增锁）释放时机

轻量锁：用完即释放。
真正的表级 AUTO-INC 锁：在SQL 语句执行完成时释放。

3. 总结

结合Server和InnoDB层，对锁做个总结。

回顾MDL锁加锁流程：

select

- TABLE-TRANSACTION-SR

insert/update/delete

- 在open_table阶段获取GLOBAL-STATEMENT-IX、TABLE-TRANSACTION-SW
- 在commit阶段获取COMMIT-MDL_EXPLICIT-IX

那么一个SQL完整的加锁流程从MDL锁开始到InnoDB层加表锁或者行锁，要根据隔离级别以及查询是否命中主键、唯一索引、普通二级索引、查询的数据是否存在、范围查询、查询的是覆盖索引、有没有limit，等诸多因素影响：

参考文献：

从一道数据库面试题彻谈MySQL加锁机制

MySQL 核心模块揭秘 | 16 期 | InnoDB 表锁

《MySQL 45讲》

MySQL 自增锁 (Auto-Increment Lock) 的原理详解

MySQL 锁三部曲（2） - InnoDB 锁

1. 概述

1.1. 锁分类

1.2. 意向锁

1.3. 表锁

1.4. 自增锁（AUTO-INC）

1.5. InnoDB 行锁

1.5.1. 普通记录锁（LOCK_REC_NOT_GAP）

1.5.2. 间隙锁（LOCK_GAP）

1.5.3. Next-Key 锁（LOCK_ORDINARY）

1.5.4. 插入意向锁（LOCK_INSERT_INTENTION）

2. 案例

2.1. 案例一：主键等值查询间隙锁

2.2. 案例二：非唯一索引等值锁

2.3. 案例三：主键索引范围锁

2.4. 案例四：非唯一索引范围锁

2.5. 案例五：唯一索引范围锁 bug

2.6. 案例六：非唯一索引上存在"等值"的例子

2.7. 案例七：limit 语句加锁

2.8. 案例八：一个死锁的例子

3. 总结

1.5.4. 插入意向锁（`LOCK_INSERT_INTENTION`）