MySQL中SELECT FOR UPDATE因ORDER BY锁超预期行数问题求助

问题场景 #

两个事务针对maas.model_requests表的不同行执行SELECT ... FOR UPDATE，但事务1未提交时，事务2始终无法获取锁；移除ORDER BY子句后，事务2可正常执行。

事务1（未提交） #

START TRANSACTION;
SELECT * FROM maas.model_requests where id='test1' and model_request_status='QUEUED' order by priority asc limit 1 for update;

事务2（阻塞） #

START TRANSACTION;
SELECT * FROM maas.model_requests where id='test2' and model_request_status='QUEUED' order by priority asc limit 1  for update;
COMMIT;

已尝试的无效操作 #

• 设置binlog_format = row
• 设置tx_isolation = read-committed
• 确认WHERE子句中的id和model_request_status均有索引，且EXPLAIN显示查询已使用对应索引

环境信息 #

• MySQL版本：5.7.12
• 表结构：

CREATE TABLE `model_requests` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `client_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_request_status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_request_execution_status` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_function_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_function_sub_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_function_version` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `archetype_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `request_user` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `mpu_retry_total` int(11) DEFAULT '0',
  `client_retry_total` int(11) DEFAULT '0',
  `max_retry_client_count` int(11) DEFAULT '1',
  `max_retry_mpu_count` int(11) DEFAULT '1',
  `mpu_ack_timeout` bigint(20) DEFAULT '10',
  `client_ack_timeout` bigint(20) DEFAULT '10',
  `client_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sub_type_version` varchar(45) COLLATE utf8_unicode_ci DEFAULT '1.0',
  `is_synchronous` tinyint(4) DEFAULT '0',
  `mpu_expiry_time` datetime DEFAULT NULL,
  `client_expiry_time` datetime DEFAULT NULL,
  `routing_key` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  KEY `index_maas_request_id` (`id`),
  KEY `index_model_requests_status_arch_type_priority_created_at` (`model_request_status`,`archetype_name`,`priority`,`created_at`),
  KEY `index_model_requests_status_arch_type_rkey_priority_created_at` (`model_request_status`,`archetype_name`,`routing_key`,`priority`,`created_at`),
  KEY `index_model_requests_status_client_priority_created_at` (`priority`,`created_at`,`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY LIST  COLUMNS(model_request_status)
SUBPARTITION BY KEY (routing_key)
SUBPARTITIONS 5
(PARTITION cancelled VALUES IN ('CANCELLED') ENGINE = InnoDB,
 PARTITION completed VALUES IN ('COMPLETED') ENGINE = InnoDB,
 PARTITION in_progress VALUES IN ('IN_PROGRESS') ENGINE = InnoDB,
 PARTITION notified_cancelled VALUES IN ('NOTIFIED_CANCELLED') ENGINE = InnoDB,
 PARTITION queued VALUES IN ('QUEUED') ENGINE = InnoDB,
 PARTITION notified VALUES IN ('NOTIFIED') ENGINE = InnoDB,
 PARTITION acknoweleded VALUES IN ('ACKNOWLEDGED') ENGINE = InnoDB) */

解决方案 #

1. 创建匹配查询逻辑的组合索引 #

当前查询的过滤条件是id=? AND model_request_status=?，排序字段是priority，现有索引未同时覆盖这三个字段，导致MySQL需要扫描额外数据行并在排序阶段锁定无关行。创建针对性组合索引：

CREATE INDEX idx_id_status_priority ON maas.model_requests (id, model_request_status, priority);

或根据字段过滤强度调整顺序：

CREATE INDEX idx_status_id_priority ON maas.model_requests (model_request_status, id, priority);

2. 强制使用id索引 #

若不想新增索引，可在查询中指定强制使用index_maas_request_id，让MySQL优先通过id定位目标行，再验证model_request_status并排序：

SELECT * FROM maas.model_requests FORCE INDEX (index_maas_request_id)
WHERE id='test2' AND model_request_status='QUEUED' 
ORDER BY priority ASC LIMIT 1 FOR UPDATE;

3. 拆分查询逻辑（针对批量场景） #

如果是批量处理场景，可先通过id锁定目标行，再执行排序查询（单一行场景下排序无实际意义，此方案适用于多数据行筛选）：

START TRANSACTION;
-- 先锁定目标行
SELECT * FROM maas.model_requests WHERE id='test2' AND model_request_status='QUEUED' FOR UPDATE;
-- 按需执行排序查询
SELECT * FROM maas.model_requests WHERE id='test2' AND model_request_status='QUEUED' ORDER BY priority ASC LIMIT 1;
COMMIT;

4. 升级MySQL版本（可选） #

MySQL 5.7早期版本在InnoDB锁机制、索引排序处理上存在已知bug，升级到5.7最新小版本（如5.7.44）或更高版本（如8.0）可修复此类锁范围过大的问题。

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