Bagaimana cara kerja row lock dan MVCC di InnoDB, dan bagaimana Anda menghindari deadlock?

Question

IT Interview Practice · Accepted Answer

InnoDB menggabungkan dua mekanisme. **MVCC** memungkinkan `SELECT` biasa membaca snapshot yang konsisten tanpa mengunci, sehingga pembaca tidak pernah memblokir penulis. **Lock** melindungi penulisan: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` mengambil row lock, dan InnoDB juga menggunakan **next-key lock** (baris + gap sebelumnya) untuk menghentikan phantom di bawah REPEATABLE READ.

## Deadlock

Deadlock adalah sebuah siklus: T1 memegang lock A dan menginginkan B, sementara T2 memegang B dan menginginkan A. Tidak satu pun dapat melanjutkan. InnoDB **mendeteksi siklus itu dan me-roll back satu transaction** (korban yang lebih murah) dengan error 1213, sehingga yang lain selesai.

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

## Menghindarinya

- **Urutan lock yang konsisten** — selalu sentuh baris dalam urutan yang sama (misalnya id menaik). Contoh di atas deadlock hanya karena kedua transaction mengunci 1→2 dan 2→1.
- **Jaga transaction tetap pendek** — akuisisi lock terlambat, commit cepat, jangan tahan lock melintasi tunggu jaringan/pengguna.
- **Kunci seminimal mungkin** — buat index pada `WHERE` Anda sehingga InnoDB mengunci baris tertentu, bukan range atau (tanpa index) praktis setiap baris yang dipindai.
- **Retry pada 1213** — deadlock adalah hal normal di bawah contention; bungkus transaction dalam retry level aplikasi.

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

## Mengapa ini penting

Di bawah concurrency nyata, lock contention dan deadlock adalah perbedaan antara database yang scalable dan yang macet. Mengetahui bahwa pembaca menggunakan MVCC (sehingga mereka tidak memblokir), bahwa deadlock diperkirakan dan dapat di-retry, dan bahwa urutan lock yang konsisten mencegah sebagian besarnya adalah persis yang dituntut oleh pekerjaan on-call senior.

Deadlock

Menghindarinya

Mengapa ini penting

Pertanyaan terkait