MySQL "Deadlock found when trying to get lock" (Error 1213): Read the Log and Stop It Repeating

"Deadlock found when trying to get lock; try restarting transaction" is MySQL error 1213. It appears when two or more InnoDB transactions each hold a lock the other one needs. InnoDB notices the cycle, picks a victim, rolls it back and lets the other transaction finish. A deadlock now and then is not an outage. Hundreds per hour, or the same two queries colliding every day, usually point to a fixable problem in how the application touches its data.

What the error tells you

The full error looks like this:

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

According to the MySQL server error reference, the error name is ER_LOCK_DEADLOCK and the SQLSTATE is 40001, the standard serialization-failure class. Three facts from the manual shape everything else:

• The whole transaction is gone. The InnoDB error handling page says a deadlock causes InnoDB to roll back the entire transaction, and tells you to retry the entire transaction. This is different from a lock wait timeout (error 1205), which by default rolls back only the statement that timed out.
• InnoDB prefers small victims. The deadlock detection page says InnoDB tries to roll back small transactions, measuring size by the number of rows inserted, updated or deleted.
• Detection is on by default. innodb_deadlock_detect defaults to ON, so the error comes back almost immediately instead of after innodb_lock_wait_timeout seconds.

If you are seeing 1205 rather than 1213, that is a different problem, one transaction simply waiting too long for another, and our guide to "Lock wait timeout exceeded" covers it.

The classic shape: same rows, opposite order

The manual describes the most common cause: transactions lock rows in several tables, or several rows in one table, but in opposite order. A minimal version:

-- Session A                           -- Session B
UPDATE accounts SET ... WHERE id = 1;   UPDATE accounts SET ... WHERE id = 2;
UPDATE accounts SET ... WHERE id = 2;   UPDATE accounts SET ... WHERE id = 1;
--        waits for B                   --   deadlock: one of them gets 1213

Each session holds the row the other one wants next. Neither can move, so InnoDB has to break the cycle. In real code this is rarely two literal updates. More often one code path updates the order and then the stock row, and another code path, written by someone else a year later, updates stock and then the order.

The manual also notes that you can get a deadlock in transactions that insert or delete a single row, because those operations set locks on several index records of that row. "But we only touch one row" is not proof that a deadlock is impossible.

Step 1: read LATEST DETECTED DEADLOCK

SHOW ENGINE INNODB STATUS (it needs the PROCESS privilege) contains a section with the most recent deadlock. The deadlock example in the manual shows its structure:

SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
*** (1) HOLDS THE LOCK(S):
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) TRANSACTION:
*** (2) HOLDS THE LOCK(S):
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
*** WE ROLL BACK TRANSACTION (2)

For each transaction you get the statement it was running, the locks it held and the lock it was waiting for, then which transaction InnoDB rolled back. When reading it, look for:

• The two statements. Find them in your code. The question is almost always "why do these two code paths lock the same rows in a different order?"
• The index name in each lock line. A lock on PRIMARY is expected; a lock on an index you did not expect, or a large number of locks for a statement that should touch one row, often means the query is scanning more than it needs.
• The lock mode. InnoDB uses shared (S) and exclusive (X) locks, and record, gap and next-key variants. A gap lock in the output under REPEATABLE READ tells you the statement locked a range, not just existing rows.

The catch: this section only shows the latest deadlock. On a busy server it is overwritten before you look.

Step 2: log every deadlock and count them

Turn on innodb_print_all_deadlocks. It defaults to OFF, is dynamic, and, per the InnoDB parameters reference, records information about all deadlocks in InnoDB user transactions in the error log:

SET GLOBAL innodb_print_all_deadlocks = ON;
SELECT @@log_error;

The manual suggests disabling it when you are finished debugging. Some teams leave it on permanently, because deadlock entries are only written when a deadlock happens. Decide based on how much your error log already grows. Remember that SET GLOBAL is lost on restart unless you also put the setting in the config file or use SET PERSIST.

For a trend line, InnoDB keeps a counter:

SELECT name, status, count
FROM information_schema.INNODB_METRICS
WHERE name = 'lock_deadlocks';

