MySQL INNODB是怎么加锁的？

Bertha 。 2022-11-25 00:54 189阅读 0赞

# 怎么查询？ #

查询无处不在，无论是select 还是update、delete，首先要做的就是查询是否存在目标记录。

innodb的表是由几棵索引树组成的，首先有一颗主键索引树，每行完整数据只存在于其叶子节点上，非叶子节点仅用于排序；然后还有唯一索引树和普通索引树，唯一索引树和普通索引树的叶子节点仅存储索引值和主键值，因此通过唯一索引或普通索引查询时，可能需要再根据主键索引值查询主键索引树，这也就是所谓的回表查询。

索引树均是向上排序，即左边的叶子节点一定比右边的叶子节点的小，普通索引树中相等的几个索引值的叶子节点按照主键排序。

**查询时，无论什么where条件都需要先定位，即从哪个叶子节点开始遍历。**

查询可分为等值查询和范围查询，即只有等于号的是等值查询，存在大于或小于的是范围查询。

如果查询条件中没有使用任何索引，那只能遍历主键索引树的所有叶子节点，即定位到主键索引树中的最小叶子节点，并向右遍历并判断是否满足条件直到**上确界节点（supremum）**，即全表扫描。

**索引等值查询**

根据索引值可直接定位到值相等的叶子节点，或者定位到第一个大于它的节点，甚至是**上确界节点（supremum）**，然后MySQL判断定位的节点满足等式，如果不满足则结束遍历; 如果满足，当是唯一索引时则结束遍历；当是普通索引仍会继续向右遍历直到第一个大于的节点，因为普通索引可重复。

**索引范围查询**

首先定位到满足条件的**最小叶节点**或者**infimum**，然后向右遍历直到第一个不满足条件的节点或者**上确界节点（supremum）**；比如 where idx\_f > 2 and idx\_f < 5 则定位到2；如果2不存在则定位到右边第一个大于2的最小节点。

但是如果order by $\{index\_field\} desc，则首先定位到满足条件的**最大叶节点**或者**supremum**，然后向左遍历直到第一个不满足条件的节点或者**下确界节点（infimum）**；比如 where idx\_f > 2 and idx\_f < 5 order by idx desc则定位到5；如果5不存在则定位到左边第一个小于5的最大节点。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V3ZW5qdW4wODIw_size_16_color_FFFFFF_t_70]

# 测试环境 #

Linux 操作系统  
MySQL 5.7.18  
测试用例中如果没有显式配置事务隔离级别，均是可重复读隔离级别。

