KAL
Menu

Penguncian

Lock baris, gap lock, deadlock, dan cara mengamati serta menanganinya.

Daftar isi

Penguncian adalah mekanisme yang menjaga dua transaksi tidak menulis baris yang sama pada saat yang sama, dan InnoDB melakukannya pada tingkat baris dengan tambahan kunci untuk celah antarbaris. Topik ini membahas jenis-jenis lock baris, cara mengamatinya melalui performance_schema, perilaku menunggu yang berujung pada error lock wait timeout, sampai deadlock beserta penanganannya. Setelah membacanya, penyebab bentrok antartransaksi dapat dibaca dari tabel sistem, bukan hanya ditebak dari pesan error.

Dua jenis lock baris#

InnoDB membedakan lock berdasarkan tujuannya. Lock eksklusif (exclusive lock atau lock X) diberikan pada pernyataan yang mengubah data, dan hanya satu transaksi yang boleh memegangnya pada satu baris. Lock bersama (shared lock atau lock S) diberikan pada pembacaan yang meminta konsistensi kuat dengan SELECT ... FOR SHARE, dan beberapa transaksi boleh memegangnya sekaligus karena membaca tidak saling mengganggu. Permintaan pembacaan yang kuat ditulis dengan FOR SHARE, sedangkan FOR UPDATE meminta lock eksklusif untuk baris yang dibaca, sebagaimana diperlihatkan blok berikut.

sql Sintaks pembacaan dengan lock 
-- tidak-dijalankan
SELECT * FROM nama_tabel WHERE id = 1 FOR SHARE;
SELECT * FROM nama_tabel WHERE id = 1 FOR UPDATE;

Selain lock pada baris, InnoDB mencatat maksud mengunci melalui lock tabel khusus yang disebut intention lock, yaitu IS untuk maksud membaca dan IX untuk maksud mengubah. Lock ini tidak menghalangi baris lain, melainkan memberi tahu InnoDB bahwa suatu transaksi sedang menandai baris di dalam tabel. Basis data latihan modul ini dibuat ulang oleh skema berikut.

