Ta yaya row locks da MVCC ke aiki a InnoDB, kuma ta yaya kake gujewa deadlocks?

Question

IT Interview Practice · Accepted Answer

InnoDB yana haɗa hanyoyi biyu. **MVCC** yana ba da damar `SELECT`s masu sauƙi su karanta snapshot mai daidaici ba tare da kullewa ba, don haka masu karatu ba sa toshe masu rubutu. **Locks** suna kare rubutu: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` suna ɗauka row locks, kuma InnoDB ma yana amfani da **next-key locks** (row + gaɓar da ke gabaninsa) don dakatar da phantoms a ƙarƙashin REPEATABLE READ.

## Deadlocks

Deadlock zagaye ne: T1 yana riƙe lock A kuma yana son B, yayin da T2 ke riƙe B kuma yana son A. Babu wanda zai iya ci gaba. InnoDB **yana gano zagayen kuma yana roll back transaction ɗaya** (wanda ya fi arha) tare da kuskure 1213, don haka ɗayan ya cika.

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

## Gujewa su

- **Tsari mai daidaici na lock** — ko da yaushe taɓa rows a tsari ɗaya (misali ascending id). Misalin yana deadlock kawai saboda transactions biyu suna lock 1→2 da 2→1.
- **Riƙe transactions gajeru** — ɗauka locks a makara, commit da sauri, kada ka riƙe locks a kan jiran network/mai amfani.
- **Lock mafi ƙanƙanci** — yi index ga `WHERE` ɗinka domin InnoDB ya lock takamaiman rows, ba ranges ba ko (ba tare da index ba) kusan kowace row da aka scan.
- **Retry a kan 1213** — deadlocks na al'ada ne a ƙarƙashin takara; nannaɗe transaction a cikin retry na matakin manhaja.

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

## Me ya sa yake da muhimmanci

A ƙarƙashin concurrency na gaske, takara na lock da deadlocks su ne bambanci tsakanin database da ke scale da wanda ke tsayawa. Sanin cewa masu karatu suna amfani da MVCC (don haka ba sa toshewa), cewa ana sa ran deadlocks kuma ana iya retry ɗinsu, da cewa tsari mai daidaici na lock yana hana yawancinsu shine daidai abin da aikin senior on-call ke buƙata.

Deadlocks

Gujewa su

Me ya sa yake da muhimmanci

Tambayoyi masu alaƙa