Level Isolasi

READ COMMITTED, REPEATABLE READ bawaan MySQL, dan anomali yang terjadi di antaranya.

Daftar isi

Level isolasi menentukan seberapa terlihat perubahan dari transaksi lain pada sesi yang sedang membaca, baik perubahan yang belum ditetapkan maupun yang baru ditetapkan. Topik ini membahas empat level isolasi pada SQL beserta anomali baca yang diizinkan masing-masing, nilai bawaan MySQL, serta demonstrasi perbedaan perilaku READ COMMITTED dan REPEATABLE READ dengan dua sesi yang berjalan nyata. Setelah membacanya, pilihan level isolasi untuk sebuah beban kerja dapat diambil berdasarkan perilaku yang terukur.

Empat level dan anomali baca#

Standar SQL menetapkan empat level isolasi dari yang paling longgar sampai yang paling ketat. Anomali yang menjadi tolok ukurnya ada tiga. Dirty read (baca kotor) terjadi ketika sesi membaca perubahan yang belum di-COMMIT oleh sesi lain, padahal perubahan itu masih bisa dibatalkan. Nonrepeatable read (baca tak terulang) terjadi ketika dua pembacaan atas baris yang sama di dalam satu transaksi menghasilkan nilai berbeda karena sesi lain mengubahnya di antara keduanya. Phantom read (baris hantu) terjadi ketika pembacaan kedua menemukan baris baru yang memenuhi syarat pencarian yang sama.

Level isolasi	Baca kotor	Baca tak terulang	Baris hantu
READ UNCOMMITTED	mungkin	mungkin	mungkin
READ COMMITTED	tidak	mungkin	mungkin
REPEATABLE READ	tidak	tidak	tidak pada baca konsisten
SERIALIZABLE	tidak	tidak	tidak

InnoDB menerapkan tabel tersebut dengan MVCC (multiversion concurrency control), yakni teknik menyajikan potret keadaan data dari undo log, ditambah lock untuk perubahan yang saling bertabrakan. Level SERIALIZABLE menempatkan lock pada setiap pembacaan biasa, sehingga transaksi berlaku seperti antrean yang ketat, tetapi biayanya adalah kemungkinan menunggu yang lebih sering.

Nilai bawaan MySQL#

MySQL menetapkan REPEATABLE READ sebagai level bawaan, berbeda dengan banyak basis data lain yang memilih READ COMMITTED. Nilai level bawaan pada server latihan dapat diverifikasi sebagai berikut.

bash

# membaca level isolasi global dan level sesi berjalan
mysql -t -u root perpustakaan -e "SELECT @@global.transaction_isolation AS level_global, @@session.transaction_isolation AS level_sesi;"

text Keluaran

+-----------------+-----------------+
| level_global    | level_sesi      |
+-----------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------+-----------------+

Level sesi dapat diubah dengan pernyataan SET dan hanya berlaku pada koneksi tempat pernyataan itu dijalankan, sedangkan level global menentukan nilai awal koneksi baru. Basis data latihan pada seluruh modul ini dibuat ulang dari nol 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

Potret keadaan pada REPEATABLE READ#

Pada REPEATABLE READ, pembacaan biasa pertama di dalam satu transaksi membentuk potret keadaan data, dan seluruh pembacaan berikutnya membaca potret yang sama sampai transaksi berakhir. Demonstrasi berikut melibatkan dua sesi. Sesi kedua berjalan di latar belakang dan mengubah stok satu detik kemudian, sedangkan sesi utama membaca baris yang sama dua kali dengan jeda dua detik, sehingga perubahan sesi kedua sempat terjadi di tengah transaksi.

bash

# sesi kedua (latar belakang) mengubah stok satu detik kemudian
( sleep 1; mysql -u root perpustakaan -e "UPDATE buku SET stok = 9 WHERE id = 1;" ) >/dev/null &

# sesi utama membaca baris yang sama dua kali dalam satu transaksi
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
SELECT stok AS stok_baca_pertama FROM buku WHERE id = 1;
SELECT SLEEP(2) AS jeda;
SELECT stok AS stok_baca_kedua FROM buku WHERE id = 1;
COMMIT;
SELECT stok AS stok_setelah_commit FROM buku WHERE id = 1;
SQL
wait

text Keluaran

+-------------------+
| stok_baca_pertama |
+-------------------+
|                 5 |
+-------------------+
+------+
| jeda |
+------+
|    0 |
+------+
+-----------------+
| stok_baca_kedua |
+-----------------+
|               5 |
+-----------------+
+---------------------+
| stok_setelah_commit |
+---------------------+
|                   9 |
+---------------------+

Kedua pembacaan di dalam transaksi menghasilkan nilai lima walaupun perubahan menuju sembilan sudah ditetapkan sesi lain di antara keduanya. Potret yang dibentuk pembacaan pertama berlaku sampai COMMIT, sehingga baca tak terulang tidak terjadi. Pembacaan setelah transaksi berakhir keluar dari potret dan menemukan nilai sembilan yang sekarang berlaku.

