Deadlocks: what they are and how to avoid them
45 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)Two transfers run together: one locks account A then wants B, the other locks B then wants A. Both wait forever. What does the database do, and how do you make this rare?
Two transactions waiting for each other
A deadlock happens when two transactions each hold a lock that the other one needs, so neither can continue. It needs two transactions that lock the same rows in opposite orders.
Two transfers cross: Asha pays Ravi while Ravi pays Asha. Each one locks an account, then waits for the account the other already holds.
Example 1 · Two transfers in opposite directions
-- Setup, once, in either session:DROP TABLE IF EXISTS bank_accounts;CREATE TABLE bank_accounts ( acc_no INT PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL, CONSTRAINT balance_not_negative CHECK (balance >= 0));INSERT INTO bank_accounts (acc_no, holder, balance) VALUES (101, 'Asha Rao', 5000.00), (102, 'Ravi Kumar', 1200.00);
-- Step 1 · Session A: Asha sends Rs. 500 to Ravi: 101 firstSTART TRANSACTION;UPDATE bank_accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Step 2 · Session B: Ravi sends Rs. 300 to Asha: 102 firstSTART TRANSACTION;UPDATE bank_accounts SET balance = balance - 300 WHERE acc_no = 102;
-- Step 3 · Session A: A needs 102, which B holdsUPDATE bank_accounts SET balance = balance + 500 WHERE acc_no = 102;
-- Step 4 · Session B: B needs 101, which A holds: a circleUPDATE bank_accounts SET balance = balance + 300 WHERE acc_no = 101;
-- Step 5 · Session B: B's whole transaction was rolled backSELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;
-- Step 6 · Session ACOMMIT;SELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;-- Step 3 · Session A (waited until step 4) -- Step 4 · Session B ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction -- Step 5 · Session B +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 5000.00 | | 102 | 1200.00 | +--------+---------+ -- Step 6 · Session A +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 4500.00 | | 102 | 1700.00 | +--------+---------+
What this code does
- In step 4 InnoDB finds the circle at once and picks B as the victim (it can pick either). B gets error 1213, and A's waiting UPDATE from step 3 finishes.
- Step 5 shows that B's debit from step 2 was undone too: Ravi is back at 1200.00. After A commits, only Asha's transfer has happened; B must run its whole transfer again.
In general, transaction A locks row 1 and then asks for row 2; at the same moment, transaction B has locked row 2 and asks for row 1. A waits for B and B waits for A.
Bank transfers are the textbook case. Asha sends money to Ravi (lock 101, then 102) while Ravi sends money to Asha (lock 102, then 101). Each UPDATE takes a row lock that lasts until its transaction ends, so the two transfers block each other. Deadlocks are normal in busy databases and are not a sign of a broken server. What matters is that the application expects them, retries, and is written so that they are rare.
How InnoDB detects and breaks a deadlock
The deadlock victim is the transaction InnoDB rolls back to break a deadlock. It is usually the one that changed the fewest rows, and it receives error 1213. The other transaction gets its lock and carries on.
InnoDB keeps track of which transaction waits for which. When a new wait closes a circle, it finds the deadlock at once, with no timeout.
SELECT @@innodb_deadlock_detect AS deadlock_detect, @@innodb_lock_wait_timeout AS lock_wait_timeout, @@innodb_rollback_on_timeout AS rollback_on_timeout;+-----------------+-------------------+---------------------+ | deadlock_detect | lock_wait_timeout | rollback_on_timeout | +-----------------+-------------------+---------------------+ | 1 | 50 | 0 | +-----------------+-------------------+---------------------+
It then picks a victim, rolls it back completely and sends it error 1213. This detection is on by default (innodb_deadlock_detect = ON). To see what happened, SHOW ENGINE INNODB STATUS prints a section called LATEST DETECTED DEADLOCK with both transactions and their locks. Setting innodb_print_all_deadlocks = ON writes every deadlock to the server's error log.
Deadlocks do not only come from two different rows. Two transactions that both read the same row with FOR SHARE and then both try to update it also deadlock: each holds a shared lock that blocks the other's write.
Example 2 · FOR SHARE, then UPDATE: a deadlock on one row
-- Step 1 · Session A: checkout 1 checks the iPhone stockSTART TRANSACTION;SELECT stock FROM products WHERE product_id = 3 FOR SHARE;
-- Step 2 · Session B: checkout 2 checks the same stockSTART TRANSACTION;SELECT stock FROM products WHERE product_id = 3 FOR SHARE;
-- Step 3 · Session AUPDATE products SET stock = stock - 1 WHERE product_id = 3;
-- Step 4 · Session BUPDATE products SET stock = stock - 1 WHERE product_id = 3;
-- Step 5 · Session ACOMMIT;SELECT stock FROM products WHERE product_id = 3;-- Step 1 · Session A +-------+ | stock | +-------+ | 8 | +-------+ -- Step 2 · Session B +-------+ | stock | +-------+ | 8 | +-------+ -- Step 3 · Session A (waited until step 4) -- Step 4 · Session B ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction -- Step 5 · Session A +-------+ | stock | +-------+ | 7 | +-------+
What this code does
- Both shared locks can exist together, but an UPDATE needs the row to itself. A waits for B's shared lock, then B waits for A's: a deadlock on a single row.
- Reading with
FOR UPDATEinstead makes B wait at its read in step 2, and no deadlock can form. Read with the lock you will need for the write.
Error 1213 and lock wait timeouts
Error 1213 means "you were the deadlock victim": MySQL rolled back your whole transaction. Run the whole transaction again from START TRANSACTION, usually after a short pause and at most a few times.
A deadlock victim sees error 1213. A transaction that only waited too long sees error 1205. The two need different reactions.
Example 3 · A lock wait timeout keeps the transaction open
-- Setup, once, in either session:DROP TABLE IF EXISTS bank_accounts;CREATE TABLE bank_accounts ( acc_no INT PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL, CONSTRAINT balance_not_negative CHECK (balance >= 0));INSERT INTO bank_accounts (acc_no, holder, balance) VALUES (101, 'Asha Rao', 5000.00), (102, 'Ravi Kumar', 1200.00);
-- Step 1 · Session B: a short timeout, only for this demoSET SESSION innodb_lock_wait_timeout = 3;
-- Step 2 · Session A: a slow transaction holds 101START TRANSACTION;UPDATE bank_accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Step 3 · Session BSTART TRANSACTION;UPDATE bank_accounts SET balance = balance + 100 WHERE acc_no = 102;
-- Step 4 · Session B: waits 3 seconds for 101, then gives upUPDATE bank_accounts SET balance = balance - 100 WHERE acc_no = 101;
-- Step 5 · Session B: B's first change is still pendingSELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;ROLLBACK;
-- Step 6 · Session AROLLBACK;-- Step 4 · Session B ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- Step 5 · Session B +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 5000.00 | | 102 | 1300.00 | +--------+---------+
What this code does
- There is no circle here, only a long wait, so after 3 seconds B gets error 1205 instead of 1213. The default wait is 50 seconds.
- Unlike a deadlock, only the statement that timed out is undone: step 5 still shows B's +100 on account 102, so the app must send ROLLBACK itself.
After error 1213, retrying only the failed statement is wrong, because the earlier statements were undone too. Error 1205, lock wait timeout, is different. There is no circle: another transaction simply held the lock longer than innodb_lock_wait_timeout seconds (50 by default). By default MySQL rolls back only the statement that waited, and your transaction stays open with its earlier changes, so the application should roll back and then retry or report the problem.
Both errors say "try restarting transaction". Both are signs to keep transactions short: never wait for a user, an email or a payment page while holding locks.
Avoiding deadlocks with a fixed lock order
A fixed lock order means every transaction locks rows in the same order, for example by ascending account number. Then no circle can form: the later transaction simply waits in line.
The two transfers deadlock only because they lock the accounts in opposite orders. Make both lock the lower account number first.
Example 4 · The same two transfers with a fixed lock order
-- Setup, once, in either session:DROP TABLE IF EXISTS bank_accounts;CREATE TABLE bank_accounts ( acc_no INT PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL, CONSTRAINT balance_not_negative CHECK (balance >= 0));INSERT INTO bank_accounts (acc_no, holder, balance) VALUES (101, 'Asha Rao', 5000.00), (102, 'Ravi Kumar', 1200.00);
-- Step 1 · Session A: Asha to Ravi: lock both, lower acc_no firstSTART TRANSACTION;SELECT acc_no, balance FROM bank_accountsWHERE acc_no IN (101, 102) ORDER BY acc_no FOR UPDATE;
-- Step 2 · Session B: Ravi to Asha: the same lock orderSTART TRANSACTION;SELECT acc_no, balance FROM bank_accountsWHERE acc_no IN (101, 102) ORDER BY acc_no FOR UPDATE;
-- Step 3 · Session AUPDATE bank_accounts SET balance = balance - 500 WHERE acc_no = 101;UPDATE bank_accounts SET balance = balance + 500 WHERE acc_no = 102;COMMIT;
-- Step 4 · Session BUPDATE bank_accounts SET balance = balance - 300 WHERE acc_no = 102;UPDATE bank_accounts SET balance = balance + 300 WHERE acc_no = 101;COMMIT;SELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;-- Step 1 · Session A +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 5000.00 | | 102 | 1200.00 | +--------+---------+ -- Step 2 · Session B (waited until step 3) +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 4500.00 | | 102 | 1700.00 | +--------+---------+ -- Step 4 · Session B +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 4800.00 | | 102 | 1400.00 | +--------+---------+
What this code does
- Both transfers lock 101 first. B waits in step 2 until A commits, and then reads the new balances: a queue, not a circle.
- Both transfers succeed: 5000 - 500 + 300 = 4800 and 1200 + 500 - 300 = 1400. The total is still Rs. 6,200.
For a transfer, lock both accounts at the start in one statement: SELECT ... WHERE acc_no IN (101, 102) ORDER BY acc_no FOR UPDATE. The direction of the transfer no longer changes the lock order.
More habits that make deadlocks rare: touch tables in the same order everywhere, for example products before orders; keep transactions short and do slow work, such as calling a payment service, before starting them; index the columns in WHERE, so locking statements lock only the rows they need; use FOR UPDATE, not FOR SHARE, when you read a row in order to change it.
Fix the error
Lock the accounts in a fixed order
Two transfers run this at the same time and sometimes deadlock. Pick the line that fixes it.
Lock both accounts at the start, whatever the transfer direction.
Error
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Next step
Check your understanding
12 quick questions