Locking

Isolation level dari chapter sebelumnya menjelaskan apa yang terlihat oleh transaksi yang berjalan bersamaan; locking menjelaskan apa yang boleh dikerjakan. Setiap kali InnoDB mengubah sebuah baris, baris itu dikunci khusus untuk transaksi tersebut sampai transaksi ditutup — kunci itulah yang membuat dua aplikasi tidak bisa menulis saldo yang sama di saat bersamaan. Pada chapter ini kita amati row lock yang dipasang secara otomatis, gunakan SELECT ... FOR UPDATE dan FOR SHARE untuk mengunci sebelum membaca, jalankan LOCK TABLES level tabel beserta mode READ dan WRITE, lalu pahami gap lock yang menjaga rentang antar-baris. Seperti biasa, efek pada koneksi lain kita jelaskan lewat prosa karena latihan kita berjalan dari satu terminal.

Menyiapkan Tabel Latihan #

Dua tabel yang saling terikat foreign key kembali dipakai — pasangan ini penting karena perilaku kunci tabel berbeda ketika FK ikut bermain:

sql
DROP DATABASE IF EXISTS belajar_trx;
CREATE DATABASE belajar_trx;
USE belajar_trx;
CREATE TABLE rekening (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pemilik VARCHAR(50) NOT NULL,
  saldo DECIMAL(12,2) NOT NULL
);
CREATE TABLE mutasi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rekening_id INT NOT NULL,
  jumlah DECIMAL(12,2) NOT NULL,
  keterangan VARCHAR(100) NOT NULL,
  tanggal DATE NOT NULL,
  CONSTRAINT fk_mutasi_rekening FOREIGN KEY (rekening_id) REFERENCES rekening(id)
);
INSERT INTO rekening (pemilik, saldo) VALUES ('Budi', 500000.00), ('Siti', 300000.00);
SELECT id, pemilik, saldo FROM rekening ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

+----+---------+-----------+
| id | pemilik | saldo     |
+----+---------+-----------+
|  1 | Budi    | 500000.00 |
|  2 | Siti    | 300000.00 |
+----+---------+-----------+
2 rows in set (0.00 sec)

Row Lock: UPDATE Mengunci Baris yang Disentuhnya #

Kunci level baris (row lock) dipasang otomatis oleh statement DML yang berjalan di dalam transaksi. Pola kerjanya sederhana: UPDATE mengunci setiap baris yang diubahnya, kunci bertahan sampai COMMIT atau ROLLBACK, dan transaksi lain yang ikut mengubah baris yang sama harus menunggu. Kita amati siklusnya di satu koneksi:

sql
START TRANSACTION;
UPDATE rekening SET saldo = saldo - 50000 WHERE id = 1;
UPDATE rekening SET saldo = saldo + 50000 WHERE id = 2;
COMMIT;
SELECT id, pemilik, saldo FROM rekening ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+----+---------+-----------+
| id | pemilik | saldo     |
+----+---------+-----------+
|  1 | Budi    | 450000.00 |
|  2 | Siti    | 350000.00 |
+----+---------+-----------+
2 rows in set (0.00 sec)

Selama transaksi di atas masih terbuka, baris id 1 dan id 2 memegang exclusive lock milik koneksi kita. Karena latihan ini berjalan dari satu terminal, efek pada koneksi lain perlu kita lukiskan: bila koneksi kedua menjalankan UPDATE ... WHERE id = 1 di saat yang sama, statement itu menggantung — tidak error, hanya menunggu — sampai transaksi pertama ditutup. InnoDB memberi batas waktu tunggu melalui variabel innodb_lock_wait_timeout yang bawaannya 50 detik; lewat dari itu, koneksi penunggu menerima ERROR 1205 (HY000): Lock wait timeout exceeded dan statement-nya gagal. Kuncian yang sempit inilah keunggulan InnoDB: hanya baris yang diperebutkan yang antre, sisanya bebas.

SELECT FOR UPDATE: Mengunci Baris Sebelum Membaca #