sql skema-perpustakaan.sql 
-- tidak-dijalankan
DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE buku (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  judul   VARCHAR(120) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  stok    TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE anggota (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE peminjaman (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT UNSIGNED NOT NULL,
  buku_id    INT UNSIGNED NOT NULL,
  jumlah     TINYINT UNSIGNED NOT NULL DEFAULT 1,
  FOREIGN KEY (anggota_id) REFERENCES anggota (id),
  FOREIGN KEY (buku_id)    REFERENCES buku (id)
) ENGINE=InnoDB;

INSERT INTO buku (judul, penulis, stok) VALUES
  ('Dasar Basis Data Relasional', 'Dewi Sartika',  5),
  ('Struktur Data dan Algoritma', 'Ali Rahman',    3),
  ('Jaringan Komputer Dasar',     'Budi Hartono',  4),
  ('Pemrograman SQL Praktis',     'Citra Lestari', 6),
  ('Sistem Operasi Modern',       'Rina Kartika',  2);

INSERT INTO anggota (nama) VALUES ('Ali'), ('Budi'), ('Citra'), ('Dewi');
bash 
# membuat ulang basis data perpustakaan beserta isinya
mysql -u root < skema-perpustakaan.sql

Mengamati lock dengan data_locks#

Tabel performance_schema.data_locks mencatat setiap lock yang sedang dipegang pada server, lengkap dengan mode, jenis, dan datanya. Amati lock yang dipegang sebuah transaksi yang mengurangi stok buku pertama dengan menjalankan pemeriksaan sebelum transaksinya di-COMMIT.

bash 
# mengunci satu baris dengan UPDATE lalu membaca daftar lock-nya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
UPDATE buku SET stok = stok - 1 WHERE id = 1;
SELECT LOCK_MODE, LOCK_TYPE, INDEX_NAME, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'buku'
ORDER BY LOCK_TYPE, LOCK_DATA;
COMMIT;
SQL
text Keluaran 
+---------------+-----------+------------+-----------+
| LOCK_MODE     | LOCK_TYPE | INDEX_NAME | LOCK_DATA |
+---------------+-----------+------------+-----------+
| X,REC_NOT_GAP | RECORD    | PRIMARY    | 1         |
| IX            | TABLE     | NULL       | NULL      |
+---------------+-----------+------------+-----------+

Keluaran menampilkan dua lock yang dipegang transaksi tersebut. Baris pertama adalah lock X dengan sifat REC_NOT_GAP pada indeks PRIMARY untuk nilai kunci 1, yaitu lock eksklusif murni pada satu baris tanpa menyentuh celah di sekitarnya. Baris kedua adalah lock IX bertipe TABLE yang menyatakan maksud mengubah baris pada tabel buku. Susunan ini berlaku umum, yaitu satu intention lock pada tabel ditambah lock baris untuk setiap baris yang tersenggol.

Gap lock dan next-key lock#

Selain baris yang ada, REPEATABLE READ mengunci juga celah antarbaris agar baris baru tidak muncul di tengah rentang yang sedang dibaca. Kunci celah ini disebut gap lock, sedangkan gabungan lock baris dengan lock pada celah sebelum baris itu disebut next-key lock. Ciri khasnya dapat diamati dengan membuka transaksi yang membaca rentang kosong menggunakan FOR UPDATE.

bash 
# membaca rentang kosong dengan FOR UPDATE, lalu mengamati lock-nya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
SELECT * FROM buku WHERE id BETWEEN 10 AND 20 FOR UPDATE;
SELECT LOCK_MODE, LOCK_TYPE, INDEX_NAME, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'buku' AND LOCK_TYPE = 'RECORD'
ORDER BY LOCK_DATA;
COMMIT;
SQL
text Keluaran 
+-----------+-----------+------------+------------------------+
| LOCK_MODE | LOCK_TYPE | INDEX_NAME | LOCK_DATA              |
+-----------+-----------+------------+------------------------+
| X         | RECORD    | PRIMARY    | supremum pseudo-record |
+-----------+-----------+------------+------------------------+

Rentang antara id 10 dan 20 tidak berisi satu baris pun, tetapi pembacaan itu tetap mengunci, yaitu lock X pada supremum pseudo-record yang menandai celah setelah kunci tertinggi. Selama transaksi ini berjalan, penyisipan baris dengan id di atas nilai tertinggi akan menunggu, sehingga baris hantu tidak dapat muncul pada pembacaan berikutnya. Level READ COMMITTED tidak memasang gap lock untuk pembacaan biasa, sehingga perilaku ini hilang apabila level diubah.

Transaksi yang meminta lock yang sedang dipegang transaksi lain akan menunggu, dan lama menunggunya dibatasi variabel innodb_lock_wait_timeout dengan nilai bawaan lima puluh detik. Demonstrasi berikut membuat sesi latar belakang menahan lock X pada baris pertama selama empat detik, sedangkan sesi utama menunggu dengan batas dua detik sebelum menyerah.

bash 
# sesi A (latar belakang) menahan kunci X baris 1 selama empat detik
rm -f tunggu.out
( sleep 1; mysql -u root perpustakaan <<'SQL' >/dev/null
BEGIN;
SELECT stok FROM buku WHERE id = 1 FOR UPDATE;
SELECT SLEEP(4);
ROLLBACK;
SQL
) &

# sesi utama menunggu kunci dengan batas dua detik
mysql -t -u root perpustakaan <<'SQL' >tunggu.out 2>&1
SET SESSION innodb_lock_wait_timeout = 2;
SELECT SLEEP(2);
UPDATE buku SET stok = stok - 1 WHERE id = 1;
SQL
wait
cat tunggu.out
rm -f tunggu.out
text Keluaran 
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
ERROR 1205 (HY000) at line 3: Lock wait timeout exceeded; try restarting transaction

Sesi utama menunggu dua detik penuh, lalu server menghentikan pernyataannya dengan error 1205. Pesan tersebut menyuruh mencoba ulang karena kunci yang ditunggu mungkin sudah dilepas saat percobaan berikutnya. Error ini bukan kegagalan server, melainkan tanda bahwa desain transaksi menahan kunci terlalu lama di depan transaksi lain, dan variabel timeout dapat dinaikkan hanya bila lama menunggu memang diinginkan dalam desain tersebut.

Deadlock#

Deadlock terjadi ketika dua transaksi saling menunggu kunci yang masing-masing dipegang lawannya, sehingga tidak ada yang dapat maju. InnoDB mendeteksi kebuntuan secara otomatis, menggugurkan satu transaksi sebagai korban dengan error 1213, dan melepas kunci korban agar lawannya maju. Diagram berikut memperlihatkan urutan peristiwanya.

t1t2t3Sesi AUPDATE baris 1kunci X diperolehUPDATE baris 2menunggu kunciCOMMITberhasilmenunggu kunci baris 2menunggu kunci baris 1Sesi BUPDATE baris 2kunci X diperolehUPDATE baris 1menunggu kunciROLLBACKkorban deadlockInnoDB menggugurkan Sesi B, melepas kuncinya, dan membiarkan Sesi A menyelesaikan transaksinya
Linimasa deadlock dua sesi dan pengguguran korban

Demonstrasi berikut menghidupkan diagram tersebut. Sesi latar belakang mengunci baris dua lebih dahulu, sedangkan sesi utama mengunci baris satu lebih dahulu, lalu keduanya mencoba baris lawannya.

bash 
# sesi A (latar belakang) mengunci baris 2, lalu mencoba baris 1
rm -f sesi-b.out
( sleep 1; mysql -u root perpustakaan <<'SQL' >/dev/null 2>sesi-b.out
BEGIN;
UPDATE buku SET stok = stok + 1 WHERE id = 2;
SELECT SLEEP(2);
UPDATE buku SET stok = stok + 1 WHERE id = 1;
SQL
) &

# sesi utama mengunci baris 1, lalu mencoba baris 2
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
UPDATE buku SET stok = stok + 1 WHERE id = 1;
SELECT SLEEP(2);
UPDATE buku SET stok = stok + 1 WHERE id = 2;
COMMIT;
SELECT id, stok FROM buku ORDER BY id;
SQL
wait
cat sesi-b.out
rm -f sesi-b.out
text Keluaran 
+----------+
| SLEEP(2) |
+----------+
|        0 |
+----------+
+----+------+
| id | stok |
+----+------+
|  1 |    5 |
|  2 |    4 |
|  3 |    4 |
|  4 |    6 |
|  5 |    2 |
+----+------+
ERROR 1213 (40001) at line 4: Deadlock found when trying to get lock; try restarting transaction

Sesi utama menyelesaikan kedua perubahannya dan COMMIT, sedangkan sesi latar belakang menerima error 1213 dan transaksinya digulung balik seluruhnya, sehingga perubahan stok buku kedua yang dilakukan sesi itu dibatalkan. Korban dipilih InnoDB dari transaksi yang menulis lebih sedikit, bukan dari urutan permintaan. Penanganan yang benar di aplikasi adalah menangkap error 1213 lalu mengulang transaksi dari awal, karena kegagalan itu tidak meninggalkan perubahan apa pun. Kebiasaan yang menekan jumlah deadlock adalah mengunci baris dengan urutan yang konsisten di semua proses, misalnya selalu dari id kecil menuju id besar.

Kesalahan umum#

Kesalahan pertama adalah membiarkan transaksi terbuka sambil menunggu kerja aplikasi lain, misalnya menahan FOR UPDATE lalu memanggil layanan jaringan sebelum COMMIT. Seluruh kunci tetap dipegang selama menunggu, dan transaksi lain menumpuk di belakangnya sampai error 1205. Tutup transaksi sebelum melakukan pekerjaan yang lambat di luarnya.

Kesalahan kedua adalah menganggap error 1213 sebagai kegagalan yang harus dihindari dengan mematikan deteksi deadlock. Deteksi itu justru menyelamatkan basis data dari kebuntuan abadi, dan errornya adalah sinyal untuk mengulang transaksi yang digugurkan. Terapkan pola coba ulang dengan batas percobaan yang wajar.

Kesalahan ketiga adalah mengabaikan gap lock saat berpindah level isolasi. Pola yang aman pada REPEATABLE READ karena penyisipan tertahan oleh kunci celah dapat berubah perilaku pada READ COMMITTED. Sebaliknya, pola yang mengandalkan kecepatan READ COMMITTED menjadi lambat ketika dipindahkan ke REPEATABLE READ yang mengunci lebih banyak. Uji ulang perilaku antartransaksi setiap kali level isolasi berubah.

Ringkasan#

  • Lock X diperlukan untuk mengubah baris dan tidak boleh dibagi, sedangkan lock S untuk membaca dengan jaminan kuat dan dapat dibagi.
  • Setiap lock yang dipegang transaksi dapat dibaca dari performance_schema.data_locks, lengkap dengan mode dan barisnya.
  • REPEATABLE READ memasang gap lock dan next-key lock untuk menahan baris baru di dalam rentang yang sedang dibaca.
  • Menunggu kunci melewati innodb_lock_wait_timeout berakhir dengan error 1205, sedangkan kebuntuan silang berakhir dengan error 1213 dan pengguguran salah satu transaksi.

Referensi#