Understanding and Handling MySQL Deadlocks

1. Introduction

About database deadlocks

Database deadlock is a situation where different transactions wait for each other's resources, causing execution to halt; the database system will automatically abort one transaction to break the deadlock.

In databases, transactions can be read transactions (requiring read locks) or write transactions (requiring write locks). When multiple transactions operate on the same data set simultaneously, deadlocks may arise.

2. Situations in MySQL that cause deadlocks

MySQL deadlocks mainly occur in the following cases:

2.1 Transactions updating multiple tables simultaneously

When a transaction updates multiple tables in different orders, a deadlock can occur. For example, Transaction A updates table X first, acquires its lock, and then tries to update table Y without releasing the first lock; Transaction B does the opposite, updating Y first and then X. Both transactions wait for each other's locks, forming a deadlock.

2.2 Nested transactions

If a transaction opens another transaction inside it and the inner transaction updates a table that the outer transaction also needs to update, a deadlock may occur because each transaction waits for the other to release the lock.

2.3 Inconsistent index order

When multiple transactions access the same rows in different orders using different indexes, a deadlock can happen. For instance, Transaction A accesses rows following index 1, while Transaction B follows index 2, causing them to wait on each other's locks.

2.4 Different transactions updating the same index simultaneously

When several transactions update the same index at the same time, they may each hold locks on the index while trying to update other data, leading to a deadlock.

3. Example of a deadlock

Assume two users operate on a bank account table to perform transfers.

User A executes the following transaction:

BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

User B executes the following transaction:

BEGIN;
SELECT balance FROM accounts WHERE id = 2;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

During concurrent execution, the following may happen:

User A reads the balance of account 1.

User B reads the balance of account 2.

User A updates account 1, decreasing the balance by 500.

User B updates account 2, increasing the balance by 500.

User A attempts to commit and needs to lock account 2 for logging.

User B attempts to commit and needs to lock account 1 for logging.

Because each transaction waits for the lock held by the other, a deadlock occurs. MySQL automatically selects one transaction as the victim and rolls it back to break the deadlock.

4. How to handle deadlocks in production

If a deadlock occurs online, follow these steps:

4.1 Monitor deadlocks

Use database monitoring tools or commands to check whether deadlocks exist, and understand the specific situation, including the involved transactions and resources.

4.2 Terminate a deadlock transaction

Based on monitoring results, locate the offending transaction and terminate one of them. Decision factors may include execution time, affected rows, priority, etc. Use the shown statement to view deadlock details.

4.3 Retry the transaction

After terminating the deadlock transaction, re‑execute the rolled‑back transaction. This may require logic such as rolling back data or re‑running certain operations.

4.4 Analyze the cause

Analyze database logs and monitoring information to determine why the deadlock occurred. Optimize database design or code based on the findings.

4.5 Prevent recurrence

According to the analysis, make targeted adjustments such as schema changes, index optimization, or transaction isolation level modifications to reduce the probability of future deadlocks.

4.6 Monitoring and alerting

Establish a deadlock monitoring mechanism and set up appropriate alerts so that deadlocks can be detected and addressed promptly.

The above are general steps for handling online deadlocks; specific actions depend on the actual situation, and operations should be safe, reliable, and minimize impact on users.

5. Summary

Database deadlock occurs when different transactions wait for each other's resources, halting execution. In MySQL, deadlocks may arise from simultaneous multi‑table updates, nested transactions, inconsistent index order, and concurrent updates to the same index.