Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks

0 Environment

Initially the project used a single master database for both reads and writes. As traffic grew, occasional system alerts reported deadlock exceptions, traced to a table used for idempotency checks on orders.

1 Reproducing the Deadlock

Create an order_record table to store order information:

CREATE TABLE `order_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` int(11) DEFAULT NULL,
  `status` int(4) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_status`(`order_no`,`status`) USING BTREE
) ENGINE=InnoDB;

Disable MySQL's autocommit to simulate manual transaction control:

SHOW VARIABLES LIKE 'autocommit';
SET autocommit = 0;

Two concurrent transactions (Task A and Task B) perform the following steps:

Task A: BEGIN; → SELECT id FROM order_record WHERE order_no = 4 FOR UPDATE; →

INSERT INTO order_record(order_no,status,create_date) VALUES (4,1,'2019-07-13 10:57:03');

→ COMMIT; (blocked)

Task B: BEGIN; → SELECT id FROM order_record WHERE order_no = 5 FOR UPDATE; →

INSERT INTO order_record(order_no,status,create_date) VALUES (5,1,'2019-07-13 10:57:03');

→ COMMIT; (blocked)

Both transactions end up waiting for each other's gap locks, resulting in a deadlock that can be observed in information_schema.innodb_lock_waits. The screenshot below shows the lock wait graph.

The SELECT ... FOR UPDATE acquires an exclusive (gap) lock to prevent phantom reads. Without it, two concurrent requests could both see the order as absent and insert duplicate rows.

2 How the Deadlock Happens

In InnoDB, three lock types are relevant:

Record lock : locks the index record itself.

Gap lock : locks the interval between index records.

Next‑key lock : a combination of record and gap lock.

When the isolation level is REPEATABLE READ (the default), a non‑unique index lookup such as WHERE order_no = 4 acquires a gap lock on the range (4, +∞). Both transactions hold a gap lock on that range, then each tries to obtain an insert‑intention lock for their respective INSERT statements. Because insert‑intention locks conflict with existing gap locks, each transaction waits for the other, forming a circular wait.

SELECT id FROM demo.order_record WHERE order_no = 4 FOR UPDATE;  -- acquires GAP lock (4,+∞)
INSERT INTO demo.order_record(order_no,status,create_date) VALUES (5,1,'2019-07-13 10:57:03');  -- needs INSERT‑INTENTION lock, conflicts with GAP lock

The lock compatibility matrix (gap vs. insert‑intention, record, next‑key) shows that a GAP lock conflicts with an INSERT‑INTENTION lock, which is the root cause of the deadlock.

3 Preventing the Deadlock

Set a reasonable innodb_lock_wait_timeout so that a transaction that waits too long is rolled back, allowing the other to proceed.

Make order_no a UNIQUE index; duplicate inserts will raise an error instead of causing a deadlock.

Consider using external systems (Redis, ZooKeeper) for idempotency checks, which can be more efficient than database locks.

4 Other Common SQL Deadlock Scenarios

Deadlocks require four conditions: mutual exclusion, hold‑and‑wait, no preemption, and circular wait. A typical example involves mixing updates on a secondary index with updates on the clustered primary key.

Using the same order_record table, the following two statements can deadlock:

Transaction A: UPDATE order_record SET status = 1 WHERE order_no = 4; (uses secondary index idx_order_status)

Transaction B: UPDATE order_record SET status = 1 WHERE id = 4; (uses the primary key)

The steps are:

Transaction A acquires a GAP lock on the secondary index range.

Transaction B acquires a row lock on the primary key.

Transaction A, after locating the primary key via the secondary index, tries to lock the same row that Transaction B already holds.

Transaction B then attempts to modify the secondary index, needing the GAP lock that Transaction A holds.

The circular wait leads to a deadlock. The lesson is to prefer primary‑key updates whenever possible.

5 Summary

MySQL deadlocks are relatively rare but can be hard to diagnose. Understanding InnoDB's lock algorithms—record, gap, and next‑key locks—and how they interact with isolation levels is essential for root‑cause analysis.

Key take‑aways for preventing deadlocks:

Always access rows in a consistent order across transactions.

When possible, use the READ‑COMMITTED isolation level to avoid gap locks.

Prefer primary‑key updates over secondary‑index updates.

Keep transactions short and break long ones into smaller units.

Configure innodb_lock_wait_timeout appropriately for high‑concurrency workloads.

FAQ

Besides adjusting innodb_lock_wait_timeout, what other methods can you use to resolve long‑running deadlocked SQL statements?