MySQL Deadlock Analysis: GAP Locks, Phantom Reads, and How to Resolve Them

Background and Problem Description

In a project that reads tens of millions of external records daily, data is processed in parallel batches and upserted into a table; if a row exists it is updated, otherwise inserted. The primary key is user_id . Despite using a single‑threaded approach working fine, the multi‑threaded version encounters deadlocks.

Experiment

Create a test table and insert two rows.

Start Transaction 1 and update a non‑existent row (id=2).

Start Transaction 2 and try to insert a new row (id=8), which gets blocked.

Observations show that Transaction 2 is blocked by a X, GAP lock, and Transaction 1 also blocks due to a GAP lock, leading to a deadlock.

Phantom Read

A phantom read occurs when the same transaction reads the same query twice and gets different results because another transaction inserted or deleted rows in between. In InnoDB, GAP locks are used to prevent such phenomena.

Non‑Repeatable Read

Non‑repeatable read happens when a transaction reads a row, another transaction modifies it, and the first transaction reads it again, seeing different data. This differs from phantom reads, which involve inserts/deletes.

How to Avoid Phantom Reads

1) Read Committed

At the Read Committed isolation level, phantom reads are not prevented.

2) Read Repeatable (default in InnoDB)

InnoDB uses GAP locks to avoid phantom reads by locking the index range between adjacent leaf nodes, e.g., a lock on [7,10) prevents inserts in that range.

GAP Lock

In InnoDB, locks are placed on index entries, not on the data rows themselves. A GAP lock locks the range between two neighboring index leaf nodes, preventing other transactions from inserting rows in that interval and thus avoiding phantom reads.

update test set data='' where id=8;

Deadlock Analysis

Assume initial data and then process updates in two concurrent transactions:

Transaction 1 acquires a GAP lock on [10,15) while updating user_id=11 .

Transaction 2 acquires a GAP lock on [22,30) while updating user_id=25 .

Subsequent updates cause each transaction to wait for the other's GAP lock, forming a circular wait and resulting in a deadlock. How to Resolve the Deadlock Solution 1 – Adjust Isolation Level MySQL’s default isolation level is Read Repeatable , which creates GAP locks to prevent phantom reads. Switching to Read Committed removes GAP locks and can alleviate the deadlock. In Spring, the isolation level can be set via the @Transactional(isolation = Isolation.READ_COMMITTED) annotation. Solution 2 – Refactor Data Sharding Logic Ensure that each shard processes data in ascending user_id order so that GAP locks within a shard do not interfere with each other, reducing the chance of deadlocks. How to Locate the Deadlock Cause 1) Use Business Exceptions Inspect stack traces to identify the offending code and SQL statements. 2) Verify Database Isolation Level Run SELECT @@global.tx_isolation; to confirm the current isolation level (e.g., RC for Read Committed). 3) Ask DBA for InnoDB Status Execute SHOW ENGINE INNODB STATUS; to retrieve recent deadlock logs. Conclusion Deadlock analysis can be time‑consuming; often the root cause is MySQL’s default Read Repeatable isolation level, which adds GAP locks to prevent phantom reads. If the application does not require strict repeatable reads, lowering the isolation level or redesigning sharding logic can quickly mitigate deadlocks. For more details, refer to the MySQL documentation: InnoDB Deadlocks .