时间戳

首页 > 解决方案 > MySQL 间隙锁行为不符合预期

mysql - MySQL 间隙锁行为不符合预期

问题描述

问题描述：

在mysql 8.0中，我启动了一个事务并执行了SELECT * FROM child WHERE id > 1000 FOR UPDATE;（事务一），然后我启动了另一个事务并执行了update child set id = id+10 where id = 101;（事务二），在表中有一行id = 101child，但是这个事务二被阻塞了。

如果我执行update child set id = id+10 where id = 102;（事务三）并且表中不存在任何行id = 102，事务三不会被阻塞并且可以成功执行。

据我所知，mysql 8.0 gap lock只会锁定id大于1000的行，但是在第二个事务中，row id是101且不大于1000，所以两个事务不会相互冲突。那么为什么事务二会被事务一阻塞呢？

附加细节如下：

  1. 表child结构：

CREATE TABLE `child` (\n `id` int NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

  1. 表中的所有数据child：
mysql root@localhost:test> select * from child;
+------+
| id   |
+------+
| 90   |
| 101  |
| 105  |
| 106  |
| 109  |
| 111  |
| 1007 |
+------+
7 rows in set
  1. 所有 mysql 8.0 配置都是默认配置。
  2. 这两个交易是并行的。

标签： mysqllockinginnodb

解决方案

稍微简化一下，锁将始终附加到现有对象，例如一行。

假设您的表中有 ID 90、101、105、106、109、110 和 1007。请注意，我将您的示例行 111 更改为 110，否则，从 101 到 111 的更新将由于主键冲突而失败，然后才会遇到锁定问题。

如果 MySQL 需要为 发出锁WHERE id > 1000，它不会（因为它不能）跟踪确切的值> 1000。相反，它将采用最接近该值的现有对象，在您的情况下是 id 为 110 的行，并添加一个间隙锁，覆盖从 110 到 1006 的空间。另一个锁 + 间隙锁在 id 的行上1007，涵盖了 1007 及以上的空间。

是的，这覆盖了比需要更多的空间。但是该锁包括所需的空间，这是重要的方面。

您update child set id = id+10 where id = 101现在会将行移动到锁定空间中，因此必须等待现有锁被释放。因此，由于 MySQL 的工作方式，这实际上是预期的行为。

如果您在 112 和 1000 之间有另一行，则间隙锁可以从那里开始，并且您的更新事务不必等待。

实际上，这是一个问题，尤其是对于小表（或特定的更新/插入模式）。如果添加越来越多的行，随机更新命中一个“过度覆盖”间隙的概率将会降低。

推荐阅读