🚨 MySQL Error Guide: 'ERROR 1213 (40001)' Deadlock Found

Overview

ERROR 1213 (40001) is InnoDB’s deadlock error. Two (or more) transactions each hold a lock the other needs, forming a cycle. InnoDB detects the cycle, picks the cheaper transaction as the victim, rolls it back, and returns:

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

Unlike a lock wait timeout (ERROR 1205), a deadlock is resolved instantly — InnoDB does not wait, it breaks the cycle by killing one transaction. The SQLSTATE 40001 (serialization failure) signals that the operation is safe to retry. Deadlocks are usually not a sign of a broken database; they are a sign that transactions acquire locks in inconsistent orders.

It appears on any locking statement: UPDATE, DELETE, INSERT (including via foreign keys and unique-key gap locks), and SELECT ... FOR UPDATE.

Symptoms

• Occasional Deadlock found when trying to get lock under concurrency.
• One transaction in a pair is rolled back; the other succeeds.
• Frequency rises with traffic and around specific tables.
• SHOW ENGINE INNODB STATUS has a populated LATEST DETECTED DEADLOCK section.

SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-06-23 11:04:18
*** (1) TRANSACTION:
TRANSACTION 4218801, ACTIVE 0 sec starting index read
... UPDATE accounts SET balance = balance - 10 WHERE id = 1
*** (2) TRANSACTION:
TRANSACTION 4218802, ACTIVE 0 sec starting index read
... UPDATE accounts SET balance = balance + 10 WHERE id = 2
*** WE ROLL BACK TRANSACTION (1)

Common Root Causes

1. Inconsistent lock ordering between transactions

The classic deadlock: transaction A locks row 1 then row 2; transaction B locks row 2 then row 1. Each waits on the other.

*** (1) HOLDS THE LOCK(S): record lock on accounts id=1, WAITING FOR id=2
*** (2) HOLDS THE LOCK(S): record lock on accounts id=2, WAITING FOR id=1

If both transactions always locked ids in ascending order, the cycle could never form.

2. Missing index widening the lock set

Without an index on the WHERE column, InnoDB locks scanned rows (and gaps), so two unrelated statements collide on rows neither logically targets.

EXPLAIN UPDATE jobs SET state = 'done' WHERE token = 'abc';

+----+-------+------+---------------+------+------+-------+-------------+
| id | table | type | possible_keys | key  | rows | Extra |             |
+----+-------+------+---------------+------+------+-------+-------------+
|  1 | jobs  | ALL  | NULL          | NULL | 50000| Using where         |
+----+-------+------+---------------+------+------+-------+-------------+

type: ALL means a full scan locking many rows — a deadlock magnet. Adding an index on token shrinks the lock set to one row.

3. Gap / next-key locks under REPEATABLE READ

At the default REPEATABLE READ, range scans and inserts into unique indexes take gap locks. Concurrent inserts into the same gap can deadlock even on different key values.

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index uniq_email of table `app`.`users`
... lock_mode X locks gap before rec insert intention waiting

insert intention + gap lock waits between concurrent inserts is the signature.

4. Hot rows updated in opposite orders

Two code paths update the same set of rows (e.g. a parent and child, or two accounts in a transfer) in different sequences.

SELECT object_name, index_name, lock_type, lock_mode, lock_status
FROM performance_schema.data_locks WHERE lock_status = 'WAITING';

+-------------+------------+-----------+-----------+-------------+
| object_name | index_name | lock_type | lock_mode | lock_status |
+-------------+------------+-----------+-----------+-------------+
| accounts    | PRIMARY    | RECORD    | X         | WAITING     |
+-------------+------------+-----------+-----------+-------------+

5. Foreign-key locks taken in unexpected order

Inserting/updating a child row takes a shared lock on the parent. Mixing parent updates and child inserts across transactions can cycle.

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `app`.`orders`
... lock mode S locks rec but not gap waiting   (FK check on order_items)

A child insert (order_items) needs an S lock on the orders parent that another transaction’s X lock blocks.

6. Large multi-row transactions touching many rows

Big batch transactions hold many locks for longer, raising the odds of overlapping with another transaction in a cycle.

SELECT trx_id, trx_rows_locked, trx_query FROM information_schema.innodb_trx
ORDER BY trx_rows_locked DESC LIMIT 3;

+----------+-----------------+----------------------------------+
| trx_id   | trx_rows_locked | trx_query                        |
+----------+-----------------+----------------------------------+
| 4218900  | 4096            | UPDATE ledger SET ... WHERE ...  |
+----------+-----------------+----------------------------------+

Diagnostic Workflow

