Why MySQL Index Merge Triggers Deadlocks and How to Fix Them

Background

During performance testing of a newly separated inventory system, a MySQL 5.6.35 deadlock was observed on a simple UPDATE statement. The article records the investigation process.

MySQL Locking Mechanism

MySQL locks are applied to index records, not directly to data rows. In InnoDB, the primary key index and data are stored together in a B+‑tree, while MyISAM stores them separately. Queries first locate the primary key via a secondary index, then fetch the row.

Lock types depend on the index type (primary, unique, non‑unique) and the transaction isolation level (RC, RR). In RC, gap locks are added to prevent phantom reads.

Locking Examples

Update by primary key: update t set name='xxx' where id=29; acquires an X lock on the primary‑key record.

Update by unique index: update t set name='xxx' where name='ddd'; first locks the unique secondary index entry, then the corresponding primary‑key record (two locks).

Update by non‑unique index: update t set name='xxx' where name='ddd'; (name not unique) locks every matching secondary index entry and each associated primary‑key record, resulting in four X locks.

Locks are released when the transaction ends.

Deadlock Phenomenon and Investigation

The deadlock occurs on the inventory deduction interface, which updates the store table:

CREATE TABLE `store` (
  `id` int(10) AUTO_INCREMENT COMMENT 'primary key',
  `sku_code` varchar(45) COMMENT 'product code',
  `ws_code` varchar(32) COMMENT 'warehouse code',
  `store` int(10) COMMENT 'stock quantity',
  PRIMARY KEY (`id`),
  KEY `idx_skucode` (`sku_code`),
  KEY `idx_wscode` (`ws_code`)
) ENGINE=InnoDB COMMENT='product inventory';

The update statement is:

update store
set store = store-#{store}
where sku_code=#{skuCode} and ws_code = #{wsCode} and (store-#{store}) >= 0;

Under load (50 concurrent threads, each order with 5 items) the deadlock appears immediately. The InnoDB status shows two transactions each holding a lock on idx_wscode while waiting for the other's primary‑key lock, forming a classic circular wait.

Execution Plan

The optimizer chooses index_merge, combining idx_skucode and idx_wscode instead of using a single index. This causes both indexes to be locked in different orders by the two transactions, leading to deadlock.

Root Cause Analysis

Two transactions acquire locks in opposite order:

Transaction 1 locks the secondary index idx_skucode then the primary key.

Transaction 2 locks idx_wscode then attempts the primary key, which is already held by Transaction 1.

Because InnoDB locks each matching row step‑by‑step, the interleaving of index‑merge locks creates the deadlock.

How to Resolve

Force the optimizer to use a single index: SET optimizer_switch='index_merge=off'; or use FORCE INDEX(idx_skucode) in the query.

Disable Index Merge globally:

SET GLOBAL optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off';

Create a composite index covering both columns:

ALTER TABLE store ADD INDEX idx_skucode_wscode (sku_code, ws_code);

This makes the optimizer use a single index.

Rewrite the update to a two‑step process: first SELECT the primary‑key id using the two separate indexes, then UPDATE by primary key, eliminating X‑locks on secondary indexes.

Each method ensures a consistent lock acquisition order, preventing the deadlock.

Conclusion

The article demonstrates how Index Merge can unintentionally cause deadlocks by locking multiple indexes in different sequences, and provides concrete mitigation techniques that can be applied in production MySQL environments.