The 2 AM Nightmare
The phone vibrates incessantly. Slack is flooded with continuous 500 error notifications. Sentry is overflowing with logs: Deadlock found when trying to get lock; try restarting transaction. If you are operating a Backend system, this is a red alert signaling that your database is “suffocating.”
I once managed a MySQL 8.0 system with a database of about 50GB, serving more than 2,000 orders per minute. There were times when seemingly simple Update statements caused the entire workflow to freeze. A deadlock is not a syntax error. It is a state of resource conflict where transactions are in a tug-of-war. Instead of panic-restarting the service, knowing how to read the logs will help you solve the root cause of the problem.
In Plain English: What is a Deadlock?
Imagine a “you wait for me, I wait for you” situation. Two transactions are running in parallel and hit a stalemate:
- Transaction A: Holds a lock on row 1 and wants to update row 2.
- Transaction B: Holds a lock on row 2 and wants to update row 1.
Neither side is willing to release their lock, and they wait for each other indefinitely. At this point, the InnoDB “referee” steps in. It proactively cancels one transaction (usually the one with the least changes) to release resources. That is why you see the restarting transaction error appearing in the application logs.
How to “Diagnose” Using InnoDB Status
When an error occurs, the first command I always type in the terminal is:
SHOW ENGINE INNODB STATUS;This command returns a very long text file. Don’t be confused; scroll down immediately to the LATEST DETECTED DEADLOCK section. This is the crime scene, complete with all the clues.
Analyzing a Real-world Log
Below is a log snippet from a real bug I once handled:
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec
UPDATE orders SET status = 'processing' WHERE id = 10;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 index PRIMARY of table `shop`.`orders` trx id 12345 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec
UPDATE orders SET status = 'shipped' WHERE id = 5;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 50 index PRIMARY of table `shop`.`orders` trx id 12346 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 index PRIMARY of table `shop`.`orders` trx id 12346 lock_mode X locks rec but not gap waitingWhat can we read from this?
- Transaction (1) is waiting for a lock on the row with
id = 10. - Transaction (2) is holding (HOLDS) the exact lock that Transaction (1) needs.
- Conversely, (2) is also waiting for another lock from (1).
The cause is usually an inconsistent data update order. One process flow goes from 10 to 5, while the other goes backward from 5 to 10.
5 “Battle-Hardened” Tips to Stop Deadlocks
After many sleepless nights fixing bugs, I’ve distilled 5 rules to help your database run smoothly even under high load.
1. Always Update in a Fixed Order
This is the golden rule. If you need to update multiple records (e.g., deducting stock for 3 products), always sort them by ID in ascending order before executing the SQL statements.
# Safe code: Always sort IDs before updating
ids = sorted([10, 5, 8])
for id in ids:
cursor.execute(f"UPDATE products SET stock = stock - 1 WHERE id = {id}")When both transactions want to update IDs 5 and 10, they will both compete for the ID 5 lock first. The winner and loser will be clear, and there will be no more cross-waiting.
2. Shorten Transaction Execution Time
Never cram heavy logic, third-party API calls, or email sending inside a Database Transaction. The longer the transaction lasts, the longer the locks are held. This increases the probability of collisions with other threads many times over.
3. Optimize Indexes to Avoid Table Scans
If an UPDATE statement lacks an Index, MySQL must perform a full table scan. Instead of locking one row, it will lock an entire range of data (Gap Lock). Ensure every data modification statement runs based on a Primary Key or a Unique Index.
4. Lower the Isolation Level If Possible
By default, MySQL uses REPEATABLE READ with a very strict Gap Locking mechanism. If your application is not overly strict about perfect consistency, try switching to READ COMMITTED:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;In practice, this change helped me reduce Deadlock errors related to Gap Locks by up to 80% on e-commerce systems.
5. Implement a Retry Mechanism at the Application Level
Deadlocks are hard to avoid 100% of the time when a system reaches thousands of requests per second. Instead of returning an error to the user, write a small piece of logic to automatically retry the transaction.
max_retries = 3
for i in range(max_retries):
try:
# Execute DB logic here
db.commit()
break
except Exception as e:
if "Deadlock found" in str(e):
time.sleep(0.1) # Rest for 100ms then retry
continue
raise eConclusion
Handling deadlocks is not just about fixing a SQL query. It requires you to understand how data is accessed and contested. Remember: read the logs with SHOW ENGINE INNODB STATUS, standardize the update order, and always have a retry plan. Hopefully, this experience helps you feel more confident when facing unexpected system freezes.
