时间戳

首页 > 解决方案 > MySQL 8.0 SKIP LOCKED 按日期排序时返回空集

mysql - MySQL 8.0 SKIP LOCKED 按日期排序时返回空集

问题描述

我正在尝试在我的应用程序中实现表的并发更新，但我发现了一个奇怪的 MySQL 行为。看起来 FOR UPDATE + LIMIT 在某些情况下会锁定整个表，而不是仅锁定那些被选中的行。假设我们有下表：

CREATE TABLE `test`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date_created` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES (1, '2019-05-22 19:34:28');
INSERT INTO `test` VALUES (2, '2019-05-22 19:34:46');
INSERT INTO `test` VALUES (3, '2019-05-22 19:34:54');
INSERT INTO `test` VALUES (4, '2019-05-22 19:35:01');

现在，让我们同时检索行：

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

mysql> SELECT id, date_created FROM test ORDER BY date_created DESC LIMIT 2 FOR UPDATE SKIP LOCKED;
+----+---------------------+
| id | date_created        |
+----+---------------------+
|  4 | 2019-05-22 19:35:01 |
|  3 | 2019-05-22 19:34:54 |
+----+---------------------+
2 rows in set (0.00 sec)

并发事务中的相同请求：

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

mysql> SELECT id, date_created FROM test ORDER BY date_created DESC LIMIT 2 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)

因此，第二个并发事务无法检索 id 为 1 和 2 的行。在另一种情况下，当我按“id”字段而不是“date_created”对行进行排序时，它的工作原理与我预期的一样：

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

mysql> SELECT id, date_created FROM test ORDER BY id DESC LIMIT 2 FOR UPDATE SKIP LOCKED;
+----+---------------------+
| id | date_created        |
+----+---------------------+
|  4 | 2019-05-22 19:35:01 |
|  3 | 2019-05-22 19:34:54 |
+----+---------------------+
2 rows in set (0.00 sec)

和一个并发事务：

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

mysql> SELECT id, date_created FROM test ORDER BY id DESC LIMIT 2 FOR UPDATE SKIP LOCKED;
+----+---------------------+
| id | date_created        |
+----+---------------------+
|  2 | 2019-05-22 19:34:46 |
|  1 | 2019-05-22 19:34:28 |
+----+---------------------+
2 rows in set (0.00 sec)

这是一个错误吗？有什么解决方法吗？

MySQL 版本：8.0.16 MySQL 社区服务器 - GPL

标签： mysqlmysql-8.0

解决方案

这是预期的行为。问题是 date_created 列上没有索引，因此按 date_created 排序以获得两行最终会锁定所有记录。如果您从执行 SELECT ... FOR UPDATE SKIP LOCKED 的同一连接查询 performance_schema.data_locks 表，您可以看到这一点：

mysql> SELECT OBJECT_SCHEMA AS 'Schema',
              OBJECT_NAME AS 'Table', INDEX_NAME AS 'Index',
              LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
         FROM performance_schema.data_locks
         WHERE THREAD_ID = PS_CURRENT_THREAD_ID();
+--------+-------+---------+-----------+-----------+-------------+------------------------+
| Schema | Table | Index   | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+-------+---------+-----------+-----------+-------------+------------------------+
| db1    | test  | NULL    | TABLE     | IX        | GRANTED     | NULL                   |
| db1    | test  | PRIMARY | RECORD    | X         | GRANTED     | supremum pseudo-record |
| db1    | test  | PRIMARY | RECORD    | X         | GRANTED     | 1                      |
| db1    | test  | PRIMARY | RECORD    | X         | GRANTED     | 2                      |
| db1    | test  | PRIMARY | RECORD    | X         | GRANTED     | 3                      |
| db1    | test  | PRIMARY | RECORD    | X         | GRANTED     | 4                      |
+--------+-------+---------+-----------+-----------+-------------+------------------------+
6 rows in set (0.00 sec)

这也是它起作用的原因，当您按主键排序时：在这种情况下，只有所需的行将被访问并因此被锁定。

使它也适用于 date_created 的解决方案是在该列上添加一个索引：

ALTER TABLE test ADD INDEX (date_created);

推荐阅读