Row lock và MVCC trong InnoDB hoạt động ra sao, và làm sao tránh deadlock?

Question

IT Interview Practice · Accepted Answer

InnoDB kết hợp hai cơ chế. **MVCC** cho các `SELECT` thường đọc một snapshot nhất quán mà không cần lock, nên reader không bao giờ block writer. **Lock** bảo vệ ghi: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` lấy row lock, và InnoDB còn dùng **next-key lock** (row + khoảng trống trước nó) để chặn phantom dưới REPEATABLE READ.

## Deadlock

Deadlock là một vòng lặp: T1 giữ lock A và muốn B, trong khi T2 giữ B và muốn A. Không bên nào tiến được. InnoDB **phát hiện vòng lặp và roll back một transaction** (nạn nhân rẻ hơn) với lỗi 1213, để bên kia hoàn tất.

```sql
-- T1                              -- T2
BEGIN;                             BEGIN;
UPDATE acct SET bal=bal-1          UPDATE acct SET bal=bal-1
  WHERE id=1;  -- lock row 1         WHERE id=2;  -- lock row 2
UPDATE acct SET bal=bal+1          UPDATE acct SET bal=bal+1
  WHERE id=2;  -- chờ T2             WHERE id=1;  -- chờ T1 => DEADLOCK
-- ERROR 1213: Deadlock found; transaction rolled back
```

## Cách tránh

- **Thứ tự lock nhất quán** — luôn chạm row theo cùng thứ tự (vd. id tăng dần). Ví dụ trên deadlock chỉ vì hai transaction lock 1→2 và 2→1.
- **Giữ transaction ngắn** — lấy lock muộn, commit nhanh, đừng giữ lock qua các lần chờ network/user.
- **Lock tối thiểu** — index cho `WHERE` để InnoDB lock đúng row cụ thể, không lock cả range hay (nếu không có index) thực chất mọi row bị quét.
- **Retry khi gặp 1213** — deadlock là bình thường khi tranh chấp; bọc transaction trong một cơ chế retry ở tầng ứng dụng.

```sql
SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK giải thích ai chờ cái gì
```

## Tại sao điều này quan trọng

Dưới concurrency thực, tranh chấp lock và deadlock là khác biệt giữa một database mở rộng được và một database đứng khựng. Biết rằng reader dùng MVCC (nên không block), rằng deadlock là điều dự kiến và retry được, và rằng thứ tự lock nhất quán ngăn được phần lớn chúng — đó chính là điều công việc on-call cấp senior đòi hỏi.

Deadlock

Cách tránh

Tại sao điều này quan trọng

Câu hỏi liên quan