Understanding MySQL Deadlocks and How to Prevent Them

MySQL deadlock refers to a situation where two or more transactions wait for each other’s resources, causing a mutual wait that cannot be resolved without external intervention. In MySQL, deadlocks mainly occur in the InnoDB storage engine, which supports transactions and row‑level locking.

InnoDB uses a wait‑for graph to automatically detect deadlocks; when a deadlock is found, it rolls back one of the involved transactions, usually the smaller one based on the number of rows inserted, updated, or deleted.

Typical scenarios that lead to deadlocks include:

Race conditions: when transactions lock resources in different orders, e.g., transaction A locks resource 1 then tries to lock resource 2 while transaction B has locked resource 2 and tries to lock resource 1.

Inadequate indexes: missing or poorly designed indexes cause full‑table scans and lock many rows, increasing deadlock probability.

Long‑running transactions: transactions that hold locks for extended periods raise the chance of conflict.

Lock escalation: MySQL may upgrade row locks to higher‑level locks such as table locks, which can also cause deadlocks.

Examples of deadlock occurrences are illustrated in the following diagram:

How to avoid MySQL deadlocks

1. Detect deadlocks: InnoDB automatically aborts one transaction when a deadlock is detected. You can view the MySQL error log or run SHOW ENGINE INNODB STATUS to examine deadlock information.

2. Keep transactions short: Reduce transaction duration and commit or roll back promptly.

3. Optimize queries and indexes: Ensure queries are efficient and appropriate indexes are defined to minimize the number of locked rows.

4. Use lock timeout: Adjust the innodb_lock_wait_timeout parameter to limit how long a transaction waits for a lock before being terminated.

5. Analyze and monitor: Use tools such as Percona Toolkit or pt‑deadlock‑logger to monitor and analyze deadlocks.

6. Consistent lock ordering: Redesign transaction logic or reorder operations so that locks are acquired in the same order across transactions.

7. Higher‑level locks when appropriate: In some cases, using table locks instead of row locks can reduce deadlock risk, though it may affect concurrency.

Source: https://dev.mysql.com/doc/refman/8.3/en/innodb-deadlocks.html