Kako delujejo zaklepi vrstic in MVCC v InnoDB ter kako se izognete mrtvim zankam (deadlock)?

Question

IT Interview Practice · Accepted Answer

InnoDB združuje dva mehanizma. **MVCC** omogoča, da navadni `SELECT`-i berejo konsistenten posnetek brez zaklepanja, tako da bralci nikoli ne blokirajo piscev. **Zaklepi** ščitijo pisanja: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` prevzamejo zaklepe vrstic, InnoDB pa uporablja tudi **next-key locks** (vrstica + vrzel pred njo), da pod REPEATABLE READ ustavi fantome.

## Mrtve zanke

Mrtva zanka je cikel: T1 drži zaklep A in želi B, medtem ko T2 drži B in želi A. Nobena ne more nadaljevati. InnoDB **zazna cikel in razveljavi eno transakcijo** (cenejšo žrtev) z napako 1213, tako da druga zaključi.

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

## Izogibanje

- **Konsistenten vrstni red zaklepanja** — vrstic se vedno dotikajte v istem vrstnem redu (npr. po naraščajočem id). Zgornji primer se zaklene v mrtvo zanko samo zato, ker transakciji zaklepata 1→2 in 2→1.
- **Ohranite transakcije kratke** — zaklepe prevzemite pozno, hitro potrdite, ne držite zaklepov čez čakanja na omrežje/uporabnika.
- **Zaklenite minimum** — indeksirajte svoj `WHERE`, da InnoDB zaklene specifične vrstice, ne razponov ali (brez indeksa) dejansko vsake pregledane vrstice.
- **Ponovite ob 1213** — mrtve zanke so pod tekmovanjem normalne; transakcijo ovijte v ponovitev na ravni aplikacije.

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

## Zakaj je to pomembno

Pod resnično sočasnostjo sta tekmovanje za zaklepe in mrtve zanke razlika med podatkovno bazo, ki se dobro razširja, in tisto, ki se ustavlja. Vedenje, da bralci uporabljajo MVCC (zato ne blokirajo), da so mrtve zanke pričakovane in ponovljive ter da konsistenten vrstni red zaklepanja prepreči večino njih, je točno to, kar zahteva dežurno delo starejšega inženirja.

Mrtve zanke

Izogibanje

Zakaj je to pomembno

Povezana vprašanja