Isolation dan Locking

Chapter Autocommit dan Perilaku Default ditutup dengan pertanyaan yang belum dijawab: apa yang terjadi jika dua proses menulis ke toko.db di saat bersamaan? Jawabannya ada pada sistem locking SQLite — sekumpulan aturan tentang siapa yang boleh membaca dan menulis, ditegakkan pada tingkat berkas database. Chapter ini membedah aturan tersebut lewat uji dua proses yang nyata, memperlihatkan galat database is locked, lalu memperkenalkan dua peredam konflik: busy_timeout dan BEGIN IMMEDIATE.

Kunci Milik Seluruh Database #

SQLite tidak mengunci per tabel maupun per baris — objek kuncinya adalah berkas database itu sendiri. Dari situ mengikuti tiga aturan sederhana:

• Banyak koneksi boleh membaca bersamaan; pembaca tidak saling menghalangi.
• Hanya satu penulis yang boleh aktif pada satu waktu.
• Penulis berhadapan dengan pembaca hanya pada momen COMMIT.

Engine SQLite menegakkan aturan tersebut dengan tiga tingkat lock:

Akibatnya, kunci yang diambil saat menulis satu tabel ikut mengunci tabel lain — uji di bawah membuktikannya. Skema contoh kita memakai dua tabel yang tidak berhubungan:

CREATE TABLE rekening (
  id INTEGER PRIMARY KEY,
  pemilik TEXT NOT NULL,
  saldo INTEGER NOT NULL
);

INSERT INTO rekening VALUES (1, 'Budi', 500000), (2, 'Siti', 200000);

CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  harga INTEGER NOT NULL
);

INSERT INTO produk VALUES (1, 'Kopi Gayo 200g', 45000);

Setiap demo di chapter ini mengasumsikan keadaan awal di atas — buat ulang toko.db dari skema tersebut sebelum mengulang sebuah demo bila ingin keluaran yang persis sama. Koneksi pertama menahan transaksi tulis pada tabel rekening, sementara koneksi kedua mencoba mengubah tabel produk:

# Terminal 1: menulis tabel rekening, menahan lock 2 detik
( echo "BEGIN IMMEDIATE; UPDATE rekening SET saldo = saldo - 10000 WHERE id = 1;"; \
  sleep 2; echo "COMMIT;" ) | sqlite3 toko.db &
sleep 0.5

# Terminal 2: menulis tabel produk (tabel yang berbeda) tetap ditolak
sqlite3 toko.db "UPDATE produk SET harga = harga + 500 WHERE id = 1;"
wait

# setelah lock lepas, perintah yang sama berhasil
sqlite3 toko.db "UPDATE produk SET harga = harga + 500 WHERE id = 1;"
sqlite3 toko.db "SELECT id, nama, harga FROM produk ORDER BY id;"

Output yang Dihasilkan:

Error: stepping, database is locked (5)
1|Kopi Gayo 200g|45500

Pola ( echo ...; sleep ...; echo ... ) | sqlite3 toko.db & meniru terminal yang transaksinya masih terbuka: statemen dikirim ke satu koneksi lewat pipe dengan jeda sleep, sehingga lock sempat dipegang saat koneksi kedua berjalan.

Galat database is locked #

Dua penulis yang berhadapan langsung menghasilkan galat yang terkenal itu. Koneksi pertama memegang lock selama tiga detik; koneksi kedua mencoba menulis di tengah jeda tersebut:

# Terminal 1: penulis pertama menahan lock selama 3 detik
( echo "BEGIN IMMEDIATE; UPDATE rekening SET saldo = saldo - 10000 WHERE id = 1;"; \
  sleep 3; echo "COMMIT;" ) | sqlite3 toko.db &
sleep 1

# Terminal 2: penulis kedua mencoba menulis saat database terkunci
sqlite3 toko.db "UPDATE rekening SET saldo = saldo + 10000 WHERE id = 2;"
wait
sqlite3 toko.db "SELECT id, pemilik, saldo FROM rekening ORDER BY id;"

