MySQL INNODB是怎么加锁的？

怎么查询？

查询无处不在，无论是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的最大节点。

测试环境

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、唯一索引等值查询，存在匹配行，只加行锁。

测试环境

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执行：（注意先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 更新成功。

唯一索引树保证互斥

测试环境

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; 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 退化为间隙锁。

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

测试环境

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> 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不同的记录

	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> insert into tt(b) values(6); Query OK, 1 row affected (0.04 sec)
4	commit;	commit;

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

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

在RR级别下

并发事务

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

从上面可以看出，并发事务都成功执行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锁

	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> select * from tt where b >4 and b <8 lock in share mode;锁等待
4	commit;
		±—±-----+ | a | b | ±—±-----+ | 12 | 5 | ±—±-----+ 1 row in set (6.90 sec)commit;

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

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

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

	事务1	事务2	事务3
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> insert into tt(b) values(5);锁等待	mysql> insert into tt(b) values(5);锁等待
4	mysql> rollback; Query OK, 0 rows affected (0.00 sec)
		mysql> insert into tt(b) values(5); Query OK, 1 row affected (37.17 sec)	mysql> insert into tt(b) values(5); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction死锁发生了

当事务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形成死锁。

锁兼容矩阵