MySQL — Deadlock Found When Trying to Get Lock (ERROR 1213)

Analyze MySQL ERROR 1213 Deadlock found when trying to get lock using SHOW ENGINE INNODB STATUS, fix lock ordering and indexing, and retry transactions idempotently.

advanceddebuggingmysqlinnodbtransactions

Symptoms

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction aborts a small fraction of transactions
SHOW ENGINE INNODB STATUS shows LATEST DETECTED DEADLOCK with two transactions waiting for each other
Deadlocks spike when two batch jobs or hot-row updates overlap

Possible causes

Two transactions acquire row locks in opposite orders on overlapping key sets
Updates scan via secondary indexes in different orders, locking gaps in conflicting sequences
A missing index turns updates into scans that lock far more rows than intended
Composite operations (check then update) open windows where interleaving inverts lock order

Diagnosis steps

Read the latest deadlock report

mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n "/LATEST DETECTED DEADLOCK/,/TRANSACTION/p" | head -60

Interpretation

The report prints both transactions: the locks each holds and the lock each waits for. The two wait edges are the cycle; map them to your statements.

Map the statements to lock orders

grep -Rn "UPDATE balances\|SELECT .* FOR UPDATE" src/ | head

Interpretation

Find the two call sites, list the row keys each acquires in order, and compare — opposite orderings on overlapping keys is the deadlock.

Check the execution plans involved

mysql -e "EXPLAIN UPDATE ... ;"

Interpretation

A type=index or full scan means the statement locks rows far beyond the intended set, widening collision windows with every concurrent transaction.

Root cause

InnoDB detects a cycle: transaction A holds locks transaction B waits for, while B holds locks A waits for. It rolls back one victim with ERROR 1213. The cycle almost always traces to inconsistent acquisition order across code paths — different statement sequences locking the same rows in different sequences.

Fix

Normalize lock order across all paths (sort keys before updating, update in a consistent direction), add missing indexes so statements lock only intended rows, shrink transactions, and retry 1213 victims automatically with bounded backoff since the rollback makes retry safe.

Risk note

Retrying without idempotency or inside an outer transaction duplicates side effects. Retry at the transaction boundary with the same input only.

Verify the fix

Under the deadlock-triggering concurrent workload, assert zero new 1213 occurrences and no change in business outcomes; previously failing scenarios complete.

Symptom walkthrough

MySQL does the analysis for you — read it:

mysql> SHOW ENGINE INNODB STATUS\G
--------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
UPDATE accounts SET balance = balance - 10 WHERE id = 7;
HOLDS THE LOCK(S): record lock on id = 3
WAITING FOR: record lock on id = 7
*** (2) TRANSACTION:
UPDATE accounts SET balance = balance + 10 WHERE id = 3;
HOLDS THE LOCK(S): record lock on id = 7
WAITING FOR: record lock on id = 3

Transaction 1 holds 3 wants 7; transaction 2 holds 7 wants 3. That is the entire cycle. Map each statement back to its code path and you have the two orderings that must be unified.

Fix in practice

Normalize the acquisition order everywhere:

-- every path updates low id first, high id second:
UPDATE accounts ... WHERE id = LEAST(a, b);
UPDATE accounts ... WHERE id = GREATEST(a, b);

ids = sorted([from_id, to_id])          # application-side same rule
for i in ids:
    transfer_step(i, delta)

Make statements lock exactly the intended rows (EXPLAIN to verify index use), keep transactions short, and add the retry the error message itself suggests:

for attempt in range(3):
    try:
        conn.begin()
        transfer(conn, from_id, to_id, amount)
        conn.commit()
        break
    except DeadlockError as e:        # 1213: victim rolled back, safe to retry
        conn.rollback()
        time.sleep(0.05 * 2 ** attempt)

The retry is safe specifically because InnoDB rolled the victim back entirely — but only at the transaction boundary, never mid-transaction, and only with idempotent input.

Verification

Reproduce the original concurrent workload — two overlapping batch jobs or the transfer paths named in the deadlock report — and watch SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks': the counter stays flat across the run instead of climbing per batch, and previously failing scenarios complete without manual retries. For the retry path, deliberately inject a 1213 in a test double and assert the transaction executes exactly the intended number of times with identical input, proving idempotency rather than assuming it. Distinguish the neighbor error 1205 (lock wait timeout): it means a transaction waited past innodb_lock_wait_timeout without a cycle — the fix there is shorter transactions and indexed access, not reordering.