CREATE TABLE `t` (
    	`id` INT(11) NOT NULL,
    	`uk_f` INT(11) NULL DEFAULT NULL,
    	`idx_f` INT(11) NULL DEFAULT NULL,
    	`normal_f` INT(11) NULL DEFAULT NULL,
    	PRIMARY KEY (`id`),
    	UNIQUE INDEX `c` (`uk_f`),
    	INDEX `d` (`idx_f`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    ;
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (0, 0, 0, 0);
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (5, 5, 5, 5);
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (10, 10, 10, 10);
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (15, 15, 15, 15);
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (20, 20, 20, 20);
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (25, 25, 25, 25);
    INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (30, 30, 30, 30);

# 什么是行锁？ #

**一个事务对已存在的行加的锁叫做行锁**，准确来说是一个事务对与存在的索引树叶子节点加的锁叫做行锁。

mysql> begin; select * from t where idx_f = 5 for update; 
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  5 |    5 |     5 |        5 |
    +----+------+-------+----------+

另一个事务：

mysql> update t set idx_f = 50 where idx_f = 5;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这里等待的锁就是行锁，这个锁是加载 idx\_f 索引树的值等于5的叶子节点上的。

# 什么是间隙锁？为什么需要间隙锁？ #

测试环境

mysql> select * from t;
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  0 |    0 |     0 |        0 |
    |  5 |    5 |     5 |        5 |
    | 10 |   10 |    10 |       10 |
    | 15 |   15 |    15 |       15 |
    +----+------+-------+----------+

### 读提交隔离级别不解决幻读问题 ###

session1执行：

mysql> set tx_isolation = 'read-committed';
    Query OK, 0 rows affected (0.00 sec)
    mysql> begin; select * from t where normal_f = 5 for update; 
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  5 |    5 |     5 |        5 |
    +----+------+-------+----------+
    mysql> update t set idx_f = 50 where normal_f = 5;
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session1首先设置读提交，该事务隔离级别不会添加间隙锁，只会添加行锁。该事务试图锁住所有 normal\_f = 5 的行。

在session2中执行：

mysql> set tx_isolation = 'read-committed';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin; update t set idx_f = 100 where normal_f = 5; 
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (100, 100, 100, 5);    
    Query OK, 1 row affected (0.00 sec)
    
    mysql> update t set normal_f = 5 where normal_f = 10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session2中直接更新已存在的 **normal\_f** 等于5的行，显示等待锁超时，说明session1会加行锁，

但是session2中插入 **normal\_f** 等于5的行或者通过更新其他行的 **normal\_f** 为5都是成功的。

如果session1在执行update之前重新执行select \* for update ，则可能看到session2 插入的 **normal\_f** 等于5的新记录，这就是幻读！

**读提交隔离级别只加行锁，仅对存在记录加锁，其他session可通过insert或update的方式造成幻读！**

### 读提交隔离级别下的binlog必须使用raw格式 ###

并且session2在session1之前提交，那么现在session1执行commit后结果如下：

mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from t;
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  0 |    0 |     0 |        0 |
    |  4 |    4 |     5 |        4 |
    |  5 |    5 |    50 |        5 |
    | 10 |   10 |    10 |       10 |
    +----+------+-------+----------+
    8 rows in set (0.00 sec)

因为session后提交，因此在binlog中的statement的顺序是这样的：

INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (4, 4, 4, 4);    
    update t set idx_f = 5 where id =4;
    update t set idx_f = 50 where idx_f = 5;

根据binlog，执行结果不应该继续存在idx\_f等于5的记录，但是实际结果和binlog的逻辑冲突，如果此时从库根据binlog执行，那么将造成主从不一致，因此读提交隔离级别下，必须使用raw格式的binlog。raw格式记录的不是statement，而是记录更新前后的值，例如：

事务1：
    更新前：空
    更新后：(`id`, `idx_f`) VALUES (4, 4)
    
    事务2：
    更新前：(`id`, `idx_f`) VALUES (4, 4)
    更新后：(`id`, `idx_f`) VALUES (4, 5)
    
    事务3：
    更新前：(`id`,  `idx_f`) VALUES (5, 5)
    更新后：(`id`,  `idx_f`) VALUES (5, 50)

因此从库根据binlog执行后和主库也会保持一致。

### 可重复读隔离级别解决幻读问题 ###

通过间隙锁，比如session1中执行

mysql> begin; select * from t where normal_f = 5 for update;

不仅会对**normal\_f = 5** 的已存在的行加锁，还对所有行之间的间隙加锁，防止并发插入和更新（更新可看做先delete后insert），这就是间隙锁。

# 可重复读怎么加锁？ #

## 1、每次加锁的单位都是next-key lock ##

**什么是next-key lock？**

比如存在下表：

mysql> select * from t ;
    Query OK, 0 rows affected (0.00 sec)
    
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  5 |    5 |     5 |        5 |
    | 10 |   10 |    10 |       10 |
    | 15 |   15 |    15 |       15 |
    +----+------+-------+----------+
    
    select * from test where idx_f < 100 for update;

在访问过值5的节点后，继续访问值10的节点，会对 (5,10\]的区间加锁，这就是next-key锁， 换句话说就是（5,10）这个间隙锁和idx=10的行锁。

以下我们说对索引树上某个叶节点加锁，都是说加next-key锁，即该节点右侧的间隙锁+该节点上的行锁。

加锁一定是加在索引树叶子节点上的，无论是主键索引树还是唯一索引树、普通索引树。

## 2、查询过程访问到的叶节点都会加锁 ##

无论是select for update 还是 update 首先都会定位，如果where条件没有用到任何索引，查询会遍历主键索引的所有叶子节点，如果where条件中有索引，则首先在索引树中定位，然后开始遍历，直到访问到一个条件不满足的节点。无论是遍历主键叶子节点还是唯一索引或者普通索引的叶子节点，每访问一个叶节点，都会对这个叶节点加锁！即使访问到的是不满足条件的节点，也会加锁。

因为是逐个加锁，所以如果两个事务分别执行下面两句sql，则很可能发生死锁:

update t set normal_f  = 100 where uk_f in (5,25);
    update t set normal_f  = 100 where uk_f in (25,5);

### update中的子查询也会加锁 ###

会话1执行：

mysql> begin; update t, (select count(*) from t where normal_f = 1) tc set normal_f = 100;

会话2执行：

mysql> update t set normal_f = 101 where normal_f=20;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

因为会话1中的存在子查询，而且是全表扫描，导致锁表！

### 索引范围查询,总会遍历到不满足条件的节点 ###

无论是普通索引还是唯一索引，定位后都会默认向右遍历直到条件不满足。

会话1执行：

mysql> begin; select * from t where  uk_f > 6 and uk_f <= 15  for update;
    Query OK, 0 rows affected (0.00 sec)
    
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    | 10 |   10 |    10 |       10 |
    | 15 |   15 |    15 |       15 |
    +----+------+-------+----------+
    1 row in set (0.00 sec)

首先定位到节点10，则添加next-key lock : (5,10\];

继续向右遍历到节点15，则添加next-key lock: (10,15\];

继续向右遍历到节点20，则添加next-key lock: (15,20\];

因不满足条件则结束遍历。

会话2执行：

mysql> update t set normal_f = 101 where uk_f=20;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

**说明通过唯一索引对节点20进行更新时，会返回加锁超时！所以这也验证了只要访问过的节点，无论是否满足条件都会加锁**

**唯一索引等值查询，如果存在匹配行，则遍历结束；比如上面的select for update，当遍历到节点15，而条件时<=15，所以继续向右遍历是没有意义的，但是实际情况确没有停下来，多加了一个next-key锁，这可能也是MySQL的一个bug吧**

会话2执行：

mysql> update t set uk_f = 101 where id=15;          
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

说明通过主键试图修改唯一索引也不会成功，也会加锁超时！

会话2执行：

mysql> update t set normal_f = 100 where id = 15; 
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

可发现通过主键15更新其他列可以成功，**这也说明了锁是加在唯一索引上的**！

### 索引范围查询 desc 向左遍历 ###

会话1执行：

mysql> begin; select * from t where  uk_f > 6 and uk_f < 13 order by uk_f desc for update;
    Query OK, 0 rows affected (0.00 sec)
    
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    | 10 |   10 |    10 |       10 |
    +----+------+-------+----------+
    1 row in set (0.00 sec)

会话2执行：

mysql> update t set uk_f=101 where uk_f=15;            
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
    
    mysql> update t set uk_f=101 where uk_f=5;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

说明定位到节点10后，向左遍历到节点5，因此 next-key lock 是 (0,5\]。

这是通过验证左边第一个不满足条件的叶子节点被加锁，来反正desc 是 定位后向左遍历。

## 3、唯一索引等值查询，存在匹配行，只加行锁。 ##

测试环境

session1执行：（注意先commit）

mysql> begin; select * from t where uk_f =5 for update;   
    Query OK, 0 rows affected (0.00 sec)
    
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  5 |    5 |     5 |        5 |
    +----+------+-------+----------+
    1 row in set (0.00 sec)

session2执行：

mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (1, 1, 1, 1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> update t set normal_f = 101 where uk_f = 5; 
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (6, 6, 6, 6);   
    Query OK, 1 row affected (0.01 sec)

session2执行结果显示session1只对索引值等于5的行加了行锁。

**为什么可以不加间隙锁？**

即使不加间隙锁，其他session也无法插入一行索引值等于5的行，也无法修改其他行的索引值为5，因为唯一索引树本身就不允许索引值重复，**对唯一索引值的操作本身就是互斥的**！根本不需要通过间隙锁来保证互斥。

如果session2 执行：

INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (4, 4, 4, 4);
    update t set uk_f = 5 where id = 4;

在session2等待锁的过程中，session1执行commit;那么session2立刻返回：

ERROR 1062 (23000): Duplicate entry '5' for key 'c'

说明发生了主键冲突，但是如果session2 执行：

INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (4, 4, 4, 4);
    update t set uk_f = 5 where id = 4;

在等待锁的过程中，session1执行了：

mysql> update t set uk_f = 101 where uk_f = 5;    
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

那么session2立刻返回：

mysql> update t set uk_f = 5 where id = 4;
    Query OK, 1 row affected (24.58 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

因为session释放了行锁，当前唯一索引数上不存在5，所以session2 更新成功。

### 唯一索引树保证互斥 ###

测试环境

session1执行：

mysql> set tx_isolation = 'read-committed';
    Query OK, 0 rows affected (0.00 sec)
    mysql> begin; update t set uk_f = 50 where uk_f = 5;
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session1首先设置读提交，该事务隔离级别不会添加间隙锁，只会添加行锁。

在session2中执行：

mysql> begin; update t set idx_f = 100 where idx_f = 5;  
    Query OK, 0 rows affected (0.00 sec)
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (4, 4, 4, 4);    
    Query OK, 1 row affected (0.00 sec)
    
    mysql> update t set uk_f = 5 where id =4;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在读提交隔离级别下，没有了间隙锁的帮助，

session2更新索引值等于5的行等待锁超时，说明session1会加行锁，

**但是session2中通过先插入后更新的方式插入一条索引值等于5的记录，也等待锁超时，这个锁并不是间隙锁，而是唯一索引树上的锁**

## 4、普通索引等值查询，向右遍历最后一个节点，当不满足等值条件的时候，next-key lock 退化为间隙锁。 ##

等值查询只会出现向右遍历。

测试环境

session1执行：

mysql> begin; select id from t where idx_f = 5 for update; 
    Query OK, 0 rows affected (0.00 sec)
    
    +----+
    | id |
    +----+
    |  5 |
    +----+
    1 row in set (0.00 sec)

在session2中执行：

mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (1, 1, 1, 1);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> update t set normal_f = 101 where idx_f = 5; 
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (6, 6, 6, 6);   
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> update t set normal_f = 501 where idx_f = 10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

普通索引等值查询，首先定位到值5节点，对(0,5\]加锁，然后向右遍历到值10节点，对(5,10\]加锁，但是因为最后这个节点值不满足等式，所以**next-key(5,10\]退化为间隙锁（5,10）**，session2的直接结果也验证了这一点。

**为什么加行锁？**

如果不加行锁，其他session可能修改索引等于5的行，那么就和session1锁住所有等于5的行的语义相悖。

**为什么加间隙锁？**

为什么唯一索引等值查询，不需要加间隙锁，而普通索引需要加间隙锁？

如果不加间隙锁，其他session可能再插入一条索引值为5的行或者更新某行的索引值为5（普通索引数允许索引重复，如果没有锁，那么其他session插入索引值为5时，普通索引树无法确定这次插入是有问题的，换句话说普通索引数中对普通索引的操作并不是互斥的），那么就和session1锁住所有等于5的行的语义相悖，如果session1重新执行select for update则出现幻读。

### 普通索引树不保证互斥 ###

session1执行：

mysql> set tx_isolation = 'read-committed';
    Query OK, 0 rows affected (0.00 sec)
    mysql> begin; select * from t where idx_f = 5 for update; 
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  5 |    5 |     5 |        5 |
    +----+------+-------+----------+
    mysql> update t set idx_f = 50 where idx_f = 5;
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session1首先设置读提交，该事务隔离级别不会添加间隙锁，只会添加行锁。

在session2中执行：

mysql> update t set idx_f = 100 where idx_f = 5;  
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (100, 100, 100, 5);    
    Query OK, 1 row affected (0.00 sec)
    
    mysql> update t set idx_f = 5 where id =10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

在读提交隔离级别下，没有了间隙锁的帮助，

session2更新已存在的普通索引值等于5的行，显示等待锁超时，说明session1会加行锁，

**但是session2中仍可以通过insert 或者 update 来导致幻读，这也说明普通索引和普通列在幻读问题上一模一样，而且普通索引树不保证互斥。**

所以在可重复读隔离级别下，对于普通索引也需要通过间隙锁来避免幻读！

## 5、索引等值查询，不存在匹配行，next-key锁优化为间隙锁 ##

测试环境：

mysql>  select * from t;      
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  0 |    0 |     5 |        0 |
    |  5 |    5 |     5 |        5 |
    | 10 |   10 |    10 |       10 |
    +----+------+-------+----------+

session1执行：

mysql> begin; select * from t where uk_f = 2 for update; 
    Query OK, 0 rows affected (0.00 sec)

session2执行：

mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (3, 3, 3, 3);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    mysql> update t set normal_f = 101 where uk_f = 5;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session2 插入失败却更新成功，说明session1仅对（0,5）加锁。

**为什么可以不加行锁？**

因为session2更新索引值等于5的行非索引列，不会影响session1的语义。但是如果执行：

mysql> update t set uk_f = 2 where uk_f = 5;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

也是会失败的，因为这等待的是间隙锁（0,5）。

## 6、索引范围查询，不存在匹配行，则对(infimum，minValue\]加next-key锁 ##

初始环境：

mysql> select * from t;
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  6 |    6 |     6 |        6 |
    | 30 |   30 |    30 |       30 |
    +----+------+-------+----------+
    2 rows in set (0.00 sec)

session1 执行：

mysql> begin; select * from t where idx_f > 2 and idx_f < 5 for update;
    Query OK, 0 rows affected (0.00 sec)
    
    Empty set (0.00 sec)

session2 执行：

mysql> 
    mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (5, 5, 5, 5);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2 尝试向(infimum, 6)插入索引值5，显示等待锁超时，session2继续 执行：

mysql> update t set normal_f=601 where idx_f=6;     
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2 尝试更新索引值6对应的行，显示等待锁超时，session2继续 执行：

mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (7, 7, 7, 7);
    Query OK, 1 row affected (0.01 sec)

session2 尝试插入索引值7，立刻成功。

综上所述：当索引范围查询时加锁，如果无满足条件的节点时，会对(infimum, mimValue\]加锁；

为什么加锁？

因为session1执行select for update的返回的是空，如果不对（infimum，6\]加锁，那么其他session可能查询索引值等于3或者4的行，那么就和session1试图锁住大于2小于5的语义相悖，如果session1重新执行select for update则出现幻读。

加锁顺序是什么？

首先根据where条件发现不存在满足条件的节点，因此定位到infimum节点，然后向右遍历到索引值6，条件不满足则遍历结束，因此对（infimum，6\]加锁。

## 7、空表第一次加锁都相当于表锁 ##

测试环境：

mysql> select * from t;
    Empty set (0.00 sec)

session1 执行：

mysql> begin; select * from t where idx_f > 10000 for update;                       
    Query OK, 0 rows affected (0.00 sec)
    
    Empty set (0.00 sec)

session2 执行：

mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (7, 7, 7, 7);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

插入任意一行都会等待锁。

需注意的是此时执行update和delete都不会等待锁，因为根本没有匹配的行。

为什么加锁？

因为session1执行select for update的返回的是空，如果不加锁，那么其他session可能查询索引值等于20000的行，那么就和session1试图锁住大于10000的语义相悖，如果session1重新执行select for update则出现幻读，而现在表中没有数据因此只能加表锁。

加锁顺序是什么？

首先根据where条件发现不存在满足条件的节点，因此定位到infimum节点，然后向右遍历到supremum节点，条件不满足则遍历结束，因此对（infimum，supremum\]加锁。

## 8、limit缩小锁范围 ##

测试环境：

mysql> select * from t;
    +----+------+-------+----------+
    | id | uk_f | idx_f | normal_f |
    +----+------+-------+----------+
    |  0 |    0 |     0 |        0 |
    |  5 |    5 |     5 |        5 |
    | 9  |   9  |    10 |       10 |
    | 10 |   10 |    10 |       10 |
    | 15 |   15 |    15 |       15 |
    +----+------+-------+----------+

session1 执行：

mysql> begin; delete from t where idx_f < 15 limit 4;                       
    Query OK, 4 rows affected (0.00 sec)

这条语句加不加limit4实际都会删除4行记录，但是：

session2 执行：

mysql> INSERT INTO `t` (`id`, `uk_f`, `idx_f`, `normal_f`) VALUES (11, 11, 11, 11);    
    Query OK, 1 row affected (0.00 sec)
    
    mysql> update t set normal_f = 501 where idx_f = 15;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

说明没有对next-key（10,15】加锁。这个范围查询，条件时小于15，所以从infimum节点开始遍历，当遍历到id=10这一行时，已满足limit的条件，因此不会继续向右遍历，所以不会访问节点15。

# 加锁实战 #

## 插入意向锁 ##

### 概念 ###

官方文档对于insert 加锁的描述贴出来

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

大体的意思是：**insert会对插入成功的行加上排它锁**，**这个排它锁是个记录锁，而非next-key锁（当然更不是gap锁了**），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁（简称I锁吧），并发的事务可以对同一个gap加I锁。

如果insert 的事务出现了duplicate-key error ，事务会对duplicate index record加**共享锁**。这个共享锁在并发的情况下可能会产生死锁的，比如有两个并发的insert都对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的insert操作因为彼此，都无法从共享锁升级为插入意向锁，是会发生死锁的。

### 插入的过程 ###

假设现在有记录 10， 30 ；且为**主键** ，需要插入记录 25 。

1.  找到大于等于25的第一个记录，即30
2.  判断记录30 上是否有锁， 上面如果有`Gap Lock`/`Next-Key Lock`，则无法插入，因为锁的范围是（10, 30\] ；在30上增加`insert intention lock`（ 此时将处于waiting状态），当 Gap Lock / Next-Key Lock 释放时，等待的事务（ transaction）将被 唤醒 。
3.  如果没有其他事务也要插入记录25，则该事务成功插入。
4.  如果其他同时还有其他事务也要插入记录25，那么将发生duplicate-key error，因此两个事务都会加S锁，

