row lock และ MVCC ใน InnoDB ทำงานอย่างไร และหลีกเลี่ยง deadlock อย่างไร?

Question

IT Interview Practice · Accepted Answer

InnoDB ผสานสองกลไกเข้าด้วยกัน **MVCC** ทำให้ `SELECT` ธรรมดาอ่านจาก snapshot ที่คงเส้นคงวาได้โดยไม่ต้องล็อก ผู้อ่านจึงไม่เคยบล็อกผู้เขียน **Lock** ปกป้องการเขียน: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` จะจับ row lock และ InnoDB ยังใช้ **next-key lock** (แถว + ช่องว่างก่อนหน้ามัน) เพื่อหยุด phantom ภายใต้ REPEATABLE READ

## Deadlock

Deadlock คือวงจร: T1 ถือ lock A และต้องการ B ขณะที่ T2 ถือ B และต้องการ A ไม่มีฝ่ายใดเดินหน้าได้ InnoDB **ตรวจพบวงจรและ roll back หนึ่ง transaction** (เหยื่อที่ต้นทุนถูกกว่า) พร้อม error 1213 เพื่อให้อีกฝ่ายทำงานเสร็จ

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

## การหลีกเลี่ยง

- **ลำดับการล็อกที่สอดคล้องกัน** — แตะแถวในลำดับเดียวกันเสมอ (เช่น เรียง id จากน้อยไปมาก) ตัวอย่างข้างต้นเกิด deadlock เพียงเพราะสอง transaction ล็อกแบบ 1→2 และ 2→1
- **ทำให้ transaction สั้น** — จับ lock ให้ช้า commit ให้เร็ว อย่าถือ lock ค้างไว้ระหว่างรอ network/ผู้ใช้
- **ล็อกให้น้อยที่สุด** — ทำ index ให้ `WHERE` เพื่อให้ InnoDB ล็อกแถวเฉพาะจุด ไม่ใช่ช่วง (range) หรือ (เมื่อไม่มี index) แทบทุกแถวที่ถูกสแกน
- **Retry เมื่อเจอ 1213** — deadlock เป็นเรื่องปกติเมื่อมีการแย่งชิง; ให้ห่อ transaction ไว้ด้วยกลไก retry ระดับแอปพลิเคชัน

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

## ทำไมจึงสำคัญ

ภายใต้การทำงานพร้อมกันจริง การแย่งชิง lock และ deadlock คือความต่างระหว่างฐานข้อมูลที่ขยายได้กับที่ค้าง การรู้ว่าผู้อ่านใช้ MVCC (จึงไม่บล็อก) ว่า deadlock เป็นเรื่องที่คาดหมายได้และ retry ได้ และว่าลำดับการล็อกที่สอดคล้องกันช่วยป้องกันได้เกือบทั้งหมด คือสิ่งที่งาน on-call ระดับ senior ต้องการพอดี

Deadlock

การหลีกเลี่ยง

ทำไมจึงสำคัญ

คำถามที่เกี่ยวข้อง