About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction means two or more InnoDB transactions were waiting for each other, and the server rolled yours back to break the cycle. The MySQL manual says to retry: "A transaction deadlock causes InnoDB to roll back the entire transaction. Retry the entire transaction when this happens." To stop it recurring, find the two statements involved and make every transaction take its locks in the same order.
What the error means
The MySQL error reference lists error 1213 with the symbol ER_LOCK_DEADLOCK, SQLSTATE 40001 and the message "Deadlock found when trying to get lock; try restarting transaction".
The manual defines a deadlock as "a situation in which multiple transactions are unable to proceed because each transaction holds a lock that is needed by another one." When deadlock detection is enabled (the default), InnoDB "detects the condition and rolls back one of the transactions (the victim)". It "tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted." Detection is controlled by innodb_deadlock_detect, which defaults to ON.
This is different from error 1205. A lock wait timeout (1205, Lock wait timeout exceeded) happens when a transaction waits longer than innodb_lock_wait_timeout (default 50 seconds) for a row lock, and only the statement is rolled back by default. A deadlock (1213) is found immediately: the manual says the lock wait timeout "does not apply to deadlocks when innodb_deadlock_detect is enabled (the default) because InnoDB detects deadlocks immediately". The two errors need different fixes. The timeout case is covered under the title "MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction".
The manual says deadlocks "are not dangerous unless they are so frequent that you cannot run certain transactions at all", and that "You can get deadlocks even in the case of transactions that just insert or delete a single row", because those operations "automatically set locks on the (possibly several) index records of the row inserted or deleted". It also says: "even if your application logic is correct, you must still handle the case where a transaction must be retried."
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| Two transactions update the same rows or tables in opposite order | Two statements each waiting for a lock the other holds | SHOW ENGINE INNODB STATUS lists both transactions, what each holds and what each waits for |
| A locking read followed by an update on the same rows | Deadlocks between sessions that SELECT ... FOR SHARE then UPDATE |
The report shows a shared lock held and an exclusive lock waited for on the same record |
| Missing index, so statements scan and lock many rows | Frequent deadlocks on a busy table; broad locks | EXPLAIN on the statements in the report shows full or large scans |
| Long transactions or interactive sessions left open | Locks held far longer than needed | The report's ACTIVE seconds for a transaction are large |
| Very long wait-for lists | The report says the search was too deep or long | The report contains TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION |
Prove the cause before changing anything
All of these are read-only.
1. Read the last deadlock. The manual says "To view the last deadlock in an InnoDB user transaction, use SHOW ENGINE INNODB STATUS." Find the section headed LATEST DETECTED DEADLOCK.
SHOW ENGINE INNODB STATUS;
In the manual's worked example, the section contains the two transactions in the cycle, labelled (1) TRANSACTION and (2) TRANSACTION. For each it gives the statement text, a HOLDS THE LOCK(S) block, a WAITING FOR THIS LOCK TO BE GRANTED block, and ends with a line WE ROLL BACK TRANSACTION naming the victim. Read the two statements and the tables and indexes in the lock lines. Good result: you can name two code paths. Bad result: the section is empty or old. Only the latest deadlock is shown, so the one you want may have been overwritten.
2. Count deadlocks. The manual's example reads the counter from the Information Schema:
SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks";
The metrics page lists lock_deadlocks as enabled by default. Run the query twice, some hours apart. A rising count means deadlocks are still happening. The manual says counter status is not persistent across a restart, so a restart resets what you read.
3. Check the setting that logs every deadlock.
SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';
The manual says that when it is enabled, "information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock". The default is OFF.
4. Find the error log. The manual's deadlock example uses this query to show the log path.
SELECT @@log_error;
5. Check the isolation level the application uses if the statements involve locking reads. The manual says "The default isolation level for InnoDB is REPEATABLE READ".
Fix
Retry the whole transaction
The manual says "Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again." The application must catch error 1213 (or SQLSTATE 40001), start the transaction again from its first statement, and cap the number of attempts. Retrying only the last statement is wrong, because InnoDB rolled back the entire transaction.
Make the lock order consistent
The manual says "When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock." Put the update sequence in one function or stored routine instead of repeating similar INSERT, UPDATE and DELETE sequences in different places.
Shorten transactions and add indexes
The manual's advice includes: "Keep transactions small and short in duration", "Commit transactions immediately after making a set of related changes", and "do not leave an interactive mysql session open for a long time with an uncommitted transaction." It also says to "Add well-chosen indexes to your tables so that your queries scan fewer index records and set fewer locks. Use EXPLAIN SELECT to determine which indexes the MySQL server regards as the most appropriate for your queries."
Use less locking
The manual says "If you use locking reads (SELECT ... FOR UPDATE or SELECT ... FOR SHARE), try using a lower isolation level such as READ COMMITTED." In its isolation-level description, for READ COMMITTED locking reads, UPDATE and DELETE statements, "InnoDB locks only index records, not the gaps before them". Changing the isolation level changes what concurrent readers see, so it is an application decision and not a quick server fix. It also says "If you can afford to permit a SELECT to return data from an old snapshot, do not add a FOR UPDATE or FOR SHARE clause to it."
Log every deadlock while you investigate
First save the current value:
SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';
Keep the output.
Risk: the next statement changes a server-wide setting. Every deadlock from now on is written to the error log, which grows with the deadlock rate.
SET GLOBAL innodb_print_all_deadlocks = ON;
The manual shows this exact form in its deadlock example and says the variable is dynamic with global scope. Run SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks'; and confirm ON, then read the error log path from step 4.
To undo it: set the variable back to the value you saved, which for a default server is OFF, then check it. If the saved value was ON, the setting was already on before your change: skip the first line and leave it as it was.
SET GLOBAL innodb_print_all_deadlocks = OFF;
SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';
The manual says to disable the option "when you are finished debugging".
Do not turn off deadlock detection to make the errors go away
The manual says that on high-concurrency systems disabling detection and relying on innodb_lock_wait_timeout "may be more efficient", but with detection off a deadlock sits until the timeout, 50 seconds by default, before one transaction is rolled back. That trades a fast error for a long stall. It is a tuning option for specific workloads, not a fix for a lock-order fault.
Example (illustrative): On db01, an order system has tables stock and orders. A checkout request runs UPDATE stock ... WHERE item = 7 and then UPDATE orders ... WHERE id = 90. A refund job runs UPDATE orders ... WHERE id = 90 and then UPDATE stock ... WHERE item = 7. Run at the same moment, each holds the row the other wants. InnoDB detects the cycle, rolls back the smaller transaction and returns ERROR 1213 (40001) to that client. SHOW ENGINE INNODB STATUS lists both statements with the tables stock and orders, one under each transaction. The fix is a single change: the refund job updates stock first, as checkout does. With both jobs locking stock before orders, the second waits for the first to finish rather than deadlocking. The application also retries up to 3 times on error 1213, so a rare collision costs a repeat of the transaction, not a failed order.
Confirm the fix
- The
lock_deadlockscount stops rising over several days, including peak hours. SHOW ENGINE INNODB STATUSno longer shows a newLATEST DETECTED DEADLOCKfor those statements.- The error log, if
innodb_print_all_deadlocksis on, shows no new reports. - Application logs show retries on 1213 falling toward zero.
What to watch afterwards
- Count retries. A retry that always succeeds still costs time, and a rising count means a new code path has the same ordering problem.
- Keep update sequences for related tables in shared code, so a new feature cannot reverse the order.
- Turn
innodb_print_all_deadlocksback off when finished. - Treat error 1205 separately; it points to a long-held lock, not a cycle.
Read next
Related articles you may want next.
- TroubleshootingMySQL ERROR 1205: Lock wait timeout exceeded; try restarting transactionMySQL ERROR 1205 Lock wait timeout exceeded; try restarting transaction: find the blocking session with sys.innodb_lock_waits and fix it safely.Read article
- PerformanceMySQL Replica Lag: Separate Receiver Delay from Apply DelayLocate the receiver or applier bottleneck and state what replica freshness is actually evidenced.Read article
- TroubleshootingMySQL ERROR 1040 "Too many connections": find who holds them before raising max_connectionsMySQL ERROR 1040 Too many connections? Use the reserved admin connection to see who holds them, fix the cause, then raise max_connections safely.Read article
Risks and limits
- These commands have not been run or lab-tested.
SHOW ENGINE INNODB STATUSshows only the latest deadlock; the counter shows how many occurred, not which ones.- The output layout quoted from the manual's example comes from one MySQL 8.4 example and may differ in detail on your server.
SET GLOBALchanges the running server; the manual lists the variable as dynamic with global scope.- MariaDB has its own manual and is not covered.
Sources
- MySQL 8.4 Reference Manual: 17.7.5 Deadlocks in InnoDB — Oracle, no date shown (page shows version 8.4, © 2026), accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.7.5.1 An InnoDB Deadlock Example — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.7.5.2 Deadlock Detection — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.7.5.3 How to Minimize and Handle Deadlocks — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.14 InnoDB Startup Options and System Variables — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.20.5 InnoDB Error Handling — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.15.6 InnoDB INFORMATION_SCHEMA Metrics Table — Oracle, no date shown, accessed 22 November 2026
- MySQL 8.4 Reference Manual: 17.7.2.1 Transaction Isolation Levels — Oracle, no date shown, accessed 22 November 2026
- MySQL Error Reference 8.4: Server Error Message Reference — Oracle, no date shown, accessed 22 November 2026
This article was originally published on .