Output yang Dihasilkan:

Error: stepping, database is locked (5)
1|Budi|490000
2|Siti|200000

Mari kita bedah apa yang terjadi:

1. Terminal 1 (BEGIN IMMEDIATE; UPDATE ...): koneksi pertama mengambil lock RESERVED sejak BEGIN IMMEDIATE, lalu mengurangi saldo Budi — transaksinya sengaja ditahan tiga detik sebelum COMMIT.
2. Terminal 2 (UPDATE ...): penulis kedua butuh lock RESERVED yang sedang dipegang koneksi pertama. Karena tidak tersedia, statement langsung gagal dengan galat database is locked (kode 5, SQLITE_BUSY) dan prosesnya keluar dengan exit code 5.
3. SELECT terakhir: hanya transaksi koneksi pertama yang tersimpan — penurunan saldo Budi menjadi 490000 permanen, sedangkan kenaikan saldo Siti sama sekali tidak pernah terjadi.

CLI sqlite3 memulai setiap koneksi dengan busy_timeout 0, sehingga konflik tidak ditunggu sama sekali — galat muncul seketika.

Pembaca Menahan COMMIT Penulis #

Di mode rollback journal, pembaca yang transaksinya masih terbuka ikut menghambat penulis — tetapi baru terasa saat COMMIT. Ujinya seperti berikut:

# Terminal 1 (pembaca): transaksi baca dibiarkan terbuka ~3 detik
( echo "BEGIN; SELECT saldo FROM rekening WHERE id = 1;"; \
  sleep 3; echo "COMMIT;" ) | sqlite3 toko.db &
sleep 0.5

# Terminal 2 (penulis): menulis berhasil, COMMIT ditolak
( echo "BEGIN IMMEDIATE; UPDATE rekening SET saldo = saldo - 10000 WHERE id = 1;"; \
  sleep 2; echo "COMMIT;" ) | sqlite3 toko.db
wait
sqlite3 toko.db "SELECT id, pemilik, saldo FROM rekening ORDER BY id;"

Output yang Dihasilkan:

500000
Runtime error near line 2: database is locked (5)
1|Budi|500000
2|Siti|200000

Pembaca memegang SHARED sejak SELECT di dalam transaksinya, dan lock itu baru dilepas saat COMMIT pembaca. Penulis mengambil RESERVED dan menjalankan UPDATE tanpa masalah — dua lock itu boleh hidup berdampingan. Namun COMMIT si penulis menuntut EXCLUSIVE, dan selama masih ada SHARED milik pembaca, permintaan itu ditolak. Pesan near line 2 menunjuk pada baris kedua yang diterima koneksi penulis, yaitu COMMIT itu sendiri. Karena COMMIT gagal dan koneksi penulis lalu ditutup, seluruh transaksinya dibatalkan — saldo Budi kembali 500000.

Menghaluskan Konflik dengan busy_timeout #

Galat instan di atas berguna untuk diagnosis, tetapi menyiksa aplikasi yang sibuk. busy_timeout menyuruh koneksi menunggu dan mencoba lagi hingga batas waktu tertentu alih-alih langsung gagal:

# Koneksi 1: menahan lock selama 2 detik
( echo "BEGIN IMMEDIATE; UPDATE rekening SET saldo = saldo - 10000 WHERE id = 1;"; \
  sleep 2; echo "COMMIT;" ) | sqlite3 toko.db &
sleep 0.5

# Koneksi 2: menulis dengan batas tunggu 5000 ms
sqlite3 toko.db ".timeout 5000" "UPDATE rekening SET saldo = saldo + 10000 WHERE id = 2;"
wait
sqlite3 toko.db "SELECT id, pemilik, saldo FROM rekening ORDER BY id;"

Output yang Dihasilkan:

