Why MySQL Index Merge Triggers Deadlocks and How to Fix It

Background

During load testing of a product inventory service, a MySQL 5.6.35 deadlock was observed on a simple UPDATE statement that reduces stock.

MySQL locking mechanism

In InnoDB, locks are taken on index entries, not on raw rows. The primary key and data are stored together in a clustered B+‑tree; secondary indexes contain the primary key values. MyISAM stores data separately.

When a query uses an indexed column, MySQL first searches the secondary index to locate the primary key, then fetches the row from the primary index.

Locking by index type

Updates acquire exclusive (X) locks on the involved indexes. The lock order depends on which index is used:

Updating by primary key locks the primary index directly.

Updating by a unique secondary index locks the secondary entry first, then the primary‑key row.

Updating by a non‑unique secondary index locks each matching secondary entry and its corresponding primary row one record at a time.

Deadlock scenario

The table definition and UPDATE statement are:

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';

UPDATE store
SET store = store-#{store}
WHERE sku_code = #{skuCode}
  AND ws_code = #{wsCode}
  AND (store-#{store}) >= 0;

In a 50‑thread test two transactions repeatedly deadlocked. The InnoDB status showed that both transactions held a lock on the secondary index idx_wscode while each waited for a lock on the primary key of the other row, creating a circular wait.

Root cause analysis

The optimizer chose the index_merge access method, which combines idx_skucode and idx_wscode. Because InnoDB locks each index entry separately, one transaction acquired the secondary‑index lock first and then the primary‑key lock, while the other did the opposite. This reverse lock order caused the deadlock.

Solutions

Force the optimizer to use a single index, e.g. FORCE INDEX (idx_skucode), to bypass index_merge.

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 that covers both columns:

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

This makes the optimizer use one index without merging.

Rewrite the update to first fetch the primary key via the two single‑column indexes, then update by primary key, eliminating index_merge entirely.

Result

After applying any of the fixes, the execution plan shows type=range and key=idx_skucode_wscode, confirming that index_merge is no longer used and the deadlock disappears.

Conclusion

Index_merge can unintentionally introduce deadlocks by causing inconsistent lock ordering. Forcing a specific index, disabling the optimizer feature, or adding a composite index are effective ways to prevent the issue in MySQL environments.