使用SELECT FOR UPDATE SKIP LOCKED时MySQL死锁问题求助

核心死锁原因 #

你遇到的SQLSTATE[40001]死锁，核心原因大概率是查询与更新操作使用了不同索引。当SELECT FOR UPDATE SKIP LOCKED通过索引A锁定目标行，但后续UPDATE语句用索引B定位同一行时，两个事务会在不同索引上形成锁等待循环，最终触发死锁。此外，如果查询条件不够精确（比如仅按status过滤、无唯一排序规则），极端情况下也可能出现两个事务同时"争抢"同一任务的情况。

分步修复方案 #

1. 强制查询与更新使用同一索引 #

确保SELECT和UPDATE语句绑定到同一个索引，避免跨索引锁冲突：

• 给QueueJobsPending创建复合索引：

CREATE INDEX idx_queue_status_id ON QueueJobsPending(status, id);

• 查询语句强制使用该索引：

SELECT id, payload 
FROM QueueJobsPending 
FORCE INDEX (idx_queue_status_id)
WHERE status = 'pending' 
ORDER BY id ASC 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

• 更新语句直接用查询到的主键id定位（主键是聚簇索引，会自动关联复合索引锁）：

UPDATE QueueJobsPending 
SET status = 'processing', updated_at = NOW() 
WHERE id = ?;

2. 优化事务逻辑，缩小锁范围 #

• 事务内仅保留"获取任务→更新状态"的核心操作，移除无关DB交互，减少锁持有时间；
• 确保SELECT语句的WHERE+ORDER BY+LIMIT组合能精确定位唯一行，避免扫描过多行导致锁范围扩大。

3. 分析死锁日志定位细节 #

从死锁日志中重点观察锁等待链：

• 如果日志显示两个事务都在更新同一任务ID，说明其中一个事务的SELECT未正确锁定该行（索引匹配问题），或UPDATE未用主键精准命中，导致扫描额外行加锁。

4. PHP代码添加死锁重试机制 #

死锁属于偶发异常，捕获后重试1-3次即可解决：

$retryCount = 3;
do {
    try {
        $pdo->beginTransaction();
        
        // 获取待处理任务
        $stmt = $pdo->prepare("SELECT id, payload FROM QueueJobsPending FORCE INDEX(idx_queue_status_id) WHERE status = 'pending' ORDER BY id ASC LIMIT 1 FOR UPDATE SKIP LOCKED");
        $stmt->execute();
        $job = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$job) {
            $pdo->commit();
            break;
        }
        
        // 更新任务状态为处理中
        $stmt = $pdo->prepare("UPDATE QueueJobsPending SET status = 'processing', updated_at = NOW() WHERE id = ?");
        $stmt->execute([$job['id']]);
        
        $pdo->commit();
        // 执行任务逻辑...
        break;
    } catch (PDOException $e) {
        $pdo->rollBack();
        // 仅针对死锁异常重试
        if ($e->getCode() === '40001' && --$retryCount > 0) {
            usleep(100000); // 等待100ms后重试
            continue;
        }
        throw $e;
    }
} while ($retryCount > 0);

验证方法 #

修改后通过压测验证，同时用EXPLAIN检查执行计划：

• EXPLAIN SELECT ... FOR UPDATE SKIP LOCKED：type应为range/ref，key显示idx_queue_status_id；
• EXPLAIN UPDATE ... WHERE id = ?：type应为const，key显示PRIMARY。

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