MySQL ERROR 1213: Deadlock Found When Trying to Get Lock

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. InnoDB detects the cycle, picks one transaction as the victim, rolls it back, and returns error 1213 to that client. The other transaction continues normally.

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

Deadlocks are a normal consequence of concurrent writes, and a low rate of them is not a bug. What you must do is make sure the application retries, and reduce how often they happen.

Step 1: read the deadlock report

InnoDB keeps the most recent deadlock:

SHOW ENGINE INNODB STATUS\G

Look for the section LATEST DETECTED DEADLOCK. It shows both transactions, the statements they were running, the locks each held and each was waiting for, and which one was rolled back ("WE ROLL BACK TRANSACTION (2)").

Only the latest deadlock is kept, and it disappears after a restart. To record all of them in the MySQL error log, enable:

SET GLOBAL innodb_print_all_deadlocks = ON;

Add innodb_print_all_deadlocks = ON under [mysqld] in my.cnf to keep it. Logs can grow large on a busy server, so turn it on during investigation.

Step 2: understand the typical pattern

Two sessions update the same rows in opposite order:

-- Session A                         -- Session B
BEGIN;                               BEGIN;
UPDATE accounts SET bal = bal - 10   UPDATE accounts SET bal = bal - 5
 WHERE id = 1;                        WHERE id = 2;
UPDATE accounts SET bal = bal + 10   UPDATE accounts SET bal = bal + 5
 WHERE id = 2;   -- waits for B       WHERE id = 1;   -- waits for A

Each waits for the other, and InnoDB breaks the cycle. Other common sources are gap locks and next-key locks under REPEATABLE READ, concurrent inserts into a table with a unique index, INSERT ... ON DUPLICATE KEY UPDATE on the same keys, and SELECT ... FOR UPDATE over a range.

Step 3: reduce the chance of deadlocks

1. Access rows in a consistent order. Always lock or update lower ids first, sorted, in every code path. This removes the classic cycle shown above.
2. Keep transactions short. Do not hold a transaction open across API calls, emails or user input. Commit as soon as the writes are done.
3. Add indexes. An UPDATE or DELETE that scans without an index locks many more rows, which widens the collision window. Check with EXPLAIN.
4. Touch fewer rows per transaction. Chunk large batch jobs (for example 1,000 rows at a time).
5. Consider READ COMMITTED for workloads that suffer from gap-lock deadlocks. It reduces gap locking in InnoDB, but check that your application and replication format (use row-based binary logging) tolerate it:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1. Lock up front. If you will update a row anyway, take SELECT ... FOR UPDATE first instead of reading, then upgrading the lock later. Upgrade conflicts are a major deadlock source.
2. Move heavy jobs out of peak hours and track them with cron monitoring.

Step 4: retry in the application

The error text itself says to restart the transaction. Roll back, then rerun the whole transaction, not just the failing statement:

import time, random
for attempt in range(3):
    try:
        with conn.cursor() as cur:
            transfer(cur, src, dst, amount)
        conn.commit()
        break
    except OperationalError as e:
        conn.rollback()
        if e.args[0] in (1213, 1205) and attempt < 2:
            time.sleep(random.uniform(0.05, 0.2) * (attempt + 1))
            continue
        raise

Many frameworks have built-in support, such as the second argument to DB::transaction($callback, $attempts) in Laravel. Retrying only helps if the transaction is idempotent, so do not send an email inside the loop.

Step 5: deadlock detection settings

From MySQL 8.0.1, innodb_deadlock_detect (default ON) controls active detection. Turning it off can improve throughput on workloads with very high concurrency on the same rows, but then you rely on innodb_lock_wait_timeout to break deadlocks, so waits are far longer. Do not change it unless you have measured the cost.

Lock waits versus deadlocks

If statements are blocked for 50 seconds and then fail with a timeout, that is error 1205, caused by a lock held too long rather than a cycle. The diagnosis tools overlap, but the fixes differ.

Next steps

• Compare with MySQL error 1205: lock wait timeout.
• See the PostgreSQL equivalent in deadlock detected.
• Find slow requests that pile up locks with the Website Health Check.
• Add an Uptime Monitor on a page that writes to the database.

FAQ

Is a deadlock a sign that my database is corrupt?

No. It is a concurrency conflict between two transactions, and the database is fine. The victim's changes are rolled back cleanly.

Which transaction does MySQL roll back?

InnoDB generally chooses the transaction that has modified the fewest rows, treating it as the cheapest to undo. The deadlock report names the victim.

Can I prevent deadlocks completely?

No, not in a write-heavy system. Reduce them with consistent ordering and short transactions, and always retry.

Does MyISAM have deadlocks?

MyISAM uses table-level locks, so it blocks instead of deadlocking in most cases. It also lacks transactions, so InnoDB is the right engine for concurrent writes.

This page is general information written by our editorial team and updated when documentation changes. Servers and providers differ, so test changes safely first. See our editorial policy and disclaimer.