Masalah muncul saat kita perlu membaca dulu, memutuskan, baru menulis — contohnya memeriksa saldo sebelum memotongnya. Bacaan biasa tidak memasang kunci apa pun, sehingga transaksi lain bisa menyelinap mengubah saldo di antara bacaan dan penulisan kita. SELECT ... FOR UPDATE menutup celah itu: baris yang dibaca langsung terkunci eksklusif seakan sudah kita ubah:

sql
START TRANSACTION;
SELECT saldo FROM rekening WHERE id = 1 FOR UPDATE;
UPDATE rekening SET saldo = saldo - 100000 WHERE id = 1;
UPDATE rekening SET saldo = saldo + 100000 WHERE id = 2;
COMMIT;
SELECT id, pemilik, saldo FROM rekening ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+-----------+
| saldo     |
+-----------+
| 450000.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+----+---------+-----------+
| id | pemilik | saldo     |
+----+---------+-----------+
|  1 | Budi    | 350000.00 |
|  2 | Siti    | 450000.00 |
+----+---------+-----------+
2 rows in set (0.00 sec)

Bacaan FOR UPDATE mengembalikan saldo Rp450.000,00 sekaligus memasang kunci pada baris id 1 — dari titik itu, koneksi lain tidak bisa mengubah baris tersebut maupun menguncinya dengan FOR UPDATE mereka sendiri sampai transaksi kita berakhir dengan COMMIT. Transfer pun berjalan: saldo akhir Rp350.000,00 dan Rp450.000,00. Padanan yang lebih lunak tersedia untuk kunci bersama: FOR SHARE (dan bentuk lamanya LOCK IN SHARE MODE) memasang kunci baca yang boleh dipegang banyak koneksi sekaligus:

sql
START TRANSACTION;
SELECT saldo FROM rekening WHERE id = 1 FOR SHARE;
SELECT saldo FROM rekening WHERE id = 1 LOCK IN SHARE MODE;
COMMIT;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+-----------+
| saldo     |
+-----------+
| 350000.00 |
+-----------+
1 row in set (0.00 sec)

+-----------+
| saldo     |
+-----------+
| 350000.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Kedua bacaan mengembalikan saldo yang sama karena FOR SHARE hanya menjaga baris dari perubahan — koneksi lain boleh ikut membaca atau memegang kunci share yang sama, tetapi tidak boleh mengubah baris itu atau memintanya dengan FOR UPDATE sampai semua pemegang kunci share melepaskan. Pola ini cocok untuk validasi lintas-tabel: banyak pembaca boleh memeriksa data yang sama, penulis tunggal menunggu mereka selesai.

LOCK TABLES: Kunci Level Tabel #

Untuk kasus yang benar-benar membutuhkan tabel penuh — misalnya pemeliharaan admin — MySQL menyediakan LOCK TABLES dengan dua mode: READ yang membolehkan semua orang membaca tetapi melarang semua penulisan termasuk oleh pemegang kunci, dan WRITE yang memberi pemegangnya kendali penuh sambil menutup akses koneksi lain. Kunci tabel mengunci seluruh isi tabel sekaligus:

sql
LOCK TABLES rekening WRITE, mutasi WRITE;
UPDATE rekening SET saldo = saldo + 25000 WHERE id = 1;
INSERT INTO mutasi (rekening_id, jumlah, keterangan, tanggal) VALUES (1, 25000.00, 'Bunga', '2026-01-15');
UNLOCK TABLES;
SELECT saldo FROM rekening WHERE id = 1;
SELECT COUNT(*) AS jumlah_mutasi FROM mutasi;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-----------+
| saldo     |
+-----------+
| 375000.00 |
+-----------+
1 row in set (0.00 sec)

+---------------+
| jumlah_mutasi |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

Kunci WRITE pada kedua tabel membuat pembaruan saldo dan pencatatan bunga berjalan tanpa gangguan, lalu UNLOCK TABLES melepaskan semuanya — saldo kini Rp375.000,00 dan mutasi tercatat. Perhatikan bahwa kedua tabel kita kunci sekaligus: saat LOCK TABLES aktif, sesi tersebut hanya boleh menyentuh tabel yang disebutkan, dan karena mutasi terikat foreign key ke rekening, mengunci salah satunya saja akan membuat statement pada pasangannya ditolak. Mode READ bisa kita buktikan langsung — pemegangnya sendiri pun tidak boleh menulis:

