有表如下:
CREATE TABLE `test_next_key` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
+----+------+
事务 A 执行如下语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_next_key where b=3 for update;
+---+------+
| a | b |
+---+------+
| 5 | 3 |
+---+------+
1 row in set (0.00 sec)
mysql> insert into test_next_key select 4,2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test_next_key select 6,5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
select * from test_next_key where b=3 for update; 为 b 索引上 next-key locking (1,3],(3,6)
为什么 insert 4,2 被阻塞，而 insert 6,5 可成功插入?
若仅在事务 A 执行
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_next_key where b=3 for update;
+---+------+
| a | b |
+---+------+
| 5 | 3 |
+---+------+
1 row in set (0.00 sec)
同时在事务 B 执行
mysql> insert into test_next_key select 4,2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test_next_key select 6,5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
事务 B 两条都插入不成功。
加锁应该没有区别，难道是优化器根据不同事务的 SQL 做了区别的对待？
这是因为在事务 A 中，由于执行了 select * from test_next_key where b=3 for update; 这条语句，导致 b 索引上的值为 3 的行被加上了 next-key locking (1,3],(3,6)。这里的 (1,3] 表示小于等于 1, 大于 3 的所有行，而 (3,6) 表示大于 3，小于等于 6 的所有行。
当事务 A 在阻塞等待事务 B 的 insert 操作的时候，事务 B 尝试插入 (4,2) 这条记录，但是这会触发 b 索引上的 next-key locking，因为在 b 索引上 (3,6) 区间内还有被事务 A 加锁的记录，所以该 insert 操作被阻塞。
而当事务 B 尝试插入 (6,5) 这条记录时，由于它不在任何 next-key locking 的区间内，所以可以直接成功插入。
需要注意的是，在事务 A 还未提交之前，事务 B 执行的任何 insert、update 操作都会被阻塞。这是因为事务 A 中的 next-key locking 涉及到了 b 索引上的一段连续区间，包括了事务 B 尝试操作的部分。只有当事务 A 提交完成后，才会释放对 b 索引的 next-key locking。