How do row locks and MVCC work in InnoDB, and how do you avoid deadlocks?

Question

IT Interview Practice · Accepted Answer

InnoDB రెండు mechanisms ను కలుపుతుంది. **MVCC** సాధారణ `SELECT`లను locking లేకుండా ఒక consistent snapshot చదవడానికి అనుమతిస్తుంది, కాబట్టి readers ఎప్పుడూ writers ను block చేయవు. **Locks** writes ను రక్షిస్తాయి: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` row locks ను తీసుకుంటాయి, మరియు REPEATABLE READ కింద phantoms ను ఆపడానికి InnoDB **next-key locks** (row + దాని ముందు ఉన్న gap) ను కూడా ఉపయోగిస్తుంది.

## Deadlocks

deadlock అనేది ఒక చక్రం: T1 lock A ను పట్టుకుని B ను కోరుకుంటుంది, అదే సమయంలో T2 B ను పట్టుకుని A ను కోరుకుంటుంది. ఏదీ ముందుకు సాగలేదు. InnoDB error 1213 తో **చక్రాన్ని గుర్తించి ఒక transaction ను (చౌకైన victim) roll back చేస్తుంది**, కాబట్టి మరొకటి పూర్తవుతుంది.

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

## వాటిని నివారించడం

- **Consistent lock ordering** — ఎల్లప్పుడూ rows ను ఒకే క్రమంలో తాకండి (ఉదా. ascending id). ఉదాహరణ deadlock అవుతుంది ఎందుకంటే రెండు transactions 1→2 మరియు 2→1 ను lock చేస్తాయి.
- **transactions ను short గా ఉంచండి** — locks ను ఆలస్యంగా acquire చేయండి, వేగంగా commit చేయండి, network/user waits అంతటా locks ను పట్టుకోవద్దు.
- **కనిష్టాన్ని lock చేయండి** — మీ `WHERE` ను index చేయండి, తద్వారా InnoDB నిర్దిష్ట rows ను lock చేస్తుంది, ranges ను కాదు లేదా (index లేకుండా) సమర్థవంతంగా scan చేయబడిన ప్రతి row ను కాదు.
- **1213 పై retry చేయండి** — contention కింద deadlocks సాధారణం; transaction ను application-level retry లో wrap చేయండి.

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

## ఇది ఎందుకు ముఖ్యం

వాస్తవ concurrency కింద, lock contention మరియు deadlocks అనేవి scale అయ్యే database కు మరియు నిలిచిపోయే దానికి మధ్య తేడా. readers MVCC ను ఉపయోగిస్తారని (కాబట్టి అవి block అవ్వవు), deadlocks అనేవి ఊహించదగినవి మరియు retry చేయదగినవి అని, మరియు consistent lock ordering వాటిలో చాలా వరకు నివారిస్తుందని తెలుసుకోవడం సరిగ్గా senior on-call పని కోరేదే.

Deadlocks

వాటిని నివారించడం

ఇది ఎందుకు ముఖ్యం

సంబంధిత ప్రశ్నలు