sql
-- @error
LOCK TABLES rekening READ, mutasi READ;
UPDATE rekening SET saldo = saldo + 15000 WHERE id = 1;
UNLOCK TABLES;
SELECT saldo FROM rekening WHERE id = 1;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1099 (HY000): Table 'rekening' was locked with a READ lock and can't be updated
Query OK, 0 rows affected (0.00 sec)

+-----------+
| saldo     |
+-----------+
| 375000.00 |
+-----------+
1 row in set (0.00 sec)

ERROR 1099 menegaskan aturan mode READ: tabel yang dikunci baca “can’t be updated” — bahkan oleh koneksi yang memegang kuncinya. UNLOCK TABLES membersihkan keadaan dan saldo tetap Rp375.000,00 karena UPDATE tidak pernah berjalan. Bila kita hanya mengunci rekening WRITE lalu mencoba menyisipkan mutasi, server justru menolak dengan ERROR 1100 (HY000): Table 'mutasi' was not locked with LOCK TABLES — pengingat bahwa semua tabel yang akan disentuh, termasuk pasangan foreign key-nya, wajib ikut dikunci.

Gap Lock: Menjaga Rentang Antar-Baris #

Kunci baris menjaga baris yang sudah ada; gap lock menjaga ruang di antara baris yang belum ada. Bayangkan tabel rekening berisi baris id 1 dan id 5: transaksi yang menjalankan SELECT ... WHERE id BETWEEN 1 AND 5 FOR UPDATE tidak hanya mengunci kedua baris itu, tetapi juga celah id 2 hingga 4 — sehingga transaksi lain tidak bisa menyisipkan rekening baru ke tengah rentang itu sampai kunci dilepas. Gabungan kunci baris dan gap lock disebut next-key lock, dan inilah senjata InnoDB untuk mencegah phantom read pada level REPEATABLE READ yang kita bahas di chapter sebelumnya. Gap lock hanya dipasang pada pembacaan yang mengunci (FOR UPDATE/FOR SHARE) dan rentan menurunkan konkurensi bila rentang yang dikunci lebar — kunci serapat mungkin lewat kondisi yang presisi, idealnya berbasis index.

Shared vs Exclusive: Dua Sifat Dasar Kunci #

Seluruh kebun kunci MySQL tumbuh dari dua sifat: kunci shared yang bisa dipegang banyak pemegang sekaligus, dan kunci exclusive yang hanya boleh satu. Tabel berikut merangkum padanannya di tiap lapisan:

Lapisan	Bentuk shared	Bentuk exclusive	Yang boleh dilakukan pemegang lain
Baris	`SELECT ... FOR SHARE`	`SELECT ... FOR UPDATE`, `UPDATE`, `DELETE`	Shared: bisa ikut membaca; Exclusive: semua menunggu
Tabel	`LOCK TABLES ... READ`	`LOCK TABLES ... WRITE`	Read: semua bisa membaca; Write: semua menunggu

Aturan kombinasinya konsisten di semua lapisan: dua kunci shared boleh berbagi, kunci exclusive selalu menuntut ruang sendiri, dan konflik berakhir dengan antrean tunggu sampai pemegang melepas. Perbedaannya hanya cakupan — kunci baris mengantrekan transaksi yang menyentuh baris yang sama, kunci tabel mengantrekan semua pekerjaan pada tabel itu.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

sql
DROP DATABASE belajar_trx;

Output yang Dihasilkan:

text

Query OK, 2 rows affected (0.00 sec)

Referensi #

Peta kunci kini lengkap dari lapisan baris sampai tabel: row lock otomatis dari statement DML, FOR UPDATE dan FOR SHARE untuk mengunci sebelum memutuskan, LOCK TABLES untuk penguncian menyeluruh, plus gap lock penjaga rentang. Namun kunci selalu punya sisi gelap: ketika dua transaksi memegang kunci yang dibutuhkan satu sama lain, keduanya diam saling menunggu selamanya — kebuntuan bernama deadlock. Chapter berikutnya menyimulasikan kebuntuan klasik dua transfer yang saling silang, membaca jejak deteksinya, dan merumuskan pola pencegahannya.