Deadlocks

Why two transactions can trap each other, how InnoDB breaks the cycle, and the lock ordering that prevents it.

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

A deadlock is a cycle: transaction A holds a lock B wants, and B holds a lock A wants. Neither can proceed, and no amount of waiting will help. It is a normal event in a busy database, not a sign of corruption.

How it happens

The classic cause is two transactions locking the same rows in opposite order. A locks row 7 then asks for row 12; B locks row 12 then asks for row 7. Gap locks produce subtler versions: two transactions can each hold a gap lock on the same gap — gap locks do not conflict with each other — and then both block on the other's insert intention lock.

How InnoDB reacts

innodb_deadlock_detect is ON by default. When a cycle is found, InnoDB picks a victim — usually the transaction that changed the fewest rows — rolls it back completely, and returns error 1213, ER_LOCK_DEADLOCK, SQLSTATE 40001: Deadlock found when trying to get lock; try restarting transaction. The other transaction proceeds as if nothing happened.

Do not confuse this with error 1205, ER_LOCK_WAIT_TIMEOUT. That is a single transaction waiting too long for a lock, governed by innodb_lock_wait_timeout, and by default it rolls back only the statement, leaving the transaction open.

Avoiding them

Lock rows in the same order in every code path, keep transactions short, and index the columns in UPDATE ... WHERE and SELECT ... FOR UPDATE so fewer rows get locked. Then retry: no discipline removes deadlocks entirely.

Example

Example · sql
-- The fix: every transaction takes its locks in ascending id order,
-- so no cycle can form no matter which direction the transfer runs.
START TRANSACTION;

SELECT id, balance
FROM   accounts
WHERE  id IN (7, 12)
ORDER  BY id           -- deterministic order is the whole point
FOR UPDATE;

UPDATE accounts SET balance = balance - 250.00 WHERE id = 7;
UPDATE accounts SET balance = balance + 250.00 WHERE id = 12;

COMMIT;

-- A transfer in the other direction (12 -> 7) locks 7 then 12 as well,
-- because the ORDER BY does not depend on which account is the source.
-- The second transaction simply waits for the first, then proceeds.

When to use it

  • Fixing a transfer or swap routine that deadlocks whenever two users act on the same pair of rows.
  • Adding retry-on-40001 to the transaction wrapper in an application's data layer.
  • Reading SHOW ENGINE INNODB STATUS to identify which two statements formed a cycle.
  • Deciding whether a batch job should sort its input by primary key before updating.

More examples

The cycle, step by step

Two transfers between the same pair of accounts, running in opposite directions. Nothing here is wrong in isolation — each transaction is short, indexed and correct. The bug only exists across two sessions, which is why deadlocks survive code review and appear first in production. This is the shape to look for in your own code: the same two tables or rows touched in different orders by different call paths.

Example · sql
-- Session A                          -- Session B
START TRANSACTION;
UPDATE accounts                       START TRANSACTION;
SET balance = balance - 250           UPDATE accounts
WHERE id = 7;   -- X lock on 7        SET balance = balance - 100
                                      WHERE id = 12;  -- X lock on 12

UPDATE accounts
SET balance = balance + 250
WHERE id = 12;  -- waits for B
                                      UPDATE accounts
                                      SET balance = balance + 100
                                      WHERE id = 7;   -- waits for A
                                      -- cycle closed; InnoDB rolls one back
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
-- try restarting transaction

Reading the post-mortem

SHOW ENGINE INNODB STATUS is the first command to run after a 1213, but it only retains the latest deadlock — on a busy server the one you care about is often already overwritten. Turning on innodb_print_all_deadlocks before a load test, or for a day in production, is how you get a representative sample instead of a single lucky capture. Remember to turn it off again.

Example · sql
-- The LATEST DETECTED DEADLOCK section shows both transactions, the
-- exact statements, the locks held and waited for, and which was rolled back.
SHOW ENGINE INNODB STATUS\G

-- Only the most recent deadlock is kept. To capture every one in the
-- error log instead of losing all but the last:
SET GLOBAL innodb_print_all_deadlocks = ON;

-- How much lock contention there is overall. Note this counts row-lock
-- waits, not deadlocks -- SHOW STATUS has no deadlock counter:
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';

Retrying the transaction, not the statement

Match on SQLSTATE 40001 rather than the numeric 1213 — it is the portable spelling and most drivers expose it. Retry the entire transaction from START TRANSACTION, because the victim was rolled back in full, and add jitter so two retrying transactions do not immediately deadlock again. Cap the attempts: a deadlock that survives three retries is a design problem, not bad luck.

Example · sql
-- Pseudocode for the application layer; the SQL is the whole unit of work.
--
-- for attempt in 1..3:
--     try:
--         execute(TRANSFER_SQL)      -- all of it, from START TRANSACTION
--         break
--     except SQLSTATE '40001':       -- ER_LOCK_DEADLOCK
--         sleep(random 10-50 ms)     -- jitter, so retries do not collide
--         continue

-- TRANSFER_SQL:
START TRANSACTION;
SELECT id FROM accounts WHERE id IN (?, ?) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;
COMMIT;

Related

sql lockingsql isolationsql begin commitsql acidsql create index

Discussion

  • Be the first to comment on this lesson.