SQL Error: Deadlock Detected (Fix + Examples)

Getting the "deadlock detected" error in SQL? This guide explains what causes this error and how to fix it with practical examples.

What Causes This Error?

The deadlock detected error (40P01) occurs when:

• Two transactions waiting for each other's locks
• Inconsistent lock ordering across transactions
• Long-running transactions holding locks
• High concurrency on same rows

How to Fix It

1. Always access tables in consistent order.
2. Keep transactions short.
3. Use SELECT FOR UPDATE NOWAIT to fail fast.
4. Implement retry logic in your application.
5. Consider optimistic locking with version columns.

Example: Wrong vs Correct

❌ Code That Causes the Error

-- Transaction 1: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction 1: UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Transaction 2 (concurrent): UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Transaction 2: UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- DEADLOCK!

✅ Corrected Code

-- Always update accounts in consistent order (by id):
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Both transactions use same ordering - no deadlock

Quick Checklist

• [ ] Verify column/table names are spelled correctly
• [ ] Check data types match expected values
• [ ] Review query syntax for missing keywords
• [ ] Ensure referenced tables/columns exist

Related SQL Errors

If you're troubleshooting SQL errors, you might also encounter: