MySQL间隙锁推理

Question

我遇到了死锁问题，并尝试找出其背后的原因。

问题可以简化为：

表：

create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));

事务隔离级别为可重复读

(Tx1): begin; delete from testdl where c = 1000; -- 如果表为空，则不会删除任何内容

(Tx2): begin; insert into testdl (c) values (?);

无论Tx2中的值是什么，它都会挂起。这基本上意味着当delete from testdl where c = 1000无法找到匹配项时，Tx1持有整个范围（-∞, +∞）的间隙，是吗？

所以我的问题是：这是设计缺陷吗？如果是的话，这样做的目的是什么？

更新：

假设我们已经在testdl中拥有一条记录：

+----+------+
| id | c    |
+----+------+
|  1 | 1000 |
+----+------+

案例1：

(Tx1): select * from testdl where c = 500 for update; -- c = 500不存在

(TX2): insert into testdl (c) values (?);

在这种情况下，任何值 >= 1000 都可以被插入，所以 Tx1 锁定了间隙(-∞, 1000)

同样，在 (-∞, 1000) 上加锁是必要的吗？这背后的原因是什么？

- Dean Winchester

---

你可能想要查看这个链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html - Gonzalo.-

对我而言令人困惑的是，当使用select * from testdl where c = 500 for update无法匹配记录时，我却无法插入一个c = 100的记录。毕竟，我选择了使用c = 500 for update，而不是c < 500。如果允许插入一个c = 100的记录，会有什么问题吗？这是因为某些原因吗？或者只是一种实现方式，实际上不会有任何问题发生？ - Dean Winchester

锁定锁实际上是锁定实体，而没有可用于锁定的500个“实体”。只有下确界和1000之间的间隙。如果您想插入500，而我想插入100，我们都在争夺当前相同的间隙，因此其中一个人需要等待另一个人。 - Michael - sqlbot

@Michael-sqlbot 对不起，我不明白你的意思。我的意思是，如果我执行 select * from testdl where c > 500 for update，那么在我完成之前，所有具有 c > 500 的并发写入都应该被禁止。但是，如果我执行 select * from testdl where c = 500 for update，如果另一个事务插入一个 c = 100 的记录，可能会造成什么样的危害呢？ - Dean Winchester

如果另一个事务插入 c = 100，它必须插入到 c = 500 将要去的完全相同的位置——两个潜在的新行都需要放置在完全相同的位置：紧接着 c = 1000 的间隙之前。那里只有一个间隙，你锁定不存在的 500 并不会创建两个空洞，一个小于 500，一个大于等于 500。仍然只有一个间隙。两个事务需要相同的东西，所以其中一个需要等待。 - Michael - sqlbot

Answer 1

这类似于我最近自己感到好奇，所以让我试着解释一下...

无论 Tx2 中的值是什么，它都会挂起。因此，基本上意味着当从 testdl 中删除 c = 1000 的匹配项失败时，Tx1 就持有了整个范围（-∞，+∞）的间隙，对吗？

那么我的问题是：这是按设计实现的吗？如果是的话，这有什么意义？

这是按设计实现的，间隙锁的主要目的是防止在这些间隙中插入任何记录，以避免 虚幻行。

因此，想象一下您的空表格，并在事务内执行 delete from testdl where c = 1000;。现在，无论之前有多少这样的行存在，您都期望在此查询之后没有这样的行存在于您的表格中，对吗？因此，如果在此之后在同一事务中执行 select * from testdl where c = 1000 for update;，则期望结果为空。

为了确保表中没有插入c = 1000的新行，我们需要锁定可以插入这些记录的间隙。在一个空表中，只有一个间隙：infimum和supremum伪记录之间的间隙（正如Michael指出的那样）。

在这种情况下，任何值>= 1000都可以插入，因此Tx1锁定间隙（-∞，1000）

同样，锁定（-∞，1000）是必要的吗？背后的原因是什么？

我相信上面的解释也应该解答您关于表中已经有一条记录的第二种情况的问题。但是我还是会尝试解释一下。

在您的第一次交易中，您执行了select * from testdl where c = 500 for update;现在我们需要确保如果我们决定在此事务内再次进行这样的查询，则不会出现新的c = 500记录。因此，我们需要锁定所有必要的间隙。我们有哪些间隙？(-∞, 1000)和(1000, +∞)，显然，新记录不会插入到第二个间隙中，但会插入到第一个间隙中，因此我们必须锁定它。

希望这回答了您的问题。

- GProst

---

已经两年了 :) 我知道幻读的问题。让我这样说，理论上来说，我们只需要禁止插入 c=1000 就可以了，对吧（在第一个例子中）？如果我们忘记了“你不能锁定不存在的东西，锁定只是对某个内存地址进行串行访问”的事情，那么这更像是一种实现方式（或选择，或者这是我们现在能做到的最好的）对吧？ - Dean Winchester

只有在你的 where 子句中的列有索引时，才是正确的。否则，该子句将被忽略 - InnoDB 锁定的是索引而不是记录。如果你对 c 进行索引，它将进行记录锁定并允许在间隙中插入/删除/更新，否则它将进行 next-key 锁定，这是一个记录锁和间隙锁的组合。 - Nikola Jankovic

@DeanWinchester 你找到这个问题的实际原因了吗？ - vinieth

不是真的。但我倾向于认为这是一种实现方式。比如当表为空时，只有-∞和+∞。这就是MySQL需要锁定的全部内容。但这只是我告诉自己为所见之事辩解的方式，并不一定是真相。 - Dean Winchester

Answer 2

执行后：

create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));

-- transaction 1
begin;
delete from testdl where c = 1000;

-- transaction 2
begin;
insert into testdl (c) values (?); -- ? can be any int

select * from performance_schema.data_locks 的输出结果如下：

+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+
| INNODB | 140043377180024:1073:140043381454544   |                  2322 |        48 |      218 | test          | testdl      | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 140043377180024:12:5:1:140043381451552 |                  2322 |        48 |      218 | test          | testdl      | NULL           | NULL              | idx_c      |       140043381451552 | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
| INNODB | 140043377180872:1073:140043381460688   |                  2321 |        49 |      154 | test          | testdl      | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 140043377180872:12:5:1:140043381457776 |                  2321 |        49 |      154 | test          | testdl      | NULL           | NULL              | idx_c      |       140043381457776 | RECORD    | X                  | GRANTED     | supremum pseudo-record |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+

事务2正在等待获取插入意图锁（负无穷大，正无穷大），因为事务1持有下一个键锁（负无穷大，正无穷大），所以它无法继续。

但在执行后

create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));
insert into testdl values(1, 1000);

-- transaction 1
begin;
select * from testdl where c = 500 for update;

select * from performance_schema.data_locks 的输出结果是:

+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 140043377180872:1074:140043381460688   |                  2341 |        49 |      167 | test          | testdl      | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 140043377180872:13:5:2:140043381457776 |                  2341 |        49 |      167 | test          | testdl      | NULL           | NULL              | idx_c      |       140043381457776 | RECORD    | X,GAP     | GRANTED     | 1000, 1   |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

事务1持有间隙锁 (负无穷，(1000, 1))，因此其他事务无法向该间隙插入数据。

- Jason Law