Step 1: Read the LATEST DETECTED DEADLOCK section

SHOW ENGINE INNODB STATUS\G

This is the single most important artifact: it shows both transactions, the exact statements, which locks each held, and which lock each was waiting for. Read it top to bottom.

Step 2: Identify the two statements and the resource

From the dump, note the two SQL statements and the index/rows involved. Determine the order in which each transaction acquired locks — the mismatch is the bug.

Step 3: Check the indexes used by both statements

EXPLAIN <statement A>;
EXPLAIN <statement B>;

A type: ALL or index scan on either side means the lock set is wider than necessary — a likely contributor.

Step 4: Enable a deadlock log if they are frequent

SET GLOBAL innodb_print_all_deadlocks = ON;

-- Every deadlock is now written to the MySQL error log, not just the latest.

This captures patterns over time instead of only the most recent occurrence.

Step 5: Inspect current lock waits live

SELECT r.trx_id AS waiting, r.trx_query AS waiting_query,
       b.trx_id AS blocking, b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

This shows contention that is about to deadlock or is timing out, complementing the post-mortem dump.

Example Root Cause Analysis

A money-transfer service logs sporadic ERROR 1213 under load. The deadlock dump shows:

*** (1) TRANSACTION 4218801:
UPDATE accounts SET balance = balance - 10 WHERE id = 1
HOLDS lock on id=1, WAITING FOR id=2
*** (2) TRANSACTION 4218802:
UPDATE accounts SET balance = balance + 10 WHERE id = 2
HOLDS lock on id=2, WAITING FOR id=1

Both transactions are transfers between accounts 1 and 2, but in opposite directions. Transfer A debits account 1 then credits account 2; transfer B debits account 2 then credits account 1. Each locks its first account, then blocks on the other’s — a perfect cycle.

Fix (code): always update the two accounts in a deterministic order, e.g. ascending by id, regardless of transfer direction:

-- Lock the lower id first, then the higher id, in every transfer:
UPDATE accounts SET balance = balance + (CASE id WHEN 1 THEN -10 WHEN 2 THEN 10 END)
WHERE id IN (1, 2) ORDER BY id;

With a consistent acquisition order the cycle is impossible. Combined with a retry-on-40001 wrapper for any residual deadlocks, the 1213 errors stop.

Prevention Best Practices

• Acquire locks in a consistent, deterministic order across all code paths (e.g. always ascending primary key). This single discipline eliminates most application deadlocks.
• Always wrap write transactions in retry-on-deadlock logic: catch SQLSTATE 40001, re-run the transaction a few times with small backoff. Deadlocks are expected under concurrency and safe to retry.
• Index every WHERE column used by UPDATE/DELETE so InnoDB locks only target rows, not scans and gaps.
• Keep transactions small and short; large multi-row transactions both increase cycle probability and make each rollback more expensive.
• Turn on innodb_print_all_deadlocks in busy systems so you can spot recurring lock-ordering patterns instead of only the latest event.
• For quick triage of a deadlock dump, the free incident assistant can summarize the two transactions into the likely ordering bug. More in MySQL guides.

Quick Command Reference

-- The post-mortem: latest deadlock detail
SHOW ENGINE INNODB STATUS\G

-- Persist all deadlocks to the error log
SET GLOBAL innodb_print_all_deadlocks = ON;

-- Confirm indexes used by the two statements
EXPLAIN <statement A>;
EXPLAIN <statement B>;

-- Live lock waits about to cycle
SELECT r.trx_id AS waiting, r.trx_query, b.trx_id AS blocking, b.trx_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

-- Biggest transactions by rows locked
SELECT trx_id, trx_rows_locked, trx_query FROM information_schema.innodb_trx
ORDER BY trx_rows_locked DESC LIMIT 3;

-- Current waiting locks
SELECT object_name, index_name, lock_type, lock_mode, lock_status
FROM performance_schema.data_locks WHERE lock_status = 'WAITING';

Conclusion

ERROR 1213 (40001) means InnoDB found a lock cycle and rolled back one transaction to break it. The usual root causes:

1. Transactions acquiring locks in inconsistent orders.
2. A missing index widening the lock set to scanned rows and gaps.
3. Gap / next-key locks colliding on concurrent inserts under REPEATABLE READ.
4. Hot rows updated in opposite sequences by different code paths.
5. Foreign-key locks taken in an unexpected order between parent and child.
6. Large multi-row transactions overlapping in a cycle.

Read the LATEST DETECTED DEADLOCK dump to find the two statements and their lock order, impose a consistent acquisition order, add the missing index, and always wrap writes in retry-on-40001 logic — deadlocks are normal under concurrency and meant to be retried.