MySQL中用INSERT...ON DUPLICATE KEY UPDATE实现分布式锁最轻量可控,需含lock_key、client_id、expire_time三字段,加锁须显式设过期时间,锁清理必须依赖独立定时事件而非存储过程自身。

存储过程里用 INSERT ... ON DUPLICATE KEY UPDATE 实现锁获取
直接在存储过程中执行带唯一约束的插入,是 MySQL 里最轻量、最可控的分布式锁落地方式。关键不是“能不能加锁”,而是“加锁失败时是否立刻退出”以及“锁有没有过期机制”。distributed_lock 表必须包含 lock_key(主键或唯一索引)、client_id、expire_time 三个字段,否则无法安全续期或清理。
常见错误现象:只建了 PRIMARY KEY(lock_key),但没加 expire_time 字段,导致超时锁永远不释放;或者把 client_id 设为可空,让不同客户端反复覆盖同一把锁,破坏互斥性。
- 加锁语句必须带
NOW() + INTERVAL ? SECOND显式设置过期时间,不能依赖应用层写入后再更新 -
ON DUPLICATE KEY UPDATE中的expire_time = IF(expire_time 是续期核心逻辑,缺一不可 - 返回影响行数(
ROW_COUNT())为 1 表示成功获取锁,0 表示已存在有效锁,-1 表示语法或权限问题
锁持有期间必须用 SELECT ... FOR UPDATE 保护业务数据
拿到锁只是开始,真正要操作的业务表(比如 orders 或 user_balance)仍需加行级锁,否则多个节点可能同时读到旧值、各自计算后覆盖写入——锁本身不自动隔离业务数据。
使用场景:定时任务做订单状态批量修正、优惠券发放去重、库存扣减等强一致性操作。此时若只靠分布式锁表而不在业务表上显式加锁,等于没锁。
- 必须在同一个事务内完成:先
SELECT ... FOR UPDATE读取待处理记录,再更新,最后提交 - 不能把
FOR UPDATE放在锁表查询之后、业务表操作之前,中间有时间窗口会导致脏读 - 避免全表扫描:
WHERE条件必须命中索引,否则会升级为表锁,拖垮并发性能
event_scheduler 调用存储过程前必须检查锁状态
MySQL 事件调度器(event_scheduler)本身不感知锁,它只按计划调用存储过程。如果两个事件实例几乎同时触发,而存储过程内部又没做锁校验,就会出现双写。所以每次执行入口第一件事就是查锁表,确认当前 lock_key 是否有效且未过期。
容易踩的坑:有人把锁校验逻辑放在存储过程末尾,或者只在开头简单 SELECT COUNT(*) 判断是否存在,却忽略 expire_time 是否已过期——结果锁早就失效了,还误判为“已被占用”。
- 推荐写法:
SELECT client_id INTO @holder FROM distributed_lock WHERE lock_key = 'my_job' AND expire_time > NOW() - 若
@holder为空,说明锁不存在或已过期,可尝试获取;否则直接LEAVE proc_label退出 - 事件本身建议设为
ON COMPLETION PRESERVE,避免因某次失败被自动删除
锁自动清理必须靠独立事件,不能依赖存储过程自身
存储过程执行失败、崩溃、或被 kill,都可能导致锁残留。指望每个存储过程自己负责清理是不可靠的——它甚至可能没机会运行到清理代码就退出了。必须另起一个低频事件(比如每 5 分钟),专门执行 DELETE FROM distributed_lock WHERE expire_time 。
这个清理事件和业务事件权限要分离:业务事件账号只需对锁表有 INSERT/UPDATE/SELECT,清理事件账号才需要 DELETE 权限。否则任意业务过程出错都可能误删其他服务的锁。
- 清理 SQL 必须加
WHERE expire_time ,不能只用,避免因 MySQL 时区配置偏差漏删 - 清理事件的
DEFINER应指定高权限账号,避免因 definer 权限不足导致静默失败 - 线上务必监控
distributed_lock表行数趋势,突增往往意味着锁未释放或清理事件停摆
锁的可靠性不取决于“加锁动作有多酷”,而取决于“锁失效时系统是否继续跑偏”。所有环节——获取、持有、续期、清理——都得闭环,少一环,定时任务就可能在凌晨三点悄悄双跑。











