When Does MySQL ON DUPLICATE KEY UPDATE Hurt Performance? Experiments & Insights

Background

During a code review a colleague warned that using MySQL's ON DUPLICATE KEY UPDATE may have serious performance and hidden issues, suggesting a split‑query approach with batch inserts/updates and a CASE WHEN construct.

Official Documentation

The MySQL 5.7 reference manual describes the syntax and notes that when multiple unique indexes exist the statement behaves like an UPDATE that may affect only one row, and that InnoDB uses row‑level locks while MyISAM uses table‑level locks.

Validation Experiments

Created table t1 with an auto‑increment primary key and performed repeated INSERT … ON DUPLICATE KEY UPDATE statements. The results showed that when the primary key already exists the statement updates the row (equivalent to UPDATE t1 SET c=c+1 WHERE a=1).

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

Added a unique index on column b and repeated the same insert. The operation performed only an update, confirming the documentation warning about multiple unique indexes.

ALTER TABLE t1 ADD UNIQUE INDEX uniq_b (b ASC);
INSERT INTO t1 (a,b,c) VALUES (3,20,30)
ON DUPLICATE KEY UPDATE c=c+1;

Observed that with InnoDB the statement acquires row‑level locks, but a MySQL bug report shows possible deadlocks when many concurrent threads execute the statement.

Summary

On MyISAM the syntax uses table‑level locks, leading to poor concurrency.

On InnoDB it uses row‑level locks, but concurrent bulk operations can still cause deadlocks.

Avoid ON DUPLICATE KEY UPDATE on tables that have more than one unique index.