Come funzionano i row lock e MVCC in InnoDB, e come si evitano i deadlock?

Question

IT Interview Practice · Accepted Answer

InnoDB combina due meccanismi. **MVCC** permette alle `SELECT` semplici di leggere uno snapshot consistente senza fare lock, così i lettori non bloccano mai gli scrittori. I **lock** proteggono le scritture: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` prendono row lock, e InnoDB usa anche i **next-key lock** (la riga + il gap prima di essa) per fermare i phantom sotto REPEATABLE READ.

## Deadlock

Un deadlock è un ciclo: T1 detiene il lock A e vuole B, mentre T2 detiene B e vuole A. Nessuno dei due può procedere. InnoDB **rileva il ciclo e annulla una transazione** (la vittima meno costosa) con l'errore 1213, così l'altra si completa.

```sql
-- T1                              -- T2
BEGIN;                             BEGIN;
UPDATE acct SET bal=bal-1          UPDATE acct SET bal=bal-1
  WHERE id=1;  -- blocca la riga 1   WHERE id=2;  -- blocca la riga 2
UPDATE acct SET bal=bal+1          UPDATE acct SET bal=bal+1
  WHERE id=2;  -- aspetta T2          WHERE id=1;  -- aspetta T1 => DEADLOCK
-- ERROR 1213: Deadlock found; transaction rolled back
```

## Come evitarli

- **Ordine di lock coerente** — tocca sempre le righe nello stesso ordine (es. id crescente). L'esempio va in deadlock solo perché le due transazioni bloccano 1→2 e 2→1.
- **Mantieni le transazioni brevi** — acquisisci i lock tardi, committa in fretta, non mantenere i lock durante attese di rete/utente.
- **Blocca il minimo** — indicizza il tuo `WHERE` così InnoDB blocca righe specifiche, non intervalli o (senza un indice) di fatto ogni riga scandita.
- **Riprova sul 1213** — i deadlock sono normali sotto contesa; avvolgi la transazione in un retry a livello applicativo.

```sql
SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK spiega chi aspettava cosa
```

## Perché è importante

Sotto concorrenza reale, la contesa sui lock e i deadlock fanno la differenza tra un database che scala e uno che si blocca. Sapere che i lettori usano MVCC (quindi non bloccano), che i deadlock sono attesi e ripetibili, e che un ordine di lock coerente ne previene la maggior parte è esattamente ciò che richiede il lavoro on-call di un senior.

Deadlock

Come evitarli

Perché è importante

Domande correlate