Understanding MySQL Deadlock Caused by Index Merge on Update Statements

Background

In a production environment a deadlock was observed where two identical UPDATE statements (differing only in the trans_id value) blocked each other. The article dissects the root cause and shares the findings to help others facing the same issue.

Deadlock Log

*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
...
*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
*** (2) HOLDS THE LOCK (S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

The log shows transaction (1) waiting for an X‑lock on idx_status while holding a lock on the row, and transaction (2) holding the idx_status lock but waiting for an X‑lock on the PRIMARY (clustered) index, leading to a classic deadlock that MySQL resolves by rolling back the cheaper transaction.

Table Structure

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trans_id` varchar(21) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,
  KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

The table has a primary clustered index on id , a unique secondary index on trans_id , and a non‑unique secondary index on status . InnoDB stores row data in the clustered index, while secondary indexes contain the primary key value.

Execution Plan

The UPDATE statements use the index_merge optimization, i.e., Using intersect(uniq_trans_id,idx_status) , meaning MySQL fetches matching id values from both secondary indexes, intersects them, and then performs a primary‑key lookup (the “back‑table” step).

Why Index Merge Is Used

Since MySQL 5.1 the optimizer can combine multiple indexes for a single query. When the optimizer estimates that intersecting the result sets of uniq_trans_id and idx_status is cheaper than scanning one index and filtering the other condition, it chooses the index‑merge path. This often happens when the status = 0 condition matches very few rows.

How Index Merge Leads to Deadlock

Both transactions acquire an X‑lock on idx_status (or on the intersected set) and then need an X‑lock on the clustered PRIMARY index to update the row. Because the lock acquisition order differs between the two transactions, they end up waiting on each other, forming a circular wait.

Solutions

From the Application Code

Query only by trans_id , fetch the row, and then check status in application logic before updating.

Force the optimizer to use the unique index: FORCE INDEX (uniq_trans_id) .

Update by primary key id after the row is retrieved.

From the MySQL Side

Drop the idx_status index or replace it with a composite index covering both trans_id and status .

Disable the index‑merge optimizer (e.g., SET optimizer_switch='index_merge=off' ).

By aligning lock acquisition order or removing the conflicting secondary index, the deadlock can be avoided.