Why MySQL Inserts Deadlock on Unique Indexes and How to Prevent Them

1. Business Background

Our data product aggregates real‑time and crawled data into a big‑data warehouse (ODPS). For fast reads the data are copied into a MySQL table with a composite unique index (cinema_id, show_id, now_date). During high‑TPS batch inserts the table suffered frequent deadlocks, which we investigated with DBA assistance.

2. Deadlock Situation

Table definition

CREATE TABLE tkn_tb_cinema_show_data (
    ...,
    cinema_id BIGINT(20) DEFAULT NULL COMMENT 'cinema ID',
    show_id BIGINT(20) DEFAULT NULL COMMENT 'movie ID',
    now_date VARCHAR(32) DEFAULT NULL COMMENT 'date',
    ...,
    PRIMARY KEY (id),
    UNIQUE KEY uid_cinema_show_date (cinema_id,show_id,now_date)
) ENGINE=InnoDB AUTO_INCREMENT=2162973490 DEFAULT CHARSET=utf8 COMMENT='Taobao movie order statistics';

Deadlock log excerpt

TRANSACTION 73278630816 ACTIVE 1 sec inserting
... 
HOLDS THE LOCK(S): index uid_cinema_show_date ... lock_mode X
WAITING FOR THIS LOCK TO BE GRANTED: index uid_cinema_show_date ... lock_mode X locks gap before rec insert intention waiting
...
TRANSACTION 73278630826 ACTIVE 1 sec inserting
... 
HOLDS THE LOCK(S): index uid_cinema_show_date ... lock_mode X
WAITING FOR THIS LOCK TO BE GRANTED: index uid_cinema_show_date ... lock_mode X locks gap before rec insert intention waiting

3. Analysis

Both transactions hold X locks on the unique index and wait for an insert‑intention lock (IK) on the same gap, creating a classic deadlock cycle. InnoDB’s lock compatibility matrix shows that an X‑IK request is blocked by a preceding S‑NK lock, and after the insert the X‑RK lock remains.

The insert statements for the two transactions illustrate that the key ranges overlap, causing each transaction’s S‑NK to be blocked by the other’s X‑RK.

4. Prevention

To avoid such deadlocks:

Keep transactions short and batch them.

Use a lower isolation level (e.g., READ‑COMMITTED) to reduce the duration of S‑NK locks.

Ensure that insert operations acquire gap locks in a consistent order or redesign the unique index usage.