使用INSERT ... ON DUPLICATE KEY为何仍触发SQLIntegrityConstraintViolationException？

问题描述 #

使用MariaDB 10.11，表主键为整数，执行以下SQL语句：

INSERT INTO mytable (id, myfield) VALUES (?,?) ON DUPLICATE KEY UPDATE myfield=?;

触发主键重复错误：

(conn=248) Duplicate entry '18163' for key 'PRIMARY'; nested exception is java.sql.SQLIntegrityConstraintViolationException: (conn=248) Duplicate entry '18163' for key 'PRIMARY'

可能的原因及解决办法 #

• 高并发竞态冲突
  当多个请求同时写入同一id时，会出现竞态：第一个请求未完成提交时，第二个请求执行INSERT前的唯一性检查，无法检测到未提交的记录；等第一个请求提交后，第二个请求执行写入就会触发主键冲突。
  解决：给语句添加FOR UPDATE锁，或在应用层通过分布式锁控制同一ID的写入；若业务允许，可改用REPLACE INTO（注意该语句会删除旧记录再插入，存在数据丢失风险）。

• 触发器/存储过程干扰
  若表关联了BEFORE INSERT或AFTER INSERT触发器，触发器内部可能存在插入/修改同主键记录的逻辑，导致主键冲突。
  解决：检查表的所有触发器和关联存储过程，排查是否有额外的主键写入操作。

• 事务内重复操作
  若在同一事务中，先执行了该主键的插入操作但未提交，后续再次执行这条INSERT语句，在READ COMMITTED等隔离级别下，后续语句无法看到未提交的记录，从而触发重复插入。
  解决：梳理事务逻辑，避免同一事务内对同一主键重复执行插入操作，或调整事务隔离级别。

• 参数与主键类型不匹配
  如果传入的参数类型和主键字段类型不一致（比如主键是INT UNSIGNED，但传入了带格式的字符串），隐式转换过程中可能意外匹配到现有主键记录，触发冲突。
  解决：确保传入参数的类型与主键字段类型完全一致，避免隐式转换。

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