Fixing MySQL Deadlocks — A Simple Explanation for Beginners

Deadlocks can look terrifying, especially when you're new to databases.
One moment everything is fine… the next, your application throws this:

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

Relax.
Even large companies like Facebook, Uber, and Shopify deal with deadlocks daily.
Deadlocks aren’t a sign that MySQL is broken — they're just the database saying:

“Hey, two queries are fighting over the same data. I had to stop one.”

Let’s break this down clearly and gently.

---

1. What Is a Deadlock? (Simple Explanation)

Imagine two people trying to walk through a narrow door at the same time.

• Person A blocks Person B

• Person B blocks Person A

• Neither can move

• Someone must step back

That’s a deadlock.

In MySQL:
Two (or more) transactions are trying to lock rows in an order that causes a conflict.

MySQL chooses one of them to cancel.
Your application then sees the deadlock error.

---

2. Why Deadlocks Happen (Beginner Perspective)

Deadlocks happen because of the way your application reads and updates data.

Here are the most common triggers:

🔥 Reason #1: Updating rows in different order

Example:

• User A updates row 10 then row 20

• User B updates row 20 then row 10

Boom — deadlock.

🔥 Reason #2: Missing indexes

Without indexes, MySQL scans more rows than needed → larger locks → more fighting between queries.

🔥 Reason #3: Long-running transactions

Keeping a transaction open too long increases the chance of collision.

🔥 Reason #4: Two transactions updating the same table

Especially with UPDATE or DELETE.

🔥 Reason #5: Foreign keys and cascading updates

These can silently lock multiple tables.

---

3. How to See What Caused the Deadlock

This is the most important part.

Run:

SHOW ENGINE INNODB STATUS\G;

This shows:

• Which transactions were fighting

• Which queries were running

• Which lock was requested

• Which transaction MySQL killed

Don’t worry if the output looks long — we only care about these parts:

• LATEST DETECTED DEADLOCK

• TRANSACTION

• WAITING FOR

• HOLDS THE LOCK

These tell you exactly what happened.

---

Example Deadlock Output (Simplified)

LATEST DETECTED DEADLOCK
------------------------
Transaction A: UPDATE orders SET status='paid' WHERE id=10;
Transaction B: UPDATE orders SET status='shipped' WHERE id=10;

This tells you:

• Both queries tried to update the same row

• They conflicted

• MySQL stopped one

• 
  

---

4. How to Fix Deadlocks (Step-by-Step)

Now let’s solve them.

---

Step 1: Add Missing Indexes

Deadlocks often disappear after adding the correct indexes because fewer rows are locked.

Example:

CREATE INDEX idx_orders_id ON orders(id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_products_category ON products(category_id);

If you see Using where + large scans in EXPLAIN, you need an index.

---

Step 2: Keep Transactions Short

Bad example:

BEGIN;
SELECT * FROM orders;
-- do something in the application, maybe slow or waiting
UPDATE orders SET ...
COMMIT;

👇 Better:

• Only start a transaction right before your updates

• Commit as fast as possible

• Never run “slow logic” inside a transaction

• 
  

---

Step 3: Update Rows in a Consistent Order

Always update in the same order in your application.

Bad:

User A updates 10 → 20
User B updates 20 → 10

Good:

Both always update 10 → 20

Order matters!

---

Step 4: Reduce Concurrency for Certain Queries

If many writes hit the same table:

• Use queues

• Batch updates

• Use background jobs

Even big companies use this trick.

---

Step 5: Use SELECT … FOR UPDATE Carefully

This locks rows.
If used incorrectly, it creates deadlocks.

Only lock what you absolutely must lock.

---

Step 6: Retry the Transaction

This is actually the recommended approach.

When deadlock occurs, retry the operation:

Pseudocode:

for i in 1..3:
    try:
        begin transaction
        run update
        commit
        break
    catch deadlock:
        wait a bit and try again

Even MySQL’s documentation recommends this.

---

5. How to Prevent Deadlocks Permanently

Here’s your long-term strategy:

✔ Add the correct indexes
✔ Avoid locking more rows than necessary
✔ Keep transactions short
✔ Always update rows in consistent order
✔ Retry deadlocks automatically in applications
✔ Audit slow queries regularly
✔ Archive old data so tables stay small

Deadlocks aren't bugs — they’re signals that your application logic needs tuning.

---

6. Real World Example (Before & After)

❌ Before

• 150 deadlocks/day

• Checkout process failing

• Orders stuck

• Angry users

• 
  

✔ After Changes

• Added 2 indexes

• Reordered UPDATE logic

• Reduced transaction time

Result:
Deadlocks dropped to near zero, and the server became stable.

---

Conclusion

Deadlocks are one of the most confusing MySQL errors for beginners, but now you understand:

• what a deadlock is

• why it happens

• how to read the MySQL deadlock report

• how to fix it

• how to prevent future deadlocks