MySQL中DELETE语句的记录锁获取顺序及影响因素咨询

测试场景 #

创建的TestA表结构如下：

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

在事务中执行语句：

delete from TestA where id in (7,1);

疑问 #

• 获取记录锁时，是按照IN列表中的顺序(7,1)，还是表的主键索引顺序(1,7)？
• 是否存在其他决定锁获取顺序的因素？
• 查询performance_schema.data_locks得到如下结果，能否确定锁获取顺序是1、7？该顺序是否会在某些情况下变化？

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 | 5254385560:563294:5198889928     |               9915434 |      5974 |      695 | test          | testa       | NULL           | NULL              | NULL       |            5198889928 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 5254385560:562138:4:2:5201207320 |               9915434 |      5974 |      695 | test          | testa       | NULL           | NULL              | PRIMARY    |            5201207320 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| INNODB | 5254385560:562138:4:5:5201207320 |               9915434 |      5974 |      695 | test          | testa       | NULL           | NULL              | PRIMARY    |            5201207320 | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+--------+----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

回答 #

1. 锁获取顺序遵循主键索引排序，和IN列表顺序无关
   InnoDB处理WHERE id IN (...)这类查询时，会先把IN列表里的值按照查询依赖的索引排序规则重新排序，再依次访问记录加锁。你这里用的是主键索引，所以会先获取id=1的锁，再获取id=7的锁，完全不看IN列表里7在前、1在后的顺序。

2. 核心影响因素是查询依赖的索引排序
   如果查询使用的是二级索引而非主键索引，锁的获取顺序会遵循该二级索引的排序规则。另外，即使IN列表里有不存在的记录，在RR隔离级别下InnoDB会给对应间隙加锁，但加锁顺序依然是按索引排序后的顺序来。

3. 当前结果能确定锁顺序，但特殊场景会变化
   你给出的data_locks结果里，LOCK_DATA的顺序是1在前、7在后，确实反映了当前场景的锁获取顺序。但要注意几种特殊情况：

• 如果查询强制走其他索引，锁顺序会跟着该索引的排序规则改变；
• 若数据库执行计划变化（比如IN列表值过多导致转为全表扫描），锁顺序会变成聚簇表全表扫描的顺序，也就是主键排序后的顺序；
• 极少数老版本MySQL的特定bug可能影响顺序，但主流8.0/5.7版本中，索引排序决定锁顺序的逻辑是稳定的。

内容的提问来源于stack exchange，提问作者Vigneswari