解决MySQL中INSERT INTO SELECT NOT EXISTS语句的死锁问题

问题核心约束 #

• 同一clientID下的非空customerReference必须唯一，空值无唯一限制
• 并发批量插入时需避免重复数据，同时解决原有INSERT...SELECT NOT EXISTS语句导致的死锁问题

对现有方案的分析 #

1. 接受死锁并判断受影响行数：不可靠。死锁发生时事务会被回滚，受影响行数为0，但这无法区分是重复数据导致的冲突还是真的死锁，且死锁会降低系统可用性，不建议采用。
2. 哈希列+INSERT IGNORE：存在哈希碰撞风险，且需要额外维护哈希字段，增加了系统复杂度，并非最优解。

最优解决方案：部分唯一索引+INSERT ON DUPLICATE KEY UPDATE #

1. 创建部分唯一索引 #

利用MySQL 5.7+支持的部分唯一索引，仅对customerReference非空的行，强制clientID + customerReference的唯一性：

CREATE UNIQUE INDEX idx_client_ref ON ordersTable (clientID, customerReference) 
WHERE customerReference IS NOT NULL;

这个索引既满足空值不做唯一限制的需求，又能在数据库层面保证非空值的唯一性，从根源上避免重复数据。

2. 原子性插入语句 #

使用INSERT ... ON DUPLICATE KEY UPDATE替代原有INSERT...SELECT NOT EXISTS，并发时数据库会自动处理唯一冲突，且锁粒度更小，大幅降低死锁概率：

INSERT INTO ordersTable (clientID, customerReference, deliveryName)
VALUES ('clientID', 'customerReference', 'deliveryName')
ON DUPLICATE KEY UPDATE deliveryName = deliveryName; -- 无意义更新，仅让语句成功执行，不修改数据

如果希望直接忽略重复插入，也可以用INSERT IGNORE，但INSERT IGNORE会忽略所有错误（如字段类型不匹配），因此更推荐ON DUPLICATE KEY UPDATE，精准处理唯一冲突场景。

为什么这个方案能解决死锁？ #

原有INSERT...SELECT NOT EXISTS语句会扫描全表或大范围数据，产生间隙锁；而部分唯一索引仅对目标唯一键对应的行加锁，锁范围极小，并发时几乎不会出现锁冲突，从根本上避免死锁。

批量插入场景适配 #

如果是批量提交订单，同样可以用该语句批量处理，保证原子性：

INSERT INTO ordersTable (clientID, customerReference, deliveryName)
VALUES 
('client1', 'ref1', 'name1'),
('client1', 'ref2', 'name2'),
('client2', 'ref1', 'name3')
ON DUPLICATE KEY UPDATE deliveryName = deliveryName;

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