A Real-World Scenario: The Midnight Nightmare
2:00 AM. My phone vibrated violently on the nightstand with PagerDuty alerts. The monitoring dashboard was glowing red: the checkout API had ground to a halt, the HTTP 500 error rate spiked to 35%, and the database connection pool (capped at 200 connections) was completely maxed out.
Scrambling to open my laptop and SSH into the production server, application logs were spewing out a painfully familiar exception:
Deadlock found when trying to get lock; try restarting transaction (errno: 1213)Many engineers encountering this error for the first time assume MySQL crashed or that a SQL query has syntax errors. In reality, that is not the case at all. A deadlock occurs when two or more transactions hold locks on different resources while simultaneously waiting for each other’s resources in a circular wait condition.
To put it simply: Transaction 1 holds record A and waits for record B. Meanwhile, Transaction 2 holds record B and waits for record A. Both sides are locked in a standoff, neither willing to yield. To keep the system from hanging indefinitely, the InnoDB engine must step in. It chooses to roll back the transaction with the lower undo cost—meaning the one with fewer rows modified, based on undo log volume.
Back when our team’s orders and wallets tables reached 15 million records, the system handled roughly 1,800 requests/second during flash sales. With concurrent writes surging, two code execution paths acquiring locks out of order by just a millisecond would instantly bring the database to a grinding halt.
Rapid On-Site Diagnostics
While the incident is actively occurring, the first tool you should reach for is InnoDB’s internal status report.
Log in to the MySQL server via terminal:
mysql -u root -pRun the following command immediately to inspect the current state:
SHOW ENGINE INNODB STATUS\GScroll down to the LATEST DETECTED DEADLOCK section. Here, InnoDB records a detailed post-mortem of the most recent deadlock incident:
- (1) TRANSACTION & (2) TRANSACTION: Thread ID, active duration, number of locked rows, and undo log entries.
- WAITING FOR THIS LOCK TO BE GRANTED: The SQL statement waiting for execution and the requested lock mode (typically
lock_mode X waiting– Exclusive lock). - HOLDS THE LOCK(S): The resources currently held by the competing transaction.
- WE ROLLBACK TRANSACTION: InnoDB’s final verdict on which transaction was aborted to allow the other to proceed.
A fatal downside: SHOW ENGINE INNODB STATUS retains only the single most recent deadlock event. If your system encounters a storm of dozens of deadlocks per minute, newer events continuously overwrite older ones. By the time you investigate the next morning, the initial root-cause trace is already gone.
Configuring Comprehensive Deadlock Logging
To avoid missing any conflict on production, we must configure MySQL to record every deadlock event directly into the standard error log file.
1. Enable at Runtime (No Restart Required)
Run this command directly on production without disrupting live user traffic:
SET GLOBAL innodb_print_all_deadlocks = ON;Verify that the system variable has updated successfully:
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';2. Persist the Configuration in my.cnf
Open the MySQL configuration file with root privileges:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnfAdd the following two lines under the [mysqld] block:
[mysqld]
# Enable logging all deadlocks to the error log (/var/log/mysql/error.log)
innodb_print_all_deadlocks = 1
# Reduce lock wait timeout from default 50s down to 5s-10s for web apps
innodb_lock_wait_timeout = 10Why should you reduce innodb_lock_wait_timeout? By default, MySQL allows transactions to wait up to 50 seconds for a lock. In web applications, holding a connection idle for 50 seconds will exhaust the connection pool in the blink of an eye. Reducing this to 10 seconds helps queries fail fast, releasing resources for other requests and returning a clean error for the client layer to retry.
3. Transaction Design Patterns to Prevent Deadlocks
After inspecting the logs from that 2:00 AM incident, the culprit became clear: the fund transfer module between two e-wallets.
When User 10 transferred funds to User 20, the code locked record 10 first, then record 20. Simultaneously, User 20 transferred money back to User 10, locking record 20 before record 10. The two processes collided head-on, triggering a deadlock.
The definitive solution lies at the application code level: Deterministic Lock Ordering. Regardless of transfer direction, we always enforce acquiring locks on the lower ID first, followed by the higher ID.
Python code standardizing this workflow:
def transfer_funds(connection, from_account_id: int, to_account_id: int, amount: float):
if from_account_id == to_account_id:
raise ValueError("Source and destination accounts cannot be identical")
# Enforce deterministic lock ordering: always lock lower ID first, higher ID second
first_id, second_id = sorted([from_account_id, to_account_id])
cursor = connection.cursor()
try:
connection.begin()
# 1. Lock sequentially in ascending ID order to eliminate circular waits
cursor.execute("SELECT id, balance FROM accounts WHERE id = %s FOR UPDATE", (first_id,))
acc1 = cursor.fetchone()
cursor.execute("SELECT id, balance FROM accounts WHERE id = %s FOR UPDATE", (second_id,))
acc2 = cursor.fetchone()
# 2. Verify sender account balance
sender_balance = acc1["balance"] if acc1["id"] == from_account_id else acc2["balance"]
if sender_balance < amount:
raise ValueError("Insufficient available balance for transaction")
# 3. Update balances for both accounts
cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_account_id))
cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_account_id))
connection.commit()
except Exception as e:
connection.rollback()
raise e
finally:
cursor.close()Two essential principles to remember when writing transactions:
- Keep transactions as short as possible: Never invoke third-party APIs (payment gateways, SMS/email services) or execute heavy computations inside an active transaction. Complete external API calls beforehand, and only open the database transaction to persist the changes.
- Ensure UPDATE/DELETE statements are backed by indexes: If an
UPDATE ... WHERE ...statement cannot leverage an index, InnoDB must perform a full-table scan. Consequently, it places locks across all rows and index gaps (Gap Locks), causing the risk of deadlocks to skyrocket.
Verification & Long-Term Monitoring
Writing clean code is only step one. You need a monitoring system to verify that your fix is genuinely effective.
1. Inspecting the Error Log Directly
Use tail to isolate deadlock entries from MySQL’s logs:
sudo tail -f /var/log/mysql/error.log | grep -A 20 "InnoDB: transactions deadlock detected"2. Checking Deadlock Counters via Status Metrics
On Percona Server or MariaDB, you can check quickly using:
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';If you are running standard MySQL (Community 5.7/8.0+), that status variable is not available. Instead, enable the InnoDB Metrics table:
-- Enable the deadlock metric counter if not already active
SET GLOBAL innodb_monitor_enable = 'lock_deadlocks';
-- Query total deadlock occurrences since server startup
SELECT name, count FROM information_schema.innodb_metrics WHERE name = 'lock_deadlocks';3. Setting Up Prometheus Alertmanager Alerts
Don’t wait for angry user reports to discover system outages. Set up alert rules in Prometheus using mysqld_exporter:
- alert: MySQLDeadlockSpike
expr: rate(mysql_info_schema_innodb_metrics_lock_deadlocks_total[5m]) > 0
for: 1m
labels:
severity: warning
annotations:
summary: "Deadlock detected on MySQL {{ $labels.instance }}"
description: "Deadlock rate exceeded 0 over the past 5 minutes. Check error logs immediately."Deadlocks are an inherent reality in multithreaded relational database systems. The goal is not to pray they never happen, but to enforce strict lock ordering, minimize lock duration, and build monitoring sensitive enough to resolve issues before they escalate.
