Avoiding Deadlocks in MySQL - Explained

Avoiding Deadlocks in MySQL: A Practical Guide for Real-Time Systems

If you've ever seen ERROR 1213 (40001): Deadlock found when trying to get lock in a production log at 2am, this post is for you. Deadlocks are one of those MySQL problems that seem rare until your system has real concurrent traffic — then they show up weekly.

📘 What Is a Deadlock?

A deadlock happens when two (or more) transactions each hold a lock the other one needs, and neither can proceed. MySQL's InnoDB engine detects this automatically and kills one of the transactions (the "victim") to break the cycle — but that still means one of your transactions failed, and your application needs to handle that gracefully.

🧪 A Simple Example

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction B (running at the same time)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

If A locks row 1 then waits for row 2, while B locks row 2 then waits for row 1 — that's a deadlock. Notice the accounts are updated in opposite order in each transaction. This is the single most common real-world cause.

🛠️ How to Prevent Deadlocks

1. Always access rows in the same order

The fix for the example above: always update the lower ID first, in every transaction, everywhere in your codebase.

-- Both transactions should follow this order:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

2. Keep transactions short

The longer a transaction holds a lock, the more chances another transaction has to collide with it. Don't do slow application logic (API calls, file I/O) between your START TRANSACTION and COMMIT.

3. Use appropriate indexes

InnoDB locks index entries, not just rows. A missing index on a WHERE clause column can cause MySQL to lock far more rows than necessary (a full table scan lock), dramatically increasing deadlock risk.

-- Check if your query is using an index:
EXPLAIN UPDATE accounts SET balance = balance - 100 WHERE id = 1;

4. Lower your isolation level if you can

MySQL's default isolation level, REPEATABLE READ, takes more locks than READ COMMITTED. If your application logic tolerates it, switching to READ COMMITTED reduces gap locks and lowers deadlock frequency:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

🔁 Implement Retry Logic

Even with all the prevention above, deadlocks can still happen under real concurrent load — the correct application-level response is to retry the transaction, not treat it as a fatal error:

import time
import pymysql

def run_with_retry(cursor, query, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(query)
            return
        except pymysql.err.OperationalError as e:
            if e.args[0] == 1213 and attempt < max_retries - 1:
                time.sleep(0.1 * (attempt + 1))  # simple backoff
                continue
            raise

🔍 Diagnosing Deadlocks After They Happen

MySQL keeps a record of the last deadlock — this is your best debugging tool:

SHOW ENGINE INNODB STATUS;
-- Look for the "LATEST DETECTED DEADLOCK" section

⚠️ Common Mistakes

• Updating rows in different orders across different parts of the application (the #1 cause)
• Long-running transactions that mix database calls with slow external API calls
• Missing indexes causing broader-than-necessary locks
• Treating a deadlock as a bug to "fix once" rather than a normal condition to retry

🔗 Related Posts

• Indexing in SQL Explained: How to Speed Up Slow Queries
• PostgreSQL vs MySQL: Key Differences Every Developer Should Know