### 场景演示 ###

演示表如下：

CREATE TABLE `tt` (
      `a` int(11) NOT NULL AUTO_INCREMENT,
      `b` int(11) DEFAULT NULL,
      PRIMARY KEY (`a`),
      KEY `idx_b` (`b`)
    ) ENGINE=InnoDB;
    select * from tt;
    +----+------+
    | a  |   b  |
    +----+------+ 
    |  1 |   2  |  
    |  2 |   4  |   
    |  3 |   8  |   
    +----+------+

#### 场景一：两个并发插入到相同gap不同的记录 ####

<table> 
 <thead> 
  <tr> 
   <th align="left"></th> 
   <th align="left">事务1</th> 
   <th align="left">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">mysql&gt; insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left"></td> 
   <td align="left">mysql&gt; insert into tt(b) values(6); Query OK, 1 row affected (0.04 sec)</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">commit;</td> 
   <td align="left">commit;</td> 
  </tr> 
 </tbody> 
</table>

这个场景证明，对于同一个gap，I锁是不冲突的，事务1和事务2没有锁等待，都插入成功。

#### 场景二：插入意向锁和间隙锁冲突 ####

在RR级别下

**并发事务**

<table> 
 <thead> 
  <tr> 
   <th>T1</th> 
   <th>T2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin；</td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>delete from tt where b = 3;//ok, 0 rows affected</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>delete from tt where b = 3; //ok, 0 rows affected</td> 
  </tr> 
  <tr> 
   <td>insert into tt(b) values( 3);//wating,被阻塞</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into tt(b) values( 3);//wating,被阻塞 //ERROR 1213 (40001): Deadlock found when trying to get lock;</td> 
  </tr> 
  <tr> 
   <td>T1执行完成， 1 rows affected</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

从上面可以看出，并发事务都成功执行delete后（影响行数为0），执行insert出现死锁。

**等待锁分析**

查看死锁日志，显示事务T1的insert语句在等待插入意向锁，`lock_mode X locks gap before rec insert intention waiting`;事务T2持有b=4的gap lock，同时也在等待插入意向锁。另外，T1能执行delete，说明它也拿到了gap lock，所以，两个事务都持有gap lock，导致循环等待插入意向锁而发生死锁。

**加锁分析**

1.  delete的where子句没有满足条件的记录，而**对于不存在的记录并且在RR级别**下，delete加锁类型为gap lock，gap lock之间是兼容的，所以两个事务都能成功执行delete，这里的gap范围是索引b列(2,4)的范围。
2.  insert时，其加锁过程为先在插入间隙上获取插入意向锁，插入数据后再获取插入行上的排它锁。又因为插入意向锁与gap lock和 Next-key lock冲突，即一个事务想要获取插入意向锁，如果有其他事务已经加了gap lock或 Next-key lock，则会阻塞。
3.  场景中两个事务都持有gap lock，然后又都申请这个间隙内的插入意向锁，循环等待造成死锁。

**以上测试在RC级别下，不会发生锁！**

#### 场景三：对插入后的行加X锁 ####

<table> 
 <thead> 
  <tr> 
   <th align="left"></th> 
   <th align="left">事务1</th> 
   <th align="left">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">mysql&gt; insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left"></td> 
   <td align="left">mysql&gt; select * from tt where b &gt;4 and b &lt;8 lock in share mode;锁等待</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">commit;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left">±—±-----+ | a | b | ±—±-----+ | 12 | 5 | ±—±-----+ 1 row in set (6.90 sec)commit;</td> 
  </tr> 
 </tbody> 
