Row locks, gap locks and deadlocks

Key points

• InnoDB locks index records, not rows in the abstract. A statement with no usable index locks every record it examines, so a missing index turns a small update into a table-wide block.
• Under REPEATABLE READ, InnoDB also takes gap locks - locks on the space between index values - to stop phantom rows appearing. That is why one transaction can block an insert of a key that does not exist yet.
• Deadlock is two transactions holding what the other needs. InnoDB detects it, rolls back the cheaper transaction, and returns error 1213 - which the application must be prepared to retry.
• The most effective prevention is consistent lock ordering. If every transaction touches accounts in ascending id order, a cycle cannot form.
• Keep transactions short and do no remote calls inside them. A transaction that holds locks while waiting on an HTTP response converts a slow dependency into database contention.
• SHOW ENGINE INNODB STATUS prints the last deadlock in full - both transactions, both statements and the locks held. It is the only reliable way to diagnose one.

Example

-- A missing index makes this lock far more than it should.
UPDATE orders SET status = 'CANCELLED' WHERE customer_id = 5;
--   No index on customer_id -> InnoDB examines every row and locks
--   every index record it touches, blocking unrelated writers.

-- GAP LOCK. Under REPEATABLE READ:
--   T1: SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;
--   T2: INSERT INTO orders (id, ...) VALUES (15, ...);   -- BLOCKS
--   Row 15 may not exist - the GAP is locked to prevent a phantom.

-- DEADLOCK - two transactions, opposite order:
--   T1: UPDATE accounts SET bal = bal - 100 WHERE id = 1;   -- locks 1
--   T2: UPDATE accounts SET bal = bal - 50  WHERE id = 2;   -- locks 2
--   T1: UPDATE accounts SET bal = bal + 100 WHERE id = 2;   -- waits on T2
--   T2: UPDATE accounts SET bal = bal + 50  WHERE id = 1;   -- cycle -> 1213

-- PREVENTION: always take locks in the same order.
START TRANSACTION;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
--   ORDER BY id means every transaction locks 1 before 2. No cycle.
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
UPDATE accounts SET bal = bal + 100 WHERE id = 2;
COMMIT;

-- Diagnose the last one - it prints both sides in full:
SHOW ENGINE INNODB STATUS\G
--   LATEST DETECTED DEADLOCK
--   *** (1) TRANSACTION: ... WAITING FOR THIS LOCK TO BE GRANTED
--   *** (2) TRANSACTION: ... HOLDS THE LOCK(S)

-- What is blocking right now (MySQL 8):
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.innodb_lock_waits;

-- Application side: 1213 is expected, and must be retried.
--   @Retryable(retryFor = DeadlockLoserDataAccessException.class,
--              maxAttempts = 3,
--              backoff = @Backoff(delay = 50, multiplier = 2, random = true))

InnoDB locks index records, so a missing index widens every lock - and consistent lock ordering is what actually prevents deadlocks.