Deadlocks: Cause, Detection and Prevention

A deadlock happens when two (or more) transactions each hold a lock that the other needs, so neither can continue. Without intervention they would wait forever.

InnoDB detects deadlocks immediately, picks one transaction as the victim, rolls it back, and lets the other proceed. The victim’s statement fails with error 1213. A deadlock is not a bug in MySQL: it is a normal event in a busy system that your application must handle.

A real deadlock

1
2

CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(10), balance INT) ENGINE = InnoDB;
INSERT INTO accounts VALUES (1, 'asha', 100), (2, 'ben', 100), (3, 'chitra', 100);

Two transfers run at the same moment, in opposite directions:

1
2
3
4
5
6
7
8
9
10
11
12
13

-- A: transfer 10 from Asha (1) to Ben (2)
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;     -- A locks row 1

-- B: transfer 20 from Ben (2) to Asha (1)
START TRANSACTION;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;     -- B locks row 2

-- A:
UPDATE accounts SET balance = balance + 10 WHERE id = 2;     -- A waits for B's lock on row 2

-- B:
UPDATE accounts SET balance = balance + 20 WHERE id = 1;     -- B waits for A's lock on row 1: a cycle

1

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

A waits for B, and B waits for A. InnoDB sees the cycle, and aborts one transaction (here B) with error 1213. B’s whole transaction is rolled back. A’s waiting update then succeeds, and A can commit:

1
2
3

-- A:
COMMIT;
SELECT * FROM accounts ORDER BY id;

1
2
3
4
5
6
7

+----+--------+---------+
| id | owner  | balance |
+----+--------+---------+
|  1 | asha   |      90 |
|  2 | ben    |     110 |
|  3 | chitra |     100 |
+----+--------+---------+

Only A’s transfer took effect. B must retry its transfer.

The cause

Both transactions locked the same rows in different orders: A took 1 then 2, B took 2 then 1. That is the most common cause of deadlocks. Others:

• Two transactions inserting into a range with gap locks, under REPEATABLE READ.
• A transaction that reads with a shared lock, then tries to upgrade to exclusive, while another does the same.
• Updates through different indexes that touch the same rows in different orders.
• Foreign key checks that lock parent rows.

Read the details

1

SHOW ENGINE INNODB STATUS\G

The section LATEST DETECTED DEADLOCK shows both transactions, the statement each was running, the locks each held and the lock each was waiting for, and which one was rolled back:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

*** (1) TRANSACTION:
TRANSACTION 64613, ACTIVE 1 sec starting index read
UPDATE accounts SET balance = balance + 10 WHERE id = 2

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `tx`.`accounts` ... lock_mode X locks rec but not gap
 ... (the row of asha)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `tx`.`accounts` ... lock_mode X locks rec but not gap waiting
 ... (the row of ben)

*** (2) TRANSACTION:
UPDATE accounts SET balance = balance + 20 WHERE id = 1
*** (2) HOLDS THE LOCK(S): ... (the row of ben)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ... (the row of asha)

*** WE ROLL BACK TRANSACTION (2)

(Excerpt from a real run, shortened.) Read it as: transaction 1 holds row asha and waits for row ben, transaction 2 holds row ben and waits for row asha. Only the latest deadlock is shown. To log all deadlocks to the error log, turn on:

1

SELECT @@innodb_print_all_deadlocks AS logs_all_deadlocks;

1
2
3
4
5
6

+--------------------+
| logs_all_deadlocks |
+--------------------+
|                  0 |
+--------------------+
1 row in set

(SET GLOBAL innodb_print_all_deadlocks = ON; makes it log every deadlock, which is useful on a production system that you are investigating.)

How InnoDB chooses the victim

It rolls back the transaction that has changed fewer rows (the “cheaper” one to undo), measured by the number of rows modified and locks held. You cannot choose.

Handle it in the application

A deadlock error means: “your transaction was rolled back; try again.” The standard solution is a retry loop for the whole transaction:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
function runInTransaction(PDO $pdo, callable $work, int $maxAttempts = 3) {
    for ($attempt = 1; ; $attempt++) {
        try {
            $pdo->beginTransaction();
            $result = $work($pdo);
            $pdo->commit();
            return $result;
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            $mysqlCode = $e->errorInfo[1] ?? 0;
            $retryable = in_array($mysqlCode, [1213, 1205], true);   // deadlock, lock wait timeout
            if (!$retryable || $attempt >= $maxAttempts) {
                throw $e;
            }
            usleep(random_int(20000, 100000) * $attempt);             // short random back-off
        }
    }
}

Rules for retrying:

• Retry the whole transaction, from START TRANSACTION, not just the failed statement.
• Re-read the data inside the retry. Do not reuse values from the failed attempt.
• Keep the retry count small (2 or 3) and add a short random pause.
• Make the work idempotent, so a retry cannot apply it twice (Transaction Patterns).

Prevent deadlocks

1. Lock rows in a consistent order. For a transfer between two accounts, always update the lower id first, whichever direction the money moves:

1
2
3
4

START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = LEAST(1, 2);     -- the lower id first
UPDATE accounts SET balance = balance + 10 WHERE id = GREATEST(1, 2);
COMMIT;

In code: sort the ids, SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE to lock them all in order, then do the updates.

1. Keep transactions short and small. Fewer locks held for less time means fewer cycles.
2. Index the columns in WHERE clauses, so each statement locks only the rows it needs.
3. Use READ COMMITTED if gap locks cause the deadlocks.
4. Avoid mixing shared-lock reads (FOR SHARE) with later updates of the same rows. Take FOR UPDATE from the start.
5. Use the same order of statements in all code paths that touch the same tables.
6. Break up big batch jobs into small transactions (Changing Millions of Rows).

Deadlock or lock wait timeout?

How often is too often?

A few deadlocks per day on a busy system are normal. Dozens per minute mean a design problem: look at the statements in SHOW ENGINE INNODB STATUS, find the pair, and fix the lock order or the indexes.