</table>

事务2要等待的锁的类型是S gap锁，加锁的间隙是（4，8），这个锁被事务1的X锁组塞，所以可以确认insert插入后是会加排它锁，这里可以通过修改事务2的语句，确定出insert 插入后加的是记录锁（这里就不列出具体的演示场景了）。

#### 场景四：并发insert发生duplicate-key error导致死锁 ####

**演示前先tt表的b字段改成unique key。**

<table> 
 <thead> 
  <tr> 
   <th align="left"></th> 
   <th align="left">事务1</th> 
   <th align="left">事务2</th> 
   <th align="left">事务3</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
   <td align="left">mysql&gt; begin; Query OK, 0 rows affected (0.00 sec)</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">mysql&gt; insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)</td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left"></td> 
   <td align="left">mysql&gt; insert into tt(b) values(5);锁等待</td> 
   <td align="left">mysql&gt; insert into tt(b) values(5);锁等待</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">mysql&gt; rollback; Query OK, 0 rows affected (0.00 sec)</td> 
   <td align="left"></td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left"></td> 
   <td align="left">mysql&gt; insert into tt(b) values(5); Query OK, 1 row affected (37.17 sec)</td> 
   <td align="left">mysql&gt; insert into tt(b) values(5); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction死锁发生了</td> 
  </tr> 
 </tbody> 
