基于MySQL Locker表实现多进程id互斥处理的锁机制问询

核心思路 #

利用MySQL唯一索引的原子性约束，结合INSERT ... ON DUPLICATE KEY UPDATE语句实现原子性的锁抢占，同时通过locked_until字段控制锁的有效期，避免因进程崩溃、网络异常导致锁永久占用的问题。

具体实现步骤 #

1. 尝试获取锁 #

当进程拿到待处理的Id后，执行以下SQL尝试获取锁：

INSERT INTO Locker (Id, locked_until)
VALUES (:target_id, DATE_ADD(NOW(), INTERVAL 5 MINUTE))
ON DUPLICATE KEY UPDATE
locked_until = CASE 
    WHEN locked_until < NOW() THEN DATE_ADD(NOW(), INTERVAL 5 MINUTE)
    ELSE locked_until
END;

结果判断逻辑：

• 如果执行后影响行数为1：说明要么是首次插入（锁不存在，成功获取），要么是锁已过期并被成功更新（抢占到过期锁），此时进程可以开始处理逻辑。
• 如果执行后影响行数为0：说明当前锁未过期，其他进程正在持有该锁，当前进程需放弃处理该Id（可以加入重试队列或直接跳过）。

注意：这里的5 MINUTE是锁的有效期，你可以根据实际处理耗时调整，确保正常处理能在有效期内完成。

2. 处理完成后释放锁 #

当进程完成Id的处理逻辑后，需要主动释放锁，避免占用资源。有两种可选方式：

• 方式一：直接删除锁记录（适合不需要保留锁历史的场景）

DELETE FROM Locker WHERE Id = :target_id;

• 方式二：标记锁为已过期（适合需要排查锁使用历史的场景）

UPDATE Locker SET locked_until = NOW() - INTERVAL 1 MINUTE WHERE Id = :target_id;

3. 超时锁的自动回收 #

因为我们在获取锁时设置了locked_until，即使进程崩溃或异常退出，只要锁超过有效期，后续进程就能通过INSERT ... ON DUPLICATE KEY UPDATE抢占到该锁，无需额外的定时任务清理（当然你也可以定期清理已过期的锁记录来优化表性能）。

额外优化建议 #

• 锁续约机制：如果你的处理逻辑耗时可能超过锁有效期，可以在处理过程中定期执行续约SQL，延长锁的有效期：

UPDATE Locker SET locked_until = DATE_ADD(NOW(), INTERVAL 5 MINUTE) WHERE Id = :target_id AND locked_until > NOW();

• 避免长事务：锁相关的SQL操作尽量独立执行，不要放在长事务中，防止锁被长时间占用。
• 索引优化：你的表已经给Id加了唯一索引，这已经足够保证锁操作的性能，无需额外索引。

异常场景处理 #

• 进程崩溃：依赖locked_until的超时机制，其他进程在超时后可以正常抢占锁。
• 网络抖动：如果执行获取锁的SQL后网络中断，进程无法确认是否获取到锁，此时可以先查询该Id的locked_until是否在未来时间，如果是则继续处理，否则放弃。

SELECT locked_until FROM Locker WHERE Id = :target_id;

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