Understanding InnoDB Locking and Deadlocks

2019-06-21 · mysql, innodb, locking, transactions

The first time I saw ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in an application log, I assumed MySQL was broken. It isn't. Two transactions each hold a lock the other needs, InnoDB detects the cycle, and it rolls back the one that has done less work. The app is expected to retry. Working out why the cycle formed means knowing what InnoDB actually locks, and it's more than "the row".

Below are the lock types you'll meet, how I read the deadlock report, and the patterns that cause most deadlocks I see. Examples use MySQL 5.7 and 8.0. MariaDB behaves the same with slightly different status output.

The lock types

InnoDB row locks come in shared (S) and exclusive (X) flavours, but the thing being locked is an index record, not a table row. From that follow three variants:

Record lock: locks one index entry. SELECT ... FOR UPDATE on a primary key hit.
Gap lock: locks the space between index entries, preventing inserts into that range. Only in REPEATABLE READ, the default isolation level.
Next-key lock: a record lock plus the gap before it. This is what range scans under REPEATABLE READ take, and it is the source of most surprising deadlocks.

Plus two that are not row locks at all:

Insert intention lock: a gap lock taken by INSERT to signal intent, which waits on any conflicting gap lock.
Auto-increment lock: with innodb_autoinc_lock_mode=1 (the 5.7 default), a lightweight lock for the duration of the statement.

Table-level metadata locks (MDL) sit above all of this and are what block ALTER TABLE behind a long-running SELECT.

Watching locks live

Open two sessions and let them collide. In session A:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;

In session B:

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 7;
-- hangs

In a third session on 5.7:

SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT trx_id, trx_state, trx_started, trx_query FROM information_schema.INNODB_TRX;

On 8.0 the tables moved to Performance Schema:

SELECT * FROM performance_schema.data_lock_waits;
SELECT engine_lock_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

lock_mode shows X for a record lock, X,GAP for a gap lock, and X with a lock_data of supremum pseudo-record for the gap after the last index entry.

Session B waits innodb_lock_wait_timeout seconds (50 by default) then fails with ERROR 1205 (HY000): Lock wait timeout exceeded. That is a plain wait, not a deadlock; only a cycle triggers 1213.

Reading the deadlock report

InnoDB keeps the most recent deadlock in the engine status:

SHOW ENGINE INNODB STATUS\G

Under LATEST DETECTED DEADLOCK:

*** (1) TRANSACTION:
TRANSACTION 421388, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
UPDATE orders SET status='paid' WHERE id = 5002
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `appdb`.`orders` trx id 421388 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 421390, ACTIVE 8 sec starting index read
UPDATE orders SET status='paid' WHERE id = 5001
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 4 ... index PRIMARY ... lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 ... index PRIMARY ... lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Read it as: transaction 1 holds row 5001 and wants 5002; transaction 2 holds 5002 and wants 5001. Classic ordering deadlock. The report shows only the statement each transaction was running when it blocked, not the earlier statements that took the locks it holds, so you usually have to reconstruct the transaction from application code.

I turn this on for any server that has ever deadlocked, so I can log every deadlock to the error log rather than only the latest:

SET GLOBAL innodb_print_all_deadlocks = ON;

Common patterns and fixes

Inconsistent ordering. Two code paths update the same rows in different orders. Fix: always touch rows in a fixed order (by primary key ascending is the usual choice), or lock the parent row first with SELECT ... FOR UPDATE.

Gap locks on range updates. UPDATE t SET x=1 WHERE created_at < '2019-06-01' under REPEATABLE READ takes next-key locks on the whole scanned range, blocking inserts from other sessions and creating cycles with them. Fix: give the query an index that makes the range narrow, or run the batch job in READ COMMITTED, which drops gap locks for most statements:

SET SESSION transaction_isolation = 'READ-COMMITTED';

Unique key insert races. Two sessions INSERT the same unique value; the second waits for a shared lock on the duplicate, then the first inserts an adjacent row and needs an exclusive lock the second's shared lock blocks. Fix: INSERT ... ON DUPLICATE KEY UPDATE in a single statement, or serialise with an application-level lock.

Missing index on the WHERE column. Without an index, an UPDATE scans and locks every row in the table. Any concurrent write then deadlocks. Fix: add the index; EXPLAIN the UPDATE to confirm.

Confirming a fix

After a fix, reproduce the old collision deliberately with two mysql sessions and confirm the second now waits briefly rather than deadlocking. Then watch the counter over a day:

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

Innodb_row_lock_waits and Innodb_row_lock_time_avg should fall. On 8.0 you can also check SELECT * FROM sys.innodb_lock_waits; during peak load for anything still blocking.

Notes

Retrying is not optional. Even a perfectly ordered application can hit 1213 under load. Wrap transactions in a retry loop of two or three attempts; the rolled-back transaction has already had its locks released.

innodb_lock_wait_timeout is a session variable. Lower it for interactive tools and raise it only for batch jobs that legitimately wait on long transactions.

Deadlock detection itself costs CPU on servers with thousands of concurrent transactions on hot rows. innodb_deadlock_detect = OFF (5.7.15+) falls back to timeouts; only consider it when SHOW ENGINE INNODB STATUS shows the detector as the bottleneck.

READ COMMITTED needs binlog_format = ROW. Statement-based replication with that isolation level is refused with ERROR 1665.

Most of my deadlocks turned out to be two code paths touching the same rows in different order. Fix it in the code, and keep the retry loop anyway.