MySQL error 1213: Deadlock found when trying to get lock
Two transactions were each waiting for a lock the other held. InnoDB rolled one back; run the whole transaction again.
- Error number
1213- Name
ER_LOCK_DEADLOCK- SQLSTATE
40001- Category
- Transactions and locking
What it means
A deadlock is a cycle of lock waits that can't resolve itself. InnoDB detects it right away (with innodb_deadlock_detect on, the default) and rolls back the transaction it estimates is cheaper to undo, releasing its locks. 1213 tells the rolled-back session to retry.
The whole transaction was rolled back, not just the last statement, so retry it from the start.
Common causes
- Two transactions updating the same rows in a different order.
- Range or gap locks from
UPDATE,DELETEorSELECT ... FOR UPDATEon non-unique or missing indexes in REPEATABLE READ. - Concurrent inserts into a unique index combined with deletes on the same key range.
- Foreign key checks taking shared locks on parent rows.
How to fix it
-
Catch 1213 in the application and retry the whole transaction, a few times with a short back-off. Occasional deadlocks are normal.
-
Read the details of the last deadlock: both transactions, the locks held and wanted.
SHOW ENGINE INNODB STATUS; -- LATEST DETECTED DEADLOCK
-
Access rows in a consistent order and keep transactions short.
START TRANSACTION; SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
-
To log every deadlock, not just the latest, turn on
innodb_print_all_deadlocks.SET PERSIST innodb_print_all_deadlocks = ON;
Related errors
1205Lock wait timeout exceeded40P01PostgreSQL: deadlock detected40001PostgreSQL: could not serialize access1205SQL Server: Deadlocked and chosen as the victimORA-00060Oracle: deadlock detected while waiting for resource
More transactions and locking errors
3572Lock couldn't be acquired with NOWAIT1206Locks exceed the lock table size1792Write in a READ ONLY transaction3024Maximum statement execution time exceeded
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.