How do row locks and MVCC work in InnoDB, and how do you avoid deadlocks?

Question

IT Interview Practice · Accepted Answer

InnoDB combines two mechanisms. **MVCC** lets plain `SELECT`s read a consistent snapshot without locking, so readers never block writers. **Locks** protect writes: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` take row locks, and InnoDB also uses **next-key locks** (row + the gap before it) to stop phantoms under REPEATABLE READ.

## Deadlocks

A deadlock is a cycle: T1 holds lock A and wants B, while T2 holds B and wants A. Neither can proceed. InnoDB **detects the cycle and rolls back one transaction** (the cheaper victim) with error 1213, so the other completes.

```sql
-- T1                              -- T2
BEGIN;                             BEGIN;
UPDATE acct SET bal=bal-1          UPDATE acct SET bal=bal-1
  WHERE id=1;  -- locks row 1        WHERE id=2;  -- locks row 2
UPDATE acct SET bal=bal+1          UPDATE acct SET bal=bal+1
  WHERE id=2;  -- waits for T2        WHERE id=1;  -- waits for T1 => DEADLOCK
-- ERROR 1213: Deadlock found; transaction rolled back
```

## Avoiding them

- **Consistent lock ordering** — always touch rows in the same order (e.g. ascending id). The example deadlocks only because the two transactions lock 1→2 and 2→1.
- **Keep transactions short** — acquire locks late, commit fast, don't hold locks across network/user waits.
- **Lock the minimum** — index your `WHERE` so InnoDB locks specific rows, not ranges or (without an index) effectively every row scanned.
- **Retry on 1213** — deadlocks are normal under contention; wrap the transaction in an application-level retry.

```sql
SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK explains who waited on what
```

## Why it matters

Under real concurrency, lock contention and deadlocks are the difference between a database that scales and one that stalls. Knowing that readers use MVCC (so they don't block), that deadlocks are expected and retryable, and that consistent lock ordering prevents most of them is exactly what senior on-call work demands.

Deadlocks

Avoiding them

Why it matters

Related questions