1|Budi|490000
2|Siti|210000

Koneksi kedua menunggu sekitar satu setengah detik hingga koneksi pertama selesai, lalu UPDATE-nya berhasil — kedua perubahan tersimpan. Di CLI, batas tunggu diatur lewat .timeout <milidetik>; dari kode aplikasi, padanannya adalah PRAGMA busy_timeout=5000 yang dijalankan sekali setelah koneksi dibuka.

BEGIN DEFERRED, IMMEDIATE, dan EXCLUSIVE #

SQLite punya tiga cara membuka transaksi, dan bedanya ada pada kapan lock diambil:

Mode DEFERRED yang menjadi default inilah sumber jebakan klasik. Dua koneksi sama-sama membaca dulu (keduanya memegang SHARED), lalu keduanya mencoba menulis:

# Koneksi A: baca dulu, tulis kemudian, COMMIT paling akhir
( echo "BEGIN; SELECT saldo FROM rekening WHERE id = 1;"; \
  sleep 0.3; echo "UPDATE rekening SET saldo = saldo - 10000 WHERE id = 1;"; \
  sleep 2.5; echo "COMMIT;" ) | sqlite3 toko.db &
sleep 0.5

# Koneksi B: baca, lalu mencoba menulis
( echo "BEGIN; SELECT saldo FROM rekening WHERE id = 2;"; \
  sleep 1; echo "UPDATE rekening SET saldo = saldo + 10000 WHERE id = 2;"; \
  sleep 3 ) | sqlite3 toko.db
wait
sqlite3 toko.db "SELECT id, pemilik, saldo FROM rekening ORDER BY id;"

Output yang Dihasilkan:

500000
200000
Runtime error near line 2: database is locked (5)
Runtime error near line 3: database is locked (5)
1|Budi|500000
2|Siti|200000

Koneksi B gagal di UPDATE (baris kedua input B) karena RESERVED sudah dipegang koneksi A. Kunci SHARED milik B belum dilepas, sehingga COMMIT koneksi A (baris ketiga input A) ikut ditolak. Kedua koneksi saling menunggu: A butuh lock B dilepas, B butuh A selesai. SQLite tidak memutus kebuntuan ini secara otomatis — salah satu koneksi harus ROLLBACK lebih dulu agar yang lain bisa maju. Pada uji ini keduanya berakhir tanpa COMMIT, sehingga SELECT terakhir memastikan tidak ada satu pun perubahan yang tersimpan.

BEGIN IMMEDIATE memindahkan kegagalan ke awal, sebelum pekerjaan apa pun dilakukan:

# Koneksi 1: BEGIN IMMEDIATE menahan lock 2 detik
( echo "BEGIN IMMEDIATE; UPDATE rekening SET saldo = saldo - 10000 WHERE id = 1;"; \
  sleep 2; echo "COMMIT;" ) | sqlite3 toko.db &
sleep 0.5

# Koneksi 2: BEGIN IMMEDIATE kedua langsung ditolak di awal
sqlite3 toko.db "BEGIN IMMEDIATE;"
wait
sqlite3 toko.db "SELECT id, pemilik, saldo FROM rekening ORDER BY id;"

Output yang Dihasilkan:

Error: stepping, database is locked (5)
1|Budi|490000
2|Siti|200000

Koneksi kedua ditolak pada BEGIN IMMEDIATE itu sendiri — sebelum membaca atau mengubah apa pun. Koneksi pertama tetap selesai dengan bersih, dan aplikasi koneksi kedua tinggal mencoba ulang dari keadaan yang masih bersih, bukan dari setengah pekerjaan yang nyangkut.

Seluruh perilaku di chapter ini berlaku untuk mode rollback journal yang menjadi default SQLite. Mode WAL mengubah permainan sepenuhnya: pembaca dan penulis tidak lagi saling menunggu — itu bahasan Chapter Journal Modes dan WAL.