</table>

当事务1 rollback后，事务2和事务3发生死锁。通过show engine innodb status查看死锁日志如下：

\------------------------
    LATEST DETECTED DEADLOCK
    \------------------------
    150109 9:59:59
    *** (1) TRANSACTION:
    TRANSACTION 9D96295F, ACTIVE 19 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 1675150, OS thread handle 0x7f5181977700, query id 1001786133 192.168.148.68 q3boy update
    insert into tt(b) values(5)
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 48562 page no 4 n bits 80 index `ux_b` of table `testdg`.`tt` trx id 9D960FD9 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    0: len 4; hex 80000008; asc ;;
    1: len 4; hex 80000001; asc ;;
    
    *** (2) TRANSACTION:
    TRANSACTION 9D962A68, ACTIVE 9 sec inserting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 1675251, OS thread handle 0x7f518055e700, query id 1001790623 192.168.148.68 q3boy update
    insert into tt(b) values(5)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 48562 page no 4 n bits 80 index `ux_b` of table `testdg`.`tt` trx id 9D960FC9 lock mode S locks gap before rec
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    0: len 4; hex 80000008; asc ;;
    1: len 4; hex 80000001; asc ;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 48562 page no 4 n bits 80 index `ux_b` of table `testdg`.`tt` trx id 9D962A68 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    0: len 4; hex 80000008; asc ;;
    1: len 4; hex 80000001; asc ;;

从上面死锁日志，我们可以很容易理解死锁为何发生。事务1插入记录，事务2插入同一条记录，主键冲突，事务2将事务1的隐式锁转为显式锁，同时事务2向队列中加入一个s锁请求，事务3同样也加入一个s锁请求；

当事务1回滚后，事务2和事务3获得s锁，但随后事务2和事务3又先后请求插入意向锁，因此锁队列为：

事务2(S GAP)<—事务3(S GAP)<—事务2(插入意向锁)<–事务3(插入意向锁)

事务3,事务2,事务3形成死锁。

# 锁兼容矩阵 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V3ZW5qdW4wODIw_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V3ZW5qdW4wODIw_size_16_color_FFFFFF_t_70]: /images/20221124/2565784b39ac47e192a67d931f58723d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V3ZW5qdW4wODIw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/e7b9c60729c049e7b5716a519bc4deeb.png