Fix: ERROR 1213: Deadlock found when trying to get lock; try restarting transaction

ERROR 1213 means InnoDB found two transactions waiting on each other’s row locks in a cycle that could never resolve, so it killed one of them to unblock the other. This is deadlock detection working as designed, not a corruption or crash. The killed transaction is rolled back and the error message tells you exactly what to do: run it again. The engineering work is twofold: make sure your application actually retries, and read the deadlock report so the same pair of queries stops colliding.

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

What this error means

A deadlock needs two transactions and at least two locks. Transaction A locks row 1 and wants row 2; transaction B locks row 2 and wants row 1. Neither can proceed, and no amount of waiting fixes it, so InnoDB detects the cycle immediately and rolls back the transaction that is cheapest to undo. The survivor continues untouched. InnoDB records the full story (both queries, the locks held, the locks waited for) in the deadlock report, which is the single most useful artifact for fixing recurring cases.

Common causes

• Two code paths updating the same rows in opposite orders, such as one transferring from account A to B while another goes B to A.
• UPDATE or DELETE statements without a supporting index, causing InnoDB to lock scanned rows far beyond the ones actually changed.
• Gap and insert intention locks colliding under concurrent inserts into the same index range, common with INSERT ... ON DUPLICATE KEY UPDATE.
• Long transactions that hold locks across many statements, widening the window for a cycle to form.
• Batch jobs sweeping a table in one order while live traffic touches rows in another.
• Mixing lock granularities, like a SELECT ... FOR UPDATE range in one path and single row updates in another.

How to fix it

STABILIZE first. Deadlock victims are rolled back cleanly, so the immediate fix is retrying the failed transaction; if a batch job is deadlocking against live traffic in a loop, pause the job.

1. DIAGNOSE: read the latest deadlock report. It shows both transactions, their queries, and the exact locks in conflict.

SHOW ENGINE INNODB STATUS\G

Look at the LATEST DETECTED DEADLOCK section: transaction (1) and (2), the HOLDS THE LOCK(S) and WAITING FOR THIS LOCK TO BE GRANTED blocks, and which index each lock is on.

2. DIAGNOSE: capture every deadlock, not just the latest, while you investigate a recurring case.

SET GLOBAL innodb_print_all_deadlocks = ON;

tail -f /var/log/mysql/error.log | grep -A 40 "DEADLOCK"

3. FIX the application contract: wrap transactions in a retry loop for error 1213 (SQLSTATE 40001), rerunning the whole transaction with a short randomized backoff. Three to five attempts absorbs almost all genuine deadlocks.

4. FIX the access order: make every code path that touches the same set of rows lock them in one consistent order, for example always ascending by primary key.

BEGIN;
SELECT id FROM accounts WHERE id IN (42, 17) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 17;
UPDATE accounts SET balance = balance + 100 WHERE id = 42;
COMMIT;

5. FIX the lock footprint: check that the deadlocking statements use an index, because an unindexed WHERE locks everything it scans.

EXPLAIN UPDATE orders SET status = 'shipped' WHERE external_ref = 'ABC123';
CREATE INDEX idx_orders_external_ref ON orders (external_ref);

6. FIX transaction shape: keep transactions short, move reads that do not need locking outside the transaction, and chunk batch updates into small ranges committed separately.

How to prevent it

• Establish one canonical locking order for multi row updates and enforce it in code review.
• Index every column used in the WHERE clause of an UPDATE or DELETE on a busy table.
• Keep transactions as short as possible and never hold one open across a network call or user interaction.
• Run batch sweeps in primary key order, in small chunks, ideally off peak.
• Build retry on SQLSTATE 40001 into your database helper so every transaction gets it for free.
• Monitor the deadlock rate (SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks' on versions that expose it, or the error log with innodb_print_all_deadlocks) so regressions show up as a trend, not an outage.

When to call a senior engineer

Call for help when the same queries keep deadlocking after you fixed the ordering and indexes, when the deadlock report shows gap lock conflicts you cannot untangle, or when the retry storm itself is degrading throughput. Erzon engineers can reconstruct the full conflict from the InnoDB reports, redesign the transaction boundaries and locking order, and get the deadlock rate down to the background noise retries were meant for.