MySQL中如何安全实现同键同值可重复插入且同键值必须一致？

1. 依赖重复约束的方案是否安全？ #

不安全。插入失败后再执行查询校验的窗口中，存在并发事务修改该行val的风险：

• 事务A插入(foo, bar)因唯一键冲突失败
• 事务B此时将该行val修改为bar2
• 事务A查询到val=bar2，会错误判定插入值与现有值不一致，但实际上A发起插入时原val是符合要求的

该方案无法保证校验操作的原子性，会导致数据一致性问题。

2. MySQL InnoDB插入因重复键失败后是否持有行锁？ #

会持有冲突行的排他锁（X锁）。当插入操作检测到唯一键冲突时，InnoDB会自动对冲突行加排他锁，直到当前事务提交或回滚。这意味着事务结束前，其他事务无法修改或删除该行，避免了插入失败后行被立即删除的情况；若事务回滚，锁会被释放，其他事务即可操作该行。

3. 更优的实现方案 #

推荐使用INSERT ... ON DUPLICATE KEY UPDATE语法，实现原子性的插入/校验逻辑，既保证唯一键约束，又能在重复插入相同key-val对时成功，插入不同val时报错。

前置准备 #

已给key字段添加唯一约束：

ALTER TABLE T ADD CONSTRAINT uk_key UNIQUE (`key`);

核心SQL #

INSERT INTO T (`key`, val) VALUES (foo, bar)
ON DUPLICATE KEY UPDATE 
    val = IF(val = bar, val, SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'val与现有记录不一致');

逻辑说明 #

• 无对应key时：直接插入新行，操作成功
• 已有对应key时：
  • 若现有val等于插入值bar，执行val = val（无实际修改），操作成功
  • 若现有val不等于bar，抛出自定义错误，阻止操作，确保数据一致性

该方案全程为原子操作，无需额外的查询或锁操作，完全匹配你的需求。

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