MySQL – Deadlocks

A deadlock is two transactions each holding a lock the other one needs. Neither can proceed, and neither will ever give up on its own — so InnoDB detects the cycle and kills one of them. The survivor commits; the victim gets an error and has to start again.

This is normal under concurrency, not a sign that something is broken. An application that writes concurrently and never retries is the actual bug.

Reproducing one

Open two terminals. Run this in the first, and the second within about two seconds:

-- session 1
START TRANSACTION;
UPDATE customer_order SET phone = '111' WHERE id = 1;   -- locks row 1
SELECT SLEEP(2);
UPDATE customer_order SET phone = '111' WHERE id = 2;   -- wants row 2
COMMIT;

-- session 2
START TRANSACTION;
UPDATE customer_order SET phone = '222' WHERE id = 2;   -- locks row 2
SELECT SLEEP(2);
UPDATE customer_order SET phone = '222' WHERE id = 1;   -- wants row 1
COMMIT;

Session 1 holds row 1 and wants row 2. Session 2 holds row 2 and wants row 1. Each is waiting for the other. One of them gets:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The whole transaction is rolled back, not just the failing statement. That is the important part for application code: everything the victim did is gone, so retrying means retrying from the beginning.

Note the opposite lock order. That is the entire cause, and the entire fix.

Deadlock or lock wait?

Two different errors, often confused:

A lock wait timeout usually means a transaction stayed open far longer than it should — an HTTP call inside one, or a connection idle in a transaction because autocommit is off. See transactions.

Reading the evidence

InnoDB keeps the most recent deadlock and will describe it:

SHOW ENGINE INNODB STATUS\G

Find the LATEST DETECTED DEADLOCK section. It is long — it dumps the locked records in hex — but only a few lines matter. Abridged, from the deadlock above:

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 64394, ACTIVE 3 sec starting index read
UPDATE customer_order SET phone = '111' WHERE id = 2

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 67 page no 11 index PRIMARY of table `pizza_lab`.`customer_order`
lock_mode X locks rec but not gap

*** (2) TRANSACTION:
UPDATE customer_order SET phone = '222' WHERE id = 1

*** WE ROLL BACK TRANSACTION (2)

Read it in four parts: the two statements each transaction was blocked on, what each holds, what each wants, and which one was rolled back. The two statements are what you take back to the code — they name the two access paths that run in opposite orders.

lock_mode X is exclusive. locks rec but not gap means a single record rather than a range, which brings us to the next part.

Gap locks, and why MySQL deadlocks more

Under REPEATABLE READ — MySQL's default — InnoDB does not only lock the rows that exist. It also locks the gaps between them, so that a repeated range query cannot see newly inserted rows. That is what makes the isolation level work, and it means two transactions can conflict over rows that do not exist yet.

-- session 1
START TRANSACTION;
SELECT * FROM customer_order WHERE id BETWEEN 100 AND 200 FOR UPDATE;
-- now holds a gap lock: nobody else may INSERT an id in that range

This is why the same application can deadlock on MySQL and not on Postgres or SQL Server, which default to READ COMMITTED. Switching to READ COMMITTED removes most gap locking and genuinely reduces deadlocks — at the cost of non-repeatable reads within a transaction. It is a real option, and a decision to make deliberately rather than by copying a config file.

The two fixes that work

1. Consistent lock order

If every transaction touches rows in the same order, a cycle cannot form. Order by primary key, always:

-- session 1 and session 2 both do this, and neither can deadlock with the other
START TRANSACTION;
SELECT id FROM customer_order WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE customer_order SET phone = '111' WHERE id IN (1, 2);
COMMIT;

In application code the same rule applies to the order you update tables in: if one path writes customer_order then order_item, every path must. Batch jobs that process ids in a sorted order rather than an arbitrary one are applying the same rule.

2. Retry

Because a deadlock is a normal outcome, the caller must be able to try again:

for (int attempt = 1; attempt <= 3; attempt++) {
    try {
        return orderService.placeOrder(request);   // one transaction
    } catch (DeadlockLoserDataAccessException e) {
        if (attempt == 3) throw e;
        Thread.sleep(50L * attempt);               // brief backoff
    }
}

Two conditions make this safe. The retried unit must be the whole transaction, since the victim's work was entirely rolled back. And the operation must be idempotent — retrying a charge is not the same as retrying a read.

Reducing them in the first place

• Keep transactions short. Fewer locks held for less time is fewer chances to collide. Do slow work before opening the transaction.
• Touch fewer rows. A statement without a good index locks rows it did not need to examine — so an index is a concurrency fix as much as a speed one.
• Take the locks you need up front rather than escalating from a read to a write halfway through.
• Watch the counter. SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks' gives you a number to trend. Zero is not the target; a rising rate is the signal.

What to remember

• Deadlocks are normal under concurrency. Not retrying is the bug.
• The whole transaction is rolled back, so retry the whole thing.
• 1213 is a cycle, detected instantly; 1205 is one slow holder, after 50 seconds.
• SHOW ENGINE INNODB STATUS names both statements involved.
• Consistent lock order prevents them; REPEATABLE READ's gap locks cause extra ones.