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

What it means

InnoDB locks the rows a transaction changes (and, for locking reads, the rows it reads) until the transaction ends. A deadlock is a cycle: transaction A holds a lock B needs, and B holds one A needs. Neither can ever go on, so InnoDB notices straight away, picks one as the victim and rolls it back. The victim gets error 1213; the other carries on.

Two things matter:

• The whole transaction is rolled back, not only the last statement. Everything it did before is gone.
• It’s safe to retry. The message says so: “try restarting transaction”. Deadlocks are normal under concurrent writes; the fix is to retry them, and to make them rare.

Common causes

1. Rows locked in different orders. One request updates account 1 then 2; another updates 2 then 1 at the same moment.
2. Check-then-insert. Two transactions run SELECT … FOR UPDATE for a row that doesn’t exist yet (each takes a gap lock, which doesn’t block the other), then both INSERT it, and each insert waits for the other’s gap lock.
3. Long transactions that hold locks while doing slow work, widening the window for a clash.
4. Missing indexes. An UPDATE or DELETE whose WHERE can’t use an index locks every row it scans, not only the ones it changes.

How to fix it

Retry the transaction

Catch error 1213 (SQLSTATE 40001) in your code and run the whole transaction again from the start, ideally after a short random pause, a few times at most. Re-running only the failed statement isn’t enough: the earlier statements were rolled back too.

Lock rows in a consistent order

If a transaction touches several rows or tables, always do it in the same order, for example by ascending primary key:

START TRANSACTION;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

Replace check-then-insert with one statement

Instead of SELECT … FOR UPDATE followed by INSERT, let the unique key decide:

INSERT INTO slots (id, label) VALUES (5, 'A') AS new
ON DUPLICATE KEY UPDATE label = new.label;

(On MariaDB, write label = VALUES(label); see duplicate entry.)

Or run the transaction at READ COMMITTED, which doesn’t take gap locks for those reads. In the test below, the loser then got a duplicate key error instead of a deadlock, which is easier to handle.

Keep transactions short, and index the WHERE

Commit as soon as the related changes are done; don’t hold a transaction open across network calls or user input. Check that UPDATE and DELETE statements use an index (EXPLAIN them) so they lock only the rows they need.

Read the deadlock report

InnoDB keeps the most recent deadlock:

SHOW ENGINE INNODB STATUS;

Look for the LATEST DETECTED DEADLOCK section: each transaction’s last statement, the locks it held and the one it waited for, and which one was rolled back. Only the latest is kept; to log every deadlock to the error log, set innodb_print_all_deadlocks = ON (it’s off by default).

Reproduce it

On MySQL 8.4.11, accounts with rows 1 and 2, two sessions a second apart:

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- (2 seconds)
UPDATE accounts SET balance = balance + 10 WHERE id = 2;   -- waits for B
COMMIT;

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = balance - 5 WHERE id = 2;
-- (2 seconds)
UPDATE accounts SET balance = balance + 5 WHERE id = 1;    -- closes the cycle

Session B got:

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

Session A committed. The table ended with A’s change only (90 and 110): B’s first update was rolled back with the rest of its transaction. The report, trimmed:

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 4182, ACTIVE 3 sec starting index read
UPDATE accounts SET balance = balance + 10 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS … index PRIMARY of table `seo_mysql`.`accounts` trx id 4182 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS … index PRIMARY of table `seo_mysql`.`accounts` trx id 4182 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 4183, ACTIVE 2 sec starting index read
UPDATE accounts SET balance = balance + 5 WHERE id = 1
…
*** WE ROLL BACK TRANSACTION (2)

The check-then-insert pattern deadlocked too: both sessions ran SELECT * FROM slots WHERE id = 5 FOR UPDATE on a missing row, then INSERT INTO slots VALUES (5, …), and the second got 1213. Run again at READ COMMITTED, the second session got ERROR 1062 (23000): Duplicate entry '5' for key 'slots.PRIMARY' instead.

MariaDB 11.4.13 behaved identically in both tests, with the same message and SQLSTATE. Both servers default to REPEATABLE READ with deadlock detection on.

In Inlet

Inlet’s Activity monitor shows the server’s sessions, and which one is waiting on which, while they’re stuck. If you use manual-commit transactions in the query editor, commit or roll back when you’re done: an open transaction keeps its locks and can be one side of a deadlock.