In the INNODB_METRICS listing in the manual lock_deadlocks is shown as enabled by default. If status says disabled on your server, enable it with innodb_monitor_enable. Export the counter to your monitoring and alert on the rate, not on single events. A sudden jump after a deploy points straight at the change that introduced it.

For a live view of who holds and waits for locks right now, performance_schema.data_locks and data_lock_waits show similar lock information in table form, including the lock mode, status and locked key values.

Step 3: the usual causes and their fixes

Inconsistent lock order

The manual recommends doing multi-table or multi-row changes in a consistent order every time, so that transactions form a queue instead of a cycle. Practical versions: always update parent before child; when updating a batch of rows, sort the keys first; keep each kind of change in one function or stored routine instead of repeating similar UPDATE sequences in different places.

Missing or poor indexes

This one surprises people. The locking reference says a locking read, UPDATE or DELETE generally sets record locks on every index record it scans, whether or not the WHERE clause later excludes the row. If there is no suitable index and MySQL scans the whole table, every row of the table becomes locked. An UPDATE ... WHERE status = 'new' AND customer_id = ? without an index on customer_id can lock far more than one customer's rows and collide with everything. Check the deadlocked statements with EXPLAIN; the slow query log often shows the same statements as slow ones.

Concurrent inserts of the same key

The manual documents a three-session case. When an INSERT hits a duplicate key, InnoDB sets a shared lock on the duplicate index record. If the session holding the exclusive lock then rolls back, the waiting sessions all get shared locks and all need exclusive ones:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

-- Session 1: START TRANSACTION; INSERT INTO t1 VALUES (1);
-- Session 2: START TRANSACTION; INSERT INTO t1 VALUES (1);   -- waits
-- Session 3: START TRANSACTION; INSERT INTO t1 VALUES (1);   -- waits
-- Session 1: ROLLBACK;
--   sessions 2 and 3 now both hold a shared lock and both need an exclusive one

The same can happen when the first session deletes the row and commits. This is typical for "insert if missing" logic run from many workers at once. INSERT ... ON DUPLICATE KEY UPDATE behaves differently: on a duplicate it takes an exclusive lock rather than a shared one. Whether that removes your specific deadlock depends on the rest of the transaction, so test it against the deadlock you actually see.

Long transactions and locking reads

The longer a transaction holds locks, the more chances it has to collide. The manual's advice: keep transactions small and short, commit right after a set of related changes, and do not add FOR UPDATE or FOR SHARE to a SELECT that can read an older snapshot.

Isolation level

The default InnoDB isolation level is REPEATABLE READ. Under READ COMMITTED, locking reads, updates and deletes lock only index records, not gaps (gap locking remains for foreign-key and duplicate-key checks), and locks on rows that do not match the WHERE clause are released after evaluation. The manual says this greatly reduces the probability of deadlocks, but they can still happen. Switching isolation level changes visibility rules for your application, so treat it as a design decision, not a quick fix. With binary logging enabled, note that the manual says only row-based binary logging is supported with READ COMMITTED.

Step 4: retry properly

The manual is blunt: always be prepared to re-issue a transaction that fails because of a deadlock. Even with perfect lock order you will get some. A safe retry has three properties:

1. It replays the whole transaction, from BEGIN, including any reads whose results fed later statements. Repeating only the failed statement after a 1213 applies it without the earlier changes of the transaction, because those were already rolled back.
2. It is bounded. Two or three attempts, then fail loudly. Endless retries hide a real problem and add load during the worst moment.
3. It waits a short random time before trying again, so the same two transactions do not collide again in lockstep.

for attempt in 1..3:
    try:
        BEGIN
        ... all statements of the transaction ...
        COMMIT
        break
    except error 1213:          # SQLSTATE 40001
        ROLLBACK                # InnoDB already rolled it back; this cleans up the session
        sleep(random 50-200 ms * attempt)
        continue                # run the WHOLE transaction again, not just the last statement
raise after the last attempt

Be careful with anything outside the database inside the retried block: emails, HTTP calls, queue messages. They are not rolled back with the transaction and will run again on every attempt. Move them after the commit.

If you also run PostgreSQL, the mechanics there are similar but the diagnostics differ; see our article on PostgreSQL "deadlock detected".