多线程更新同表时死锁问题及唯一4K记录获取方案咨询

针对多连接并行执行UPDATE时出现的死锁和记录丢失问题，可通过以下几种方式确保每个线程更新唯一的4000条记录：

1. 使用行级锁+SKIP LOCKED（MySQL 8.0+推荐） #

利用SELECT ... FOR UPDATE SKIP LOCKED语法，让每个线程原子性地锁定并获取未被其他线程占用的记录，从根源避免冲突。具体步骤：

• 在事务中先锁定4000条status='new'的记录，跳过已被其他线程锁定的行
• 基于锁定的记录ID批量更新状态和哈希值

示例代码：

START TRANSACTION;

-- 锁定并获取待处理的记录ID，跳过已被其他线程锁定的行
SELECT GROUP_CONCAT(id SEPARATOR ',') INTO @processing_ids
FROM queue
WHERE status = 'new'
ORDER BY `column`
LIMIT 4000
FOR UPDATE SKIP LOCKED;

-- 仅更新已锁定的记录
UPDATE queue
SET status = 'enqueued', hash = new_hash
WHERE FIND_IN_SET(id, @processing_ids);

COMMIT;

说明：SKIP LOCKED会让当前事务直接跳过已被其他事务锁定的行，确保每个线程拿到的是唯一的一批记录，彻底避免锁竞争导致的死锁。

2. 低版本MySQL兼容方案：乐观锁+重试机制 #

如果无法使用SKIP LOCKED，可通过乐观锁配合重试逻辑减少冲突：

• 在UPDATE时保留status='new'的条件，确保只更新未被处理的记录
• 检查UPDATE的受影响行数，若不足4000则重试，直到获取足够记录或无新记录

示例代码：

DELIMITER //
CREATE PROCEDURE process_queue()
BEGIN
    DECLARE affected_rows INT;
    DECLARE retry_count INT DEFAULT 0;
    SET affected_rows = 0;

    WHILE affected_rows < 4000 AND retry_count < 3 DO
        UPDATE queue
        SET status = 'enqueued', hash = new_hash
        WHERE status = 'new'
        ORDER BY `column`
        LIMIT 4000;

        SET affected_rows = ROW_COUNT();
        SET retry_count = retry_count + 1;
        -- 可选：重试前短暂休眠，降低冲突概率
        DO SLEEP(0.1);
    END WHILE;
END //
DELIMITER ;

说明：此方案依赖MySQL的UPDATE原子性，若其他线程已更新部分记录，当前线程的UPDATE会跳过这些行，通过重试补足数量，但死锁概率仍高于行级锁方案。

3. 优化索引与事务范围 #

• 给queue表创建联合索引idx_status_column(status, column)，让UPDATE能快速定位目标行，减少锁的范围和持有时间
• 避免将游标处理逻辑放在同一个事务中：先完成记录状态的更新并提交事务，再单独用游标处理已标记为enqueued的记录，缩短锁的持有时间，降低死锁风险

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