Symptom
The app returns a random error that's impossible to reproduce in testing:
Deadlock found when trying to get lock; try restarting transaction
ERROR 1213 (40001)
It doesn't happen all the time — only when multiple requests arrive simultaneously or during peak hours. Retrying once usually succeeds.
What a deadlock is
A deadlock occurs when two transactions each hold a lock the other needs, and both are waiting:
Transaction A:
1. Lock row users WHERE id = 1 ← holds this lock
2. Wait for lock on orders id = 100 ← blocked by TX B
Transaction B:
1. Lock row orders WHERE id = 100 ← holds this lock
2. Wait for lock on users id = 1 ← blocked by TX A
Both wait indefinitely → neither can proceed → InnoDB detects the cycle, picks one transaction as the victim and rolls it back (typically the one with fewer resources consumed).
MySQL handles deadlocks automatically — no manual intervention needed. The problem is that the app must know to retry when it receives ERROR 1213.
Reading the deadlock log
Enable innodb_print_all_deadlocks
-- Write all deadlocks to the error log (MySQL 5.6.2+)
SET GLOBAL innodb_print_all_deadlocks = ON;
Or in my.cnf:
[mysqld]
innodb_print_all_deadlocks = ON
View the most recent deadlock
SHOW ENGINE INNODB STATUS\G
Find the LATEST DETECTED DEADLOCK section. A deadlock log looks like this:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-06-18 10:23:45 0x7f1a2b3c4d50
*** (1) TRANSACTION:
TRANSACTION 421938, ACTIVE 0 sec starting index read
MySQL thread id 89, OS thread handle 139..., query id 12301 localhost app
UPDATE orders SET status = 'processing' WHERE id = 100
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 312 page no 4 n bits 72 index PRIMARY of table `mydb`.`orders`
lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 298 page no 3 n bits 64 index PRIMARY of table `mydb`.`users`
lock_mode X locks rec but not gap
*** (2) TRANSACTION:
TRANSACTION 421939, ACTIVE 0 sec starting index read
MySQL thread id 90, OS thread handle 139..., query id 12302 localhost app
UPDATE users SET balance = balance - 50 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 298 page no 3 n bits 64 index PRIMARY of table `mydb`.`users`
lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 312 page no 4 n bits 72 index PRIMARY of table `mydb`.`orders`
lock_mode X locks rec but not gap
*** WE ROLL BACK TRANSACTION (2)
How to read this log
| Section | Meaning |
|---|---|
TRANSACTION (1) |
First transaction in the deadlock |
HOLDS THE LOCK(S) |
Lock this transaction is currently holding |
WAITING FOR THIS LOCK |
Lock this transaction is waiting for |
WE ROLL BACK TRANSACTION (2) |
MySQL chose TX 2 as the victim and rolled it back |
lock_mode X |
Exclusive lock (write lock) |
lock_mode S |
Shared lock (read lock) |
locks rec but not gap |
Row lock, not a gap lock |
From the log above:
- TX1 holds lock on
orders.id=100, waiting for lock onusers.id=1 - TX2 holds lock on
users.id=1, waiting for lock onorders.id=100 - MySQL rolled back TX2
The deadlock occurred because two transactions locked the same 2 tables in opposite order.
Common causes
1. Inconsistent lock order
The most common cause — code in two different places locks the same tables in different orders:
-- Flow A: users first, orders second
BEGIN;
UPDATE users SET balance = balance - 50 WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;
-- Flow B: orders first, users second
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 100;
UPDATE users SET last_order_at = NOW() WHERE id = 1;
COMMIT;
If A and B run concurrently and A has already locked users while B has locked orders → deadlock.
2. Missing index on WHERE columns in UPDATE
-- If orders.user_id has no index
UPDATE orders SET status = 'cancelled'
WHERE user_id = 1 AND created_at < '2026-01-01';
MySQL must scan many rows to find matches, creating more row locks than necessary — increasing the chance of collision with other transactions.
3. Gap locks and phantom reads
InnoDB uses gap locks (locking the spaces between index values) to enforce REPEATABLE READ. Gap locks can deadlock even when two transactions touch completely different rows:
-- TX A
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- Creates gap lock for the entire status = 'pending' range
-- TX B simultaneously
INSERT INTO orders (status, ...) VALUES ('pending', ...);
-- Also needs a gap lock → conflict
4. Foreign key constraints
-- TX A: updating parent
UPDATE users SET id = 999 WHERE id = 1;
-- MySQL automatically locks rows in orders where user_id = 1 (FK check)
-- TX B updating orders
UPDATE orders SET amount = 200 WHERE user_id = 1;
-- Conflicts with TX A's FK check lock
MySQL automatically locks related rows when foreign keys are involved — this creates unexpected deadlocks that are hard to trace.
5 ways to reduce deadlock probability
1. Enforce a consistent lock order across the entire app
The simplest rule: always lock tables in the same order everywhere.
// Convention: users → orders → payments (always in this order)
async function processPayment(userId: number, orderId: number) {
await db.transaction(async (trx) => {
// Correct order: users first
const user = await trx("users").where({ id: userId }).forUpdate().first();
const order = await trx("orders").where({ id: orderId }).forUpdate().first();
// process...
});
}
// Never do this in reverse anywhere in the codebase
2. Keep transactions short and fast
// Wrong: calling an external API inside a transaction → holds locks for seconds
await db.transaction(async (trx) => {
const order = await trx("orders").where({ id }).forUpdate().first();
await sendEmailViaThirdPartyApi(order); // can take 2-5 seconds
await trx("orders").where({ id }).update({ email_sent: true });
});
// Correct: do heavy work before opening the transaction
const order = await db("orders").where({ id }).first();
await sendEmailViaThirdPartyApi(order);
await db.transaction(async (trx) => {
await trx("orders").where({ id }).update({ email_sent: true });
});
3. Add indexes on WHERE columns used in UPDATE/DELETE
-- Before running UPDATE with WHERE user_id, check the plan
EXPLAIN UPDATE orders SET status = 'cancelled' WHERE user_id = 1;
-- If type = ALL or rows is large → add an index
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
Indexes allow MySQL to lock fewer rows, reducing the area of conflict.
4. Use READ COMMITTED instead of REPEATABLE READ when appropriate
-- REPEATABLE READ (default): uses gap locks → more prone to deadlocks
-- READ COMMITTED: no gap locks → fewer deadlocks
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ...
COMMIT;
READ COMMITTED eliminates gap locks, significantly reducing deadlocks from concurrent INSERTs. Trade-off: doesn't prevent phantom reads — appropriate for most typical OLTP use cases, not ideal when exact counts within a transaction are required.
5. Retry on ERROR 1213
Deadlocks can't be eliminated completely — only reduced. The app must retry:
async function withDeadlockRetry<T>(
fn: () => Promise<T>,
maxRetries = 3
): Promise<T> {
for (let attempt = 1; attempt <= maxRetries; attempt++) {
try {
return await fn();
} catch (err: any) {
const isDeadlock =
err.code === "ER_LOCK_DEADLOCK" ||
err.message?.includes("Deadlock found");
if (isDeadlock && attempt < maxRetries) {
await new Promise((r) => setTimeout(r, attempt * 50));
continue;
}
throw err;
}
}
throw new Error("Max retries exceeded");
}
// Usage
await withDeadlockRetry(() =>
db.transaction(async (trx) => {
// logic...
})
);
Checklist when you hit a deadlock
- Read the log:
SHOW ENGINE INNODB STATUS\G— identify which rows each transaction was locking - Check lock order: do two code paths lock the same tables in opposite orders
- Check indexes: run
EXPLAINon yourUPDATE/DELETEstatements — anytype=ALL - Check isolation level: are you using
REPEATABLE READwith many concurrent INSERTs - Add retry logic: does the app handle
ERROR 1213or let the exception bubble up to the user