Understanding Deadlocks: Causes, Prevention Strategies, and a MySQL Walkthrough

01 Deadlock Generation and Prevention

Deadlocks occur when four necessary conditions are simultaneously satisfied: mutual exclusion, non‑preemptibility, hold‑and‑wait, and circular wait.

Mutual Exclusion : Only one process can hold a resource at a time; others must wait.

Non‑Preemptibility : A resource cannot be forcibly taken from a process; it must be released voluntarily.

Hold‑and‑Wait : A process holding at least one resource requests additional resources that are currently held by others, leading to blocking without releasing its own resources.

Circular Wait : Processes form a cycle of resource requests, each waiting for a resource held by the next process in the cycle.

All four conditions must be present for a deadlock to arise.

Four main approaches to handle deadlocks are:

Prevention : Break one or more of the necessary conditions to stop deadlocks from forming.

Avoidance : Use strategies during resource allocation to keep the system out of unsafe states.

Detection : Allow deadlocks to occur, then detect them and take corrective actions.

Recovery : Once detected, apply techniques to break the deadlock and resume normal operation.

In practice, ordered resource allocation and the Banker's algorithm are common methods to avoid deadlocks.

02 MySQL Deadlock Issues

In MySQL 5.5.5+ the default InnoDB engine uses row‑level locks, which can still lead to deadlocks. InnoDB employs a wait‑for graph to automatically detect deadlocks and rolls back one of the conflicting transactions.

Below is a MySQL deadlock case study.

Step 1

Open terminal A, set the transaction isolation level to REPEATABLE READ, start a transaction, and acquire an exclusive lock on the row with id=1 in the account table.

mysql> set session transaction isolation level repeatable read;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>mysql> start transaction;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>mysql> select * from account where id=1 for update;<br/>+----+--------+---------+<br/>| id | name   | balance |<br/>+----+--------+---------+<br/>| 1  | 张三   | 300     |<br/>+----+--------+---------+<br/>1 row in set (0.00 sec)

Step 2

Open terminal B, set the same isolation level, start a transaction, and lock the row with id=2.

mysql> set session transaction isolation level repeatable read;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>mysql> start transaction;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>mysql> select * from account where id=2 for update;<br/>+----+--------+---------+<br/>| id | name   | balance |<br/>+----+--------+---------+<br/>| 2  | 李四   | 350     |<br/>+----+--------+---------+<br/>1 row in set (0.00 sec)

Step 3

In terminal A, attempt to lock the row with id=2. The request blocks because terminal B holds the lock.

mysql> select * from account where id=2 for update;

Step 4

In terminal B, attempt to lock the row with id=1, which results in a deadlock error.

mysql> select * from account where id=1 for update;<br/>ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The deadlock can be inspected using SHOW ENGINE INNODB STATUS\G or by enabling innodb_print_all_deadlocks to log details.

Common MySQL deadlock avoidance techniques include:

Use indexes to ensure queries use row‑level locks instead of escalating to table locks.

Design indexes to minimize lock scope.

Avoid wide range queries that cause gap locks.

Keep transactions short and limit the number of locked resources.

Place lock‑intensive statements at the end of transactions.

Prefer lower isolation levels when appropriate.