How to Resolve Deadlock in MySQL: Causes and Fixes

A deadlock in MySQL happens when two or more transactions wait for each other to release locks, causing a standstill. To resolve it, you can analyze the deadlock using SHOW ENGINE INNODB STATUS, then fix your queries by ensuring consistent locking order or using shorter transactions with SELECT ... FOR UPDATE locks.

Why This Happens

A deadlock occurs when two transactions each hold a lock on a resource the other needs, and neither can proceed. This creates a cycle of waiting that MySQL detects and breaks by rolling back one transaction.

sql

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction 1 holds lock on account 1

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Transaction 2 holds lock on account 2

-- Now Transaction 1 tries to update account 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 2 tries to update account 1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Output

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

The Fix

To fix deadlocks, ensure all transactions lock resources in the same order. Also, keep transactions short and use explicit locking with SELECT ... FOR UPDATE to avoid surprises.

sql

START TRANSACTION;
-- Lock accounts in consistent order
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Output

Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
COMMIT

Prevention

• Always access tables and rows in the same order in all transactions.
• Keep transactions short to reduce lock time.
• Use SELECT ... FOR UPDATE to lock rows explicitly before updating.
• Handle deadlock errors by retrying the transaction in your application.

Related Errors

Other common locking issues include lock wait timeout, which happens when a transaction waits too long for a lock, and lost updates caused by missing locks. Use proper transaction isolation and locking to avoid these.

Key Takeaways

Deadlocks happen when transactions wait on each other's locks creating a cycle.

Fix deadlocks by locking resources in a consistent order and keeping transactions short.

Use SELECT ... FOR UPDATE to lock rows explicitly before updating.

Always handle deadlock errors by retrying transactions in your application.

Prevent related locking issues by using proper transaction isolation and locking strategies.