Understanding MySQL Deadlocks: Causes, Real-World Example, and Fixes

What is MySQL deadlock

MySQL deadlock occurs when multiple transactions compete for resources and each waits for the other to release locks, causing an indefinite wait.

Causes of MySQL deadlock

Four necessary conditions must be met simultaneously:

Mutual Exclusion : a resource can be held by only one transaction at a time.

Hold and Wait : a transaction holding one resource may request another.

No Preemption : resources cannot be forcibly taken from a transaction.

Circular Wait : transactions form a cycle of waiting for each other's resources.

MySQL deadlock example

Assume two transactions A and B each lock different rows (account_id 1 and 2) and then try to lock the other's row, leading to a deadlock.

SQL to create a sample table:

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance INT
);
INSERT INTO accounts (account_id, balance) VALUES (1, 100);
INSERT INTO accounts (account_id, balance) VALUES (2, 200);

Transaction A:

START TRANSACTION;
-- Step 1: lock account_id = 1
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Step 2: waits for account_id = 2 locked by B

Transaction B:

START TRANSACTION;
-- Step 1: lock account_id = 2
SELECT balance FROM accounts WHERE account_id = 2 FOR UPDATE;
-- Step 2: waits for account_id = 1 locked by A

Solutions to MySQL deadlocks

Acquire locks in a consistent order, e.g., by ascending account_id.

Set appropriate lock wait timeouts and roll back if a lock cannot be obtained.

Rely on MySQL’s deadlock detection, which aborts one transaction to break the cycle.

In practice, preventing deadlocks also involves choosing suitable isolation levels, optimizing queries, and designing effective concurrency control.