Row locks na MVCC zinafanyaje kazi katika InnoDB, na unaepukaje deadlocks?

Question

IT Interview Practice · Accepted Answer

InnoDB inachanganya mifumo miwili. **MVCC** inaruhusu `SELECT`s za kawaida kusoma snapshot thabiti bila locking, hivyo readers kamwe hazizuii writers. **Locks** zinalinda writes: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` huchukua row locks, na InnoDB pia inatumia **next-key locks** (row + gap kabla yake) kuzuia phantoms chini ya REPEATABLE READ.

## Deadlocks

Deadlock ni mzunguko: T1 inashikilia lock A na inataka B, wakati T2 inashikilia B na inataka A. Hakuna inayoweza kuendelea. InnoDB **hugundua mzunguko na kufanya rollback ya transaction moja** (mwathirika wa bei nafuu zaidi) kwa error 1213, hivyo nyingine inakamilika.

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

## Kuziepuka

- **Consistent lock ordering** — daima gusa rows kwa mpangilio ule ule (mfano id inayopanda). Mfano una deadlock tu kwa sababu transactions mbili zina-lock 1→2 na 2→1.
- **Weka transactions fupi** — chukua locks kuchelewa, commit haraka, usishikilie locks wakati wa kusubiri network/mtumiaji.
- **Lock kiwango cha chini** — weka index kwenye `WHERE` yako ili InnoDB i-lock rows mahususi, siyo ranges au (bila index) kwa uhalisia kila row iliyochanganuliwa.
- **Retry kwenye 1213** — deadlocks ni za kawaida chini ya contention; zungushia transaction kwa retry ya kiwango cha programu.

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

## Kwa nini ni muhimu

Chini ya concurrency halisi, lock contention na deadlocks ndio tofauti kati ya database inayoongezeka (scale) na ile inayokwama. Kujua kwamba readers hutumia MVCC (hivyo hazizuii), kwamba deadlocks zinatarajiwa na zinaweza kufanyiwa retry, na kwamba consistent lock ordering huzuia nyingi kati yao ndicho hasa kazi ya on-call ya senior inachohitaji.

Deadlocks

Kuziepuka

Kwa nini ni muhimu

Maswali yanayohusiana