MySQL Error 1213: Deadlock Found — Fix It Without Restarting

Yeah, seeing ER_LOCK_DEADLOCK (1213) in your logs at 2am is annoying, especially when the app just retries and buries it. Let's fix it.

The Actual Fix

Error 1213 means InnoDB detected a circular lock dependency between two transactions and rolled one back on purpose. It's not a bug, it's a design decision. The transaction it kills is the one that's cheaper to redo.

The immediate fix is to make your app retry the transaction, but that's only half of it. You need to find why the lock order differs between the two transactions.

1. Turn on deadlock logging. On MySQL 5.6.15+ and MariaDB 10.0+, the error log captures the last deadlock automatically. Check with:

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
-- If OFF:
SET GLOBAL innodb_print_all_deadlocks = ON;

Then look at the error log (/var/log/mysql/error.log on Debian/Ubuntu, /var/lib/mysql/*.err on RHEL/CentOS/Amazon Linux). You'll see something like LATEST DETECTED DEADLOCK with the two transactions and the exact rows involved.

2. Pull the current deadlock from InnoDB directly if you can't restart MySQL to enable logging:

SHOW ENGINE INNODB STATUS\G

Scroll to LATEST DETECTED DEADLOCK. You'll see two transaction blocks. Compare the WAITING FOR and HOLDS THE LOCK(S) sections — that's your smoking gun.

3. Reorder your SQL. This is the real fix. If transaction A locks orders then order_items, and transaction B locks order_items then orders, you get a deadlock every time they interleave. Pick one canonical order and use it everywhere. Usually the parent table first, then children.

4. Add retry logic for the residual cases you can't reorder (secondary indexes, gap locks under REPEATABLE READ). Retry the whole transaction, not just the failing statement.

-- Pseudocode — do this in your app layer
for attempt in 1..3:
    try:
        BEGIN;
        ...all your statements...
        COMMIT;
        break;
    except ER_LOCK_DEADLOCK (1213):
        ROLLBACK;
        sleep(random(10..100) * attempt ms);
        continue;

Don't retry on 1205 (lock wait timeout) with the same backoff — that one often means a long-running transaction is stuck and retrying just piles up more connections.

Why That Works

InnoDB uses row-level locks. Under REPEATABLE READ (the default), it also uses gap locks and next-key locks for range scans, which is where most surprise deadlocks come from. When two transactions acquire locks in incompatible orders, InnoDB's deadlock detector runs, builds a wait-for graph, finds a cycle, and rolls back the transaction with fewer rows modified (usually).

By giving every transaction the same lock acquisition order, you eliminate the cycle before it can form. The retry logic handles the edge cases — secondary index gap locks, foreign key checks, insert intention locks — that you can't always order deterministically.

The retry is necessary because even with perfect ordering, two concurrent inserts into a unique index can still deadlock on the gap. That's not your bug, it's how MVCC works.

Weird Variants You Might Hit

Deadlocks on INSERT only

Two sessions doing INSERT INTO ... ON DUPLICATE KEY UPDATE on the same unique key can deadlock. Each one acquires a shared lock on the duplicate check, then tries to upgrade to exclusive. Classic. Fix: retry, or use INSERT IGNORE plus a separate UPDATE if you can tolerate the round trip.

Deadlocks across foreign keys

InnoDB takes shared locks on parent rows when inserting into child tables. If parent and child tables are updated in different orders across two transactions, you get a deadlock that looks mysterious because neither transaction touched the same table twice. Reorder so parent is always updated (or written) before child.

Deadlocks under READ COMMITTED

Switching from REPEATABLE READ to READ COMMITTED removes gap locks and kills a huge class of deadlocks. It's a legitimate fix. What you lose: consistent reads across statements inside a transaction. For most web apps, that's fine. Talk to whoever owns your data consistency requirements before flipping it globally.

[mysqld]
transaction-isolation = READ-COMMITTED
innodb_print_all_deadlocks = ON

Deadlocks from SELECT ... FOR UPDATE without an index

If your WHERE clause doesn't hit an index, InnoDB locks every row it scans. Two transactions scanning the same table with different filters will deadlock. Run EXPLAIN on that query. If it says type: ALL, add the index.

Deadlocks on MySQL 8.0 with SKIP LOCKED

Even with SKIP LOCKED you can still deadlock if you mix it with regular locking reads in the same transaction. Pick one pattern per transaction and stick with it.

Prevention

• Keep transactions short. Don't do HTTP calls, file I/O, or logging to external systems between BEGIN and COMMIT. The longer a transaction lives, the more chances to collide.
• Touch tables in a fixed order. Write it down. Enforce it in code review. This single rule kills 80% of deadlocks I've seen.
• Index your foreign keys. Missing FK indexes are a deadlock factory. Check with:

SELECT table_name, column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL;

• Set a sane innodb_lock_wait_timeout. 50 seconds (default) is absurd for a web request. 10-15 seconds is more useful. Set it per-session from the app if you need different values:

SET SESSION innodb_lock_wait_timeout = 10;

• Alert on 1213 in your app logs. A deadlock every hour is a smell. A deadlock every minute is an outage waiting to happen.
• Enable innodb_print_all_deadlocks in production. Yes, in production. The overhead is trivial and you'll thank yourself later.
• Don't bother with LOCK TABLES as a workaround. It serializes everything and tanks throughput. Fix the ordering instead.

One last thing: don't catch 1213 and silently swallow it. Either retry the transaction or surface it. Silent retries hide real lock order bugs that will eventually bite you under load.