我发现 MySQL 的“select for update”有一个奇怪的问题。我使用的是 5.1.45 版。我有两个表:

    mysql> show create table tag;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tag   | CREATE TABLE `tag` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `message` varchar(255) NOT NULL,
  `created_at` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table live_tag;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| live_tag | CREATE TABLE `live_tag` (
  `tag_id` int(10) unsigned NOT NULL,
  KEY `live_tag_tag_fk` (`tag_id`),
  CONSTRAINT `live_tag_tag_fk` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

第一个存储用户保存的版本(“标签”)以及提交消息。第二个表包含当前“实时”版本的 ID。 live_tag.tag_id 上有一个外键引用 tag(id)。 live_tag 只包含一行。提交新版本时会更新此行。在更新 live_tag 行之前，我执行此语句:

mysql> select tag_id from live_tag for update;

但是，当我在两个终端中运行此语句并在其中一个中更新 tag_id 时，有时 MySQL 在第二个终端中返回“空集”而不是新值:

-- TERMINAL ONE
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL TWO
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL ONE
mysql> select tag_id from live_tag for update;
+--------+
| tag_id |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

-- TERMINAL TWO
mysql> select tag_id from live_tag for update;
-- hangs (waiting for lock)

-- TERMINAL ONE
mysql> update live_tag set tag_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- TERMINAL TWO returns the following for previous "select tag_id from live_tag for update"
Empty set (8.54 sec) -- Why empty set?

我没有删除任何行，我只是更新了 live_tag 中的一行，为什么 MySQL 没有看到更新？

更奇怪的是，我注意到如果我将 live_tag 设置为比以前更高的值，第二个终端会正确返回新值:

-- TERMINAL ONE
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL TWO
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL ONE
mysql> select tag_id from live_tag for update;
+--------+
| tag_id |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

-- TERMINAL TWO
mysql> select tag_id from live_tag for update;
-- hangs (waiting for lock)

-- TERMINAL ONE
mysql> update live_tag set tag_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- TERMINAL TWO returns the following for previous "select tag_id from live_tag for update"
+--------+
| tag_id |
+--------+
|      2 |
+--------+
-- this is correct

仅当我将 tag_id 设置为比以前更低的值时才会出现此问题。

这是由于 tag_id 的外键约束造成的吗？或者因为我选择了表中的所有行(没有“where”子句)？

我已经尝试过的:

• 将 key 放到 live_tag.tag_id 上后，它可以正常工作。

• 我向 live_tag 添加了一个 id 列，并将我的“select for update”限制为“where id = 1”。这也能正常工作。

• 我用三个终端试过了。提交 1 后，2 立即返回空集。几秒钟后，3 也返回空集(即使我还没有提交 2)。

我可以将 id 列添加到表中，但仍然对这种奇怪的行为感到好奇？我尝试在此处进行谷歌搜索和搜索，但没有找到答案。

更新

Barmar 的理论似乎是正确的，因为我尝试了他建议的测试，并且在响应中只有 1 行:

-- TERMINAL ONE
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL TWO
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL ONE
mysql> select tag_id from live_tag for update;
+--------+
| tag_id |
+--------+
|      2 |
|      3 |
+--------+
2 rows in set (0.00 sec)

-- TERMINAL TWO
mysql> select tag_id from live_tag for update;
-- hangs

-- TERMINAL ONE
mysql> update live_tag set tag_id=1 where tag_id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update live_tag set tag_id=4 where tag_id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from live_tag;
+--------+
| tag_id |
+--------+
|      1 |
|      4 |
+--------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- TERMINAL TWO returns
+--------+
| tag_id |
+--------+
|      4 |
+--------+
1 row in set (34.02 sec)

谁有更新版本的 MySQL 想试试这个？

最佳答案

从将索引列的值设置得更高或更低的依赖性来看，看起来锁实际上是放在索引条目上的。数据库引擎扫描索引，并在第一个锁定的条目处停止，等待它被释放。

当第一个事务提交时，索引解锁，等待的事务继续扫描索引。由于该值已降低，因此它现在在指数中处于较早位置。所以恢复扫描看不到它，因为它已经过了那个点。

要确认这一点，请尝试以下测试:

1. 创建两行，值为 2 和 3。
2. 在两个交易中，执行SELECT ... FOR UPDATE
3. 在事务 1 中，将 2 更改为 1，将 3 更改为 4。
4. 提交事务 1。

如果我的猜测是正确的，事务 2 应该只返回带有 4 的行。

这对我来说似乎是一个错误，因为我认为您不应该得到这样的部分结果。不幸的是，很难在 bugs.mysql.com 上搜索这个，因为搜索时忽略了单词“for”，因为它太短或太常见。即使引用“for update”似乎也找不到仅包含此短语的错误。

关于MySQL select for update 返回空集，即使存在一行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/26723981/