SleepyFancy 2021-02-22 17:06 采纳率: 0%
浏览 22

InnoDB 锁问题 next-key locking

有表如下:

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 做了区别的对待？

  • 写回答

1条回答 默认 最新

  • 树莓大王 2023-03-14 23:11
    关注

    这是因为在事务 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。

    评论

报告相同问题？