SQL如何通过锁机制保证业务幂等性_唯一防重表与排他锁插入-SQL-PHP中文网

不能替代，因ON DUPLICATE KEY UPDATE无法区分插入成功与已存在，会掩盖冲突导致业务误判，且高并发下易死锁、不适用无唯一键场景；防重表通过唯一索引+INSERT报错1062实现可靠幂等。

sql如何通过锁机制保证业务幂等性_唯一防重表与排他锁插入

INSERT ... ON DUPLICATE KEY UPDATE 能否替代唯一防重表？

不能直接替代，它只在有 UNIQUE 或 PRIMARY KEY 冲突时触发更新逻辑，但不提供业务层可感知的“插入成功/已存在”区分能力——尤其当你要严格拒绝重复、而非静默覆盖时，ON DUPLICATE KEY UPDATE 会掩盖冲突，导致上游误判为“新记录写入成功”。

常见错误现象：INSERT INTO order_log (order_id, status) VALUES ('ORD123', 'paid') ON DUPLICATE KEY UPDATE status = VALUES(status)，若 order_id 已存在，SQL 执行成功且返回影响行数为 2（实际是 update），但业务代码若只检查 affected_rows == 1 就认为是新订单，就会出错。

真正需要幂等写入的场景（如支付回调、消息重投），应让失败可被捕获，而不是用 update 消解冲突
ON DUPLICATE KEY UPDATE 在高并发下仍可能因间隙锁行为引发死锁，尤其涉及二级索引时
它不适用于无主键/唯一键的表结构，而防重表本质就是靠唯一约束兜底

为什么必须用单独的防重表，而不是在业务表加唯一索引？

因为业务表的唯一约束通常绑定的是业务字段（如 order_no），但幂等判断依据往往是外部输入（如第三方回调中的 out_trade_no + channel 组合），二者语义不同、生命周期不同、查询频率也不同。混在一起会导致索引膨胀、维护困难、甚至阻塞核心业务写入。

使用场景举例：支付系统要防止同一笔微信回调被多次处理，需以 channel='wechat' + out_trade_no='123456' 为幂等键；如果把这个组合加到 payment_order 表上作唯一索引，那每次查订单就得走这个索引，而它对「查单」几乎无用，纯属冗余。

防重表应极简：至少含 key（VARCHAR 主键）、created_at（便于清理过期记录）
key 字段长度别盲目设 255，按实际最长输入定，避免索引过大影响性能
不要给防重表加外键或触发器，它只做原子性校验，其余逻辑全在应用层

SELECT ... FOR UPDATE 在防重流程中为何容易失效？

很多人想绕过建表，直接用 SELECT key FROM idempotent WHERE key = ? FOR UPDATE 判断是否存在，再决定 INSERT。这在绝大多数情况下会失败，因为 SELECT ... FOR UPDATE 只锁定已有行，对不存在的 key 不加任何锁（除非命中间隙锁，但不可控且依赖隔离级别）。

错误现象：两个事务同时查 key='abc'，都返回空，接着都执行 INSERT，违反幂等——这就是典型的“幻读未防护”问题。

只有在可重复读（RR）隔离级别下，且 key 字段上有唯一索引时，INSERT 才会自动尝试获取插入意向锁并阻塞另一个并发插入
SELECT ... FOR UPDATE 无法替代唯一约束，它只是辅助手段，不能作为幂等主逻辑
如果硬要用，必须配合 INSERT ... SELECT 或存储过程封装成原子块，但复杂度陡增，得不偿失

INSERT IGNORE 和 REPLACE INTO 各自踩坑点

INSERT IGNORE 看似简单，但它会静默忽略所有错误（不只是唯一冲突），比如字段超长、NULL 插非空列，都会变成 warning 被吞掉，导致数据丢失而不报警；REPLACE INTO 更危险，它本质是 DELETE + INSERT，在有外键或触发器的库中可能意外级联删数据，且自增 ID 会跳变。

性能影响：两者在冲突时都需要先定位冲突行，再决定忽略或替换，比纯粹的 INSERT 多一次索引查找；高并发下还可能因锁等待拉长事务时间。

永远不要用 INSERT IGNORE 做幂等主逻辑，除非你明确知道它只会遇到唯一键冲突
REPLACE INTO 应视为“强制覆盖”，不是“幂等插入”，和业务要求天然相悖
真正安全的做法只有一个：用带唯一索引的防重表 + INSERT，靠数据库报错 1062 Duplicate entry 来识别重复

最易被忽略的一点：防重表的 key 必须是完整业务上下文拼接值，不能只取部分字段。比如微信回调里 out_trade_no 单独不唯一，必须带上 channel 或 app_id 组合，否则不同渠道的同名号会互相干扰。这个拼接规则一旦定下，就不能在应用层随意变更，否则等于废掉整个幂等体系。