MySQL "Deadlock found when trying to get lock": read the report, retry, and stop the cycle

1. Read the report

SHOW ENGINE INNODB STATUS\G

Find the section LATEST DETECTED DEADLOCK:

*** (1) TRANSACTION:
TRANSACTION 5401, ACTIVE 8 sec starting index read
UPDATE accounts SET balance = balance - 10 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `shop`.`accounts` trx id 5401 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `shop`.`accounts` trx id 5401 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
...
*** WE ROLL BACK TRANSACTION (2)

Look at the two queries, the table, the index named, and the lock mode. lock_mode X locks gap before rec or next-key tell you it’s locking ranges, not only single rows. Often that’s the key to the cause.

It only keeps the latest one. To get them all in the error log:

SET GLOBAL innodb_print_all_deadlocks = ON;

2. Retry in your code

Every application using transactions on a busy database needs this. Retry the whole transaction, wait a little, and give up after a few tries:

for attempt in range(3):
    try:
        with connection.begin():
            debit(connection, from_id, amount)
            credit(connection, to_id, amount)
        break
    except OperationalError as e:
        if e.orig.args[0] != 1213 or attempt == 2:
            raise
        time.sleep(0.05 * 2 ** attempt)

Include whatever you read inside the transaction, as those values could have changed. And don’t send emails or call other services from inside it, or they’d repeat on each attempt.

3. Lock rows in the same order

Opposite orders cause cycles, so one consistent order removes them. In the example, sort by id:

first, second = sorted([from_id, to_id])

For updates of many rows, one statement doesn’t let you choose the order easily, so lock them first in a known order:

START TRANSACTION;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

4. Add the missing index

InnoDB locks the rows it scans, not only those it changes. With no useful index, an UPDATE may lock a large part of the table, and then two unrelated updates collide. Check how the query runs:

EXPLAIN UPDATE orders SET status = 'sent' WHERE customer_id = 42;

type: ALL or a huge rows figure is a warning. An index fixes both the speed and the locks:

CREATE INDEX idx_orders_customer ON orders (customer_id);

5. Keep transactions short

The longer a transaction runs, the more it collides. So don’t hold one open while calling an API, waiting for a user, or processing a file. Break a large update into batches, with a commit for each:

UPDATE orders SET archived = 1 WHERE created_at < '2024-01-01' AND archived = 0 LIMIT 1000;

Repeat until it affects no rows.

6. Gap locks and INSERT

Under the default REPEATABLE READ, InnoDB also locks the gaps between rows. That’s behind a typical surprise: two sessions running SELECT ... FOR UPDATE on a row that doesn’t exist, and then both inserting:

SELECT * FROM stock WHERE sku = 'x' FOR UPDATE;   -- no row: takes a gap lock, and both can hold it
INSERT INTO stock (sku, qty) VALUES ('x', 1);     -- waits for the other: deadlock

Use one atomic statement instead:

INSERT INTO stock (sku, qty) VALUES ('x', 1)
ON DUPLICATE KEY UPDATE qty = qty + 1;

Some workloads do better with READ COMMITTED, which takes far fewer gap locks. It changes what your reads see, so test it first:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;