TopolearnInnoDB's gap locking is the source of most surprising lock waits and deadlocks in MySQL applications.
InnoDB locks index records, not rows in the abstract. A locking statement (UPDATE, DELETE, SELECT ... FOR UPDATE / FOR SHARE) locks every index record it scans, not just the ones it changes. Without a usable index, an UPDATE ... WHERE scans and locks the whole table.
Lock types:
- Record lock: on one index record.
- Gap lock: on the gap between index records, preventing inserts into that range. Gap locks exist to stop phantoms in REPEATABLE READ.
- Next-key lock: a record lock plus the gap before it — the default for locking scans in REPEATABLE READ.
- Insert intention lock: taken by inserts; conflicts with gap locks held by others.
So in REPEATABLE READ, SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE also blocks another session inserting id = 15. For a unique-index equality match on an existing row, InnoDB locks only that record. Under READ COMMITTED, gap locking is mostly disabled, which reduces contention (and is why many high-traffic apps use it).
Deadlocks happen when sessions lock records in different orders. InnoDB detects them and rolls back one transaction (error 1213); the application should retry. Diagnose with SHOW ENGINE INNODB STATUS (latest deadlock section), innodb_print_all_deadlocks = ON, and live locks in performance_schema.data_locks and sys.innodb_lock_waits. innodb_lock_wait_timeout (default 50 s) bounds waits.
FOR UPDATE SKIP LOCKED and NOWAIT (8.0) help build job queues.
Example
CREATE TABLE seat (id INT PRIMARY KEY, taken BOOL NOT NULL DEFAULT FALSE);
INSERT INTO seat (id) VALUES (10), (20), (30);
-- Session A (REPEATABLE READ)
START TRANSACTION;
SELECT * FROM seat WHERE id BETWEEN 10 AND 20 FOR UPDATE; -- next-key locks
-- Session B
INSERT INTO seat (id) VALUES (15); -- waits: gap (10, 20) is locked by A
-- Session C: see who waits for whom
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.innodb_lock_waits;
-- Session A
COMMIT; -- B's insert proceeds
-- job queue: each worker gets a different free seat without waiting
SELECT id FROM seat WHERE NOT taken ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;Remember
- InnoDB locks the index records a statement scans, plus gaps in REPEATABLE READ
- Index your UPDATE/DELETE predicates or you lock far too much
- Deadlocks are normal under concurrency: detect, roll back, retry
Watch out for
- Running UPDATE ... WHERE unindexed_col = ? on a busy table, locking every row.