Hoe werken row locks en MVCC in InnoDB, en hoe vermijd je deadlocks?

Question

IT Interview Practice · Accepted Answer

InnoDB combineert twee mechanismen. **MVCC** laat gewone `SELECT`s een consistente snapshot lezen zonder te locken, zodat readers nooit writers blokkeren. **Locks** beschermen writes: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` nemen row locks, en InnoDB gebruikt ook **next-key locks** (rij + de gap ervoor) om phantoms onder REPEATABLE READ te stoppen.

## Deadlocks

Een deadlock is een cyclus: T1 houdt lock A vast en wil B, terwijl T2 B vasthoudt en A wil. Geen van beide kan verder. InnoDB **detecteert de cyclus en rolt één transactie terug** (het goedkopere slachtoffer) met fout 1213, zodat de ander voltooit.

```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
```

## Ze vermijden

- **Consistente lock-volgorde** — raak rijen altijd in dezelfde volgorde aan (bijv. oplopende id). Het voorbeeld deadlockt alleen omdat de twee transacties 1→2 en 2→1 locken.
- **Houd transacties kort** — verwerf locks laat, commit snel, houd geen locks vast over netwerk-/gebruikerswachttijden.
- **Lock het minimum** — indexeer je `WHERE` zodat InnoDB specifieke rijen lockt, niet ranges of (zonder index) effectief elke gescande rij.
- **Retry bij 1213** — deadlocks zijn normaal onder contentie; wikkel de transactie in een retry op applicatieniveau.

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

## Waarom het belangrijk is

Onder echte concurrency zijn lock-contentie en deadlocks het verschil tussen een database die schaalt en een die vastloopt. Weten dat readers MVCC gebruiken (zodat ze niet blokkeren), dat deadlocks verwacht en herprobeerbaar zijn, en dat consistente lock-volgorde de meeste ervan voorkomt, is precies wat senior on-call-werk vereist.

Deadlocks

Ze vermijden

Waarom het belangrijk is

Gerelateerde vragen