Jak działają blokady wierszy i MVCC w InnoDB oraz jak unikać zakleszczeń?

Question

IT Interview Practice · Accepted Answer

InnoDB łączy dwa mechanizmy. **MVCC** pozwala zwykłym `SELECT`-om odczytywać spójną migawkę bez blokowania, więc czytający nigdy nie blokują zapisujących. **Blokady** chronią zapisy: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` zakładają blokady wierszy, a InnoDB używa też **blokad next-key** (wiersz + poprzedzająca go luka), by powstrzymać odczyty fantomowe przy REPEATABLE READ.

## Zakleszczenia

Zakleszczenie to cykl: T1 trzyma blokadę A i chce B, podczas gdy T2 trzyma B i chce A. Żadna nie może się posunąć naprzód. InnoDB **wykrywa cykl i wycofuje jedną transakcję** (tańszą ofiarę) z błędem 1213, tak że druga się kończy.

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

## Jak ich unikać

- **Spójna kolejność blokowania** — zawsze dotykaj wierszy w tej samej kolejności (np. rosnące id). Przykład zakleszcza się tylko dlatego, że dwie transakcje blokują 1→2 i 2→1.
- **Utrzymuj transakcje krótkie** — zakładaj blokady późno, zatwierdzaj szybko, nie trzymaj blokad podczas oczekiwania na sieć/użytkownika.
- **Blokuj minimum** — zaindeksuj swoje `WHERE`, aby InnoDB blokował konkretne wiersze, a nie zakresy lub (bez indeksu) w praktyce każdy przeskanowany wiersz.
- **Ponawiaj przy 1213** — zakleszczenia są normalne pod rywalizacją; opakuj transakcję w ponawianie na poziomie aplikacji.

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

## Dlaczego to ważne

Pod rzeczywistą współbieżnością rywalizacja o blokady i zakleszczenia to różnica między bazą, która się skaluje, a taką, która się zatyka. Świadomość, że czytający używają MVCC (więc nie blokują), że zakleszczenia są oczekiwane i można je ponawiać oraz że spójna kolejność blokowania zapobiega większości z nich, to dokładnie to, czego wymaga senioryjna praca on-call.

Zakleszczenia

Jak ich unikać

Dlaczego to ważne

Powiązane pytania