Deadlocks and Lock Contention

Concept

A deadlock happens when two transactions each hold a lock the other needs. Neither can proceed, and neither will ever release. The database detects the cycle and kills one of them.

The classic deadlock

-- Session A                                -- Session B
START TRANSACTION;                           START TRANSACTION;

UPDATE accounts SET balance = balance - 100
WHERE id = 1;              -- locks row 1
                                             UPDATE accounts SET balance = balance - 50
                                             WHERE id = 2;          -- locks row 2

UPDATE accounts SET balance = balance + 100
WHERE id = 2;              -- waits for B
                                             UPDATE accounts SET balance = balance + 50
                                             WHERE id = 1;          -- waits for A
-- DEADLOCK: InnoDB rolls one of them back

The cause is visible once stated: A locks rows in the order 1 then 2, B locks them 2 then 1. Any two transactions that acquire the same locks in different orders can deadlock.

The fix

-- Both sessions touch rows in ascending id order. No cycle is possible.
START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Or lock everything needed up front, in a deterministic order
START TRANSACTION;
    SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

What the database does about it

InnoDB detects the cycle immediately and rolls back the transaction it judges cheapest to undo - usually the one that has changed fewest rows. The victim gets:

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

The error is expected under load and the correct response is to retry the whole transaction, not to treat it as a bug:

-- Application pseudocode
-- for attempt in 1..3:
--     try:
--         run the transaction
--         break
--     catch deadlock (SQLSTATE 40001):
--         wait a short random interval
--         retry

Diagnosing

-- The full text of the most recent deadlock, with both transactions and their locks
SHOW ENGINE INNODB STATUS;

-- MySQL 8: log every deadlock to the error log
SET GLOBAL innodb_print_all_deadlocks = ON;

-- What is currently waiting on what
SELECT * FROM sys.innodb_lock_waits;                 -- MySQL 8
SELECT * FROM performance_schema.data_lock_waits;    -- MySQL 8

Deadlock vs contention

Contention hot spots

• A counter row everyone updates. Every transaction queues on one row. Shard it into N rows and sum them, or move the count to a periodic job.
• Long transactions. A transaction open for 30 seconds holds its locks for 30 seconds.
• Unindexed updates. Locking rows that do not match multiplies the conflict surface.
• Wide transaction scope. Touching ten tables when two would do.

Important rules

• Deadlocks are detected and resolved automatically in InnoDB; the victim's transaction is rolled back entirely.
• A deadlock is not corruption. It is the engine protecting consistency.
• Retrying is the correct application response, with a small random backoff.
• Gap locks under REPEATABLE READ make deadlocks more likely than under READ COMMITTED.
• Even single statement transactions can deadlock if they touch multiple rows in different orders.

Common mistakes

• Treating deadlocks as unrecoverable errors and surfacing them to users.
• Retrying only the failed statement instead of the whole transaction - the rollback undid everything.
• Acquiring locks in whatever order the loop happens to produce.
• Raising the lock wait timeout instead of shortening the transaction.
• Adding SERIALIZABLE to fix a data bug and multiplying deadlocks.

Best practices

• Always acquire locks in a consistent order - sort ids before updating, and touch tables in a documented sequence.
• Keep transactions short and do no external I/O inside them.
• Index the columns your UPDATE and DELETE statements filter on.
• Implement retry with backoff for SQLSTATE 40001 in every write path.
• Log deadlocks and review them; a rising rate is a design signal, not noise.

Practice

1. Reproduce the two session deadlock above and read the SHOW ENGINE INNODB STATUS output.
2. Rewrite both transactions so a deadlock is impossible.
3. Design a counter that avoids a single hot row, and say what it costs on read.