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

An ordinary UPDATE failed with ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction, and MySQL threw away everything the transaction had done so far. Two transactions had taken the same rows in opposite orders. Here's how to see that for yourself, and the small change that stops it.

What I Was Doing

I had two sessions changing the stock of two products, each in a transaction, on MySQL 9.7.4 in Docker. The first one took product 3 and then 4, and the second took 4 and then 3:

mysql: terminal 1

START TRANSACTION;
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3;

mysql: terminal 2

START TRANSACTION;
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 4;

mysql: terminal 1

UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 4;

That one didn't come back, since terminal 2 holds row 4. Then:

mysql: terminal 2

UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3;

What I Expected

Terminal 2 would wait its turn too, and everything would finish in the end.

What I Got

Output (terminal 2)

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

It failed at once, and terminal 1's waiting UPDATE finished in the same moment:

Output (terminal 1)

Query OK, 1 row affected (2.059 sec)
Rows matched: 1  Changed: 1  Warnings: 0

What I Checked

What terminal 2 still had. Its first UPDATE had worked. Was it still there?

mysql: terminal 2

SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4);

Output

+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
|  3 |             50 |
|  4 |             50 |
+----+----------------+
2 rows in set (0.003 sec)

Both rows are at 50, so its change to row 4 was gone as well. The error handling page confirms it: "A transaction deadlock causes InnoDB to roll back the entire transaction."

MySQL's own report. SHOW ENGINE INNODB STATUS\G keeps the latest deadlock. With the row dumps cut down, it says:

Output (the deadlock section)

LATEST DETECTED DEADLOCK
------------------------
2026-11-05 05:26:46 281472208334528
*** (1) TRANSACTION:
TRANSACTION 4564, ACTIVE 3 sec starting index read
mysql tables in use 4, locked 4
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 111, OS thread handle 281472162709184, query id 1009 localhost inventory_app updating
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 4

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 88 index PRIMARY of table `inventory`.`products` trx id 4564 lock_mode X locks rec but not gap
 0: len 4; hex 80000003; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 88 index PRIMARY of table `inventory`.`products` trx id 4564 lock_mode X locks rec but not gap waiting
 0: len 4; hex 80000004; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 4565, ACTIVE 3 sec starting index read
mysql tables in use 4, locked 4
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 109, OS thread handle 281472163827392, query id 1010 localhost inventory_app updating
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 88 index PRIMARY of table `inventory`.`products` trx id 4565 lock_mode X locks rec but not gap
 0: len 4; hex 80000004; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 88 index PRIMARY of table `inventory`.`products` trx id 4565 lock_mode X locks rec but not gap waiting
 0: len 4; hex 80000003; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------

Transaction 1 holds row 3 (hex 80000003) and wants 4. Transaction 2 holds 4 and wants 3.

Why It Happens

Each transaction was waiting for something only the other could release, so neither could ever finish. The deadlocks page defines it: "A deadlock is a situation in which multiple transactions are unable to proceed because each transaction holds a lock that is needed by another one."

MySQL spots the cycle right away and picks a loser. The detection page says "InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock." Nothing was wrong with either statement. The problem is the order they ran in.

The Fix

Take the rows in the same order in both transactions. Lowest ID first is an easy rule. Now both start with row 3:

mysql: terminal 1

START TRANSACTION;
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3;

mysql: terminal 2

START TRANSACTION;
UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3;

Terminal 2 waits, holding nothing, so terminal 1 can finish:

mysql: terminal 1

UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 4;
COMMIT;

Then terminal 2 carries on with row 4 and commits.

Also, be ready for one that still gets through. The handling page says: "Always be prepared to re-issue a transaction if it fails due to deadlock." The error message says the same: run the whole transaction again from the start.

After the Fix

Output (terminal 2, from its first UPDATE)

mysql> UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 3;
Query OK, 1 row affected (2.141 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE products SET QuantityOnHand = QuantityOnHand - 1 WHERE Id = 4;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.004 sec)

mysql> SELECT Id, QuantityOnHand FROM products WHERE Id IN (3, 4);
+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
|  3 |             48 |
|  4 |             48 |
+----+----------------+
2 rows in set (0.001 sec)

A wait of 2.1 seconds instead of an error, and both transactions counted: the rows went from 50 to 48.

Common Mistakes

Mistake	What happens	Do this instead
Retrying only the failed statement	The earlier statements are gone too	Run the whole transaction again
Committing after the error	The transaction is already rolled back, so nothing is saved	Start again
Fixing the order in one code path only	The other path still collides	Use one rule everywhere
Raising innodb_lock_wait_timeout	A deadlock doesn't wait for it	Fix the order

Troubleshooting

Symptom	Likely cause	Fix
Deadlock with rows in the same order	A shared lock from a foreign key	Update the parent first
Deadlock on an INSERT	Two gap locks	The fix
Deadlock after FOR SHARE	Two sessions upgrading a shared lock	Use FOR UPDATE
The same error from .NET	The same cause	The EF Core version

FAQ

Does this mean my data is damaged?

No. MySQL rolled back one transaction cleanly, and the other one finished. Deadlocks are an expected event: the error handling page says "Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying."

Which transaction loses?

The page says: "InnoDB 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."

Conclusion

ERROR 1213 means two transactions were each waiting for the other, and MySQL rolled one back whole. Read the report in SHOW ENGINE INNODB STATUS to see the rows involved, take rows in the same order everywhere, and retry the whole transaction when one still happens.

Additional Resources

• Deadlocks in InnoDB — what they are.
• How to Minimize and Handle Deadlocks — what to change.

Related

• How to Avoid Deadlocks in MySQL by Locking Rows in the Same Order
• How to See the Last Deadlock in MySQL
• Fixed: "MySqlException: Deadlock found when trying to get lock" in EF Core