Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

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

Technical article
Troubleshooting

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

  1. The lock_deadlocks count stops rising over several days, including peak hours.
  2. SHOW ENGINE INNODB STATUS no longer shows a new LATEST DETECTED DEADLOCK for those statements.
  3. The error log, if innodb_print_all_deadlocks is on, shows no new reports.
  4. 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_deadlocks back off when finished.
  • Treat error 1205 separately; it points to a long-held lock, not a cycle.

Risks and limits

  • These commands have not been run or lab-tested.
  • SHOW ENGINE INNODB STATUS shows 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 GLOBAL changes the running server; the manual lists the variable as dynamic with global scope.
  • MariaDB has its own manual and is not covered.

Sources