Pembacaan segar pada READ COMMITTED#

Level READ COMMITTED membentuk potret baru pada setiap pembacaan, sehingga pembacaan kedua menemukan perubahan yang telah ditetapkan sesi lain. Demonstrasi berikut mengulang skenario yang sama dengan level sesi yang diubah terlebih dahulu, dan stok dikembalikan ke nilai lima agar keadaan awalnya identik.

bash

# mengembalikan stok awal agar demonstrasi dapat diulang
mysql -u root perpustakaan -e "UPDATE buku SET stok = 5 WHERE id = 1;"

# sesi kedua (latar belakang) mengubah stok satu detik kemudian
( sleep 1; mysql -u root perpustakaan -e "UPDATE buku SET stok = 9 WHERE id = 1;" ) >/dev/null &

# sesi utama membaca pada level READ COMMITTED
mysql -t -u root perpustakaan <<'SQL'
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT stok AS stok_baca_pertama FROM buku WHERE id = 1;
SELECT SLEEP(2) AS jeda;
SELECT stok AS stok_baca_kedua FROM buku WHERE id = 1;
COMMIT;
SQL
wait

text Keluaran

+-------------------+
| stok_baca_pertama |
+-------------------+
|                 5 |
+-------------------+
+------+
| jeda |
+------+
|    0 |
+------+
+-----------------+
| stok_baca_kedua |
+-----------------+
|               9 |
+-----------------+

Pembacaan kedua menghasilkan sembilan, berbeda dengan pembacaan pertama yang membaca lima. Inilah baca tak terulang yang diizinkan level ini, yaitu setiap pembacaan melihat keadaan terbaru yang sudah di-COMMIT. Aplikasi yang menyusun laporan dari beberapa pembacaan pada level ini perlu menyadari bahwa angka antartahap boleh berbeda, sedangkan aplikasi antrean yang selalu membaca keadaan terbaru justru cocok dengannya.

SERIALIZABLE yang mengunci pembacaan#

Level SERIALIZABLE dibangun di atas REPEATABLE READ dengan satu tambahan, yaitu setiap pembacaan biasa meminta lock bersama pada baris yang dibacanya. Konsekuensinya, pembaca memegang kunci yang membuat penulis menunggu, sehingga transaksi-transaksi yang berjalan bersamaan berperilaku seolah-olah dijalankan satu demi satu. Amati kunci yang dihasilkan pembacaan biasa pada level ini.

bash

# mengembalikan stok awal agar demonstrasi dapat diulang
mysql -u root perpustakaan -e "UPDATE buku SET stok = 5 WHERE id = 1;"

# pembacaan biasa pada SERIALIZABLE memegang lock bersama
mysql -t -u root perpustakaan <<'SQL'
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT judul, stok FROM buku WHERE id = 1;
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

+-----------------------------+------+
| judul                       | stok |
+-----------------------------+------+
| Dasar Basis Data Relasional |    5 |
+-----------------------------+------+
+---------------+-----------+------------+-----------+
| LOCK_MODE     | LOCK_TYPE | INDEX_NAME | LOCK_DATA |
+---------------+-----------+------------+-----------+
| S,REC_NOT_GAP | RECORD    | PRIMARY    | 1         |
+---------------+-----------+------------+-----------+

Pembacaan biasa tanpa FOR SHARE pun memegang lock bersama S,REC_NOT_GAP pada baris pertama sampai transaksinya berakhir. Selama kunci itu dipegang, pernyataan UPDATE atas baris yang sama dari sesi lain akan menunggu, kebalikan dari REPEATABLE READ yang membiarkan pembaca tidak mengunci apa pun. Biaya konsistensi tertinggi ini hanya pantas dibayar pada beban kerja yang benar-benar membutuhkan urutan eksekusi yang setara serial.

Ringkasan#

Empat level isolasi SQL diukur dari tiga anomali, yaitu baca kotor, baca tak terulang, dan baris hantu.
MySQL menetapkan REPEATABLE READ sebagai bawaan, yang membentuk potret keadaan pada pembacaan pertama dan memegangnya sampai transaksi berakhir.
READ COMMITTED membentuk potret baru pada setiap pembacaan, sehingga baca tak terulang boleh terjadi sedangkan baca kotor tetap mustahil.
SERIALIZABLE menambahkan lock bersama pada setiap pembacaan biasa, sehingga pembaca membuat penulis menunggu.
Level isolasi diatur per sesi dengan SET TRANSACTION ISOLATION LEVEL, dan perubahannya tidak menyentuh koneksi lain.

Referensi#

Transaction Isolation Levels — Oracle — Definisi resmi empat level isolasi InnoDB beserta anomali yang diizinkan masing-masing.
SET TRANSACTION Statement — Oracle — Cara mengatur level isolasi untuk satu transaksi, satu sesi, atau seluruh server.
Consistent Nonlocking Reads — Oracle — Mekanisme potret keadaan data yang menjadi dasar perilaku REPEATABLE READ dan READ COMMITTED.