Concurrency Control

Chapter Isolation Levels menunjukkan apa yang boleh terlihat oleh transaksi yang berjalan bersamaan. Bab ini membahas sisi mesinnya: bagaimana database menjaga janji isolasi tersebut tanpa memaksa setiap transaksi mengantri satu per satu. Dua alur pemikiran bersaing — mengunci data lebih dulu sebelum disentuh (pesimistis), atau langsung bekerja dan memeriksa konflik hanya saat akhir transaksi (optimistis) — dan pilihannya menentukan apakah aplikasi toko online kita lancar saat flash sale atau justru tersendat.

Kunci Pesimistis vs Kontrol Optimistis #

Kunci pesimistis memaksa transaksi lain yang butuh data yang sama untuk menunggu: kunci atas data diambil sebelum data diubah. Di PostgreSQL, pola ini diekspresikan dengan SELECT ... FOR UPDATE (berjalan di PostgreSQL, tidak didukung SQLite):

BEGIN;
SELECT jumlah FROM stok WHERE produk = 'Kemeja Batik' FOR UPDATE;
UPDATE stok SET jumlah = jumlah - 1 WHERE produk = 'Kemeja Batik';
COMMIT;

Kontrol optimistis tidak mengunci apa pun saat membaca. Saat COMMIT, engine membandingkan kondisi data: bila data masih sama seperti saat dibaca, perubahan diterima; bila transaksi lain telah lebih dulu mengubah data, seluruh usaha dibatalkan dan harus diulang.

MVCC: Multi-Version Concurrency Control #

MVCC (Multi-Version Concurrency Control) adalah jalan tengah yang dipakai PostgreSQL: setiap UPDATE membuat versi baru baris, sementara versi lama tetap tersedia untuk transaksi pembaca yang sedang berjalan. Pembaca mendapat snapshot konsisten dan tidak pernah menghalangi penulis; penulis juga tidak menghalangi pembaca. Konflik tulis-tulis tetap dijaga, dan proses VACUUM membersihkan versi lama yang tidak lagi dibaca. SQLite menempuh pendekatan berbeda: rollback journal mengunci seluruh berkas database, sedangkan mode WAL mendekati semangat MVCC — pembaca membaca snapshot sementara hanya ada satu penulis aktif.

Lock Tingkat Baris vs Tingkat Tabel #

Granularitas kunci menentukan seberapa banyak transaksi bisa bekerja paralel — dan posisi SQLite vs PostgreSQL berdiri di dua ujungnya:

Deadlock: Siklus Saling Menunggu #

Kunci tingkat baris membuka peluang deadlock: transaksi A mengunci baris 1 lalu ingin baris 2, sementara transaksi B mengunci baris 2 lalu ingin baris 1 — keduanya menunggu selamanya.

PostgreSQL secara berkala memeriksa siklus menunggu dan membatalkan salah satu transaksi dengan galat deadlock detected; aplikasi cukup menjalankan ulang transaksi yang kalah. SQLite memilih jalan lain: menolak penulis kedua seketika, seperti akan kita buktikan berikut ini.

Uji Nyata: Deadlock Kecil dan Solusi BEGIN IMMEDIATE #

Dua transaksi sama-sama membuka BEGIN DEFERRED, membaca stok, lalu keduanya mencoba menulis. Keduanya memasang timeout=5 — tetapi perhatikan hasilnya.

import sqlite3
import threading
import time
from pathlib import Path

DB = Path("toko.db")
if DB.exists():
    DB.unlink()

setup = sqlite3.connect(DB)
setup.execute("CREATE TABLE stok (produk TEXT PRIMARY KEY, jumlah INTEGER)")
setup.execute("INSERT INTO stok VALUES ('Kemeja Batik', 10)")
setup.commit()
setup.close()


def transaksi_a():
    koneksi = sqlite3.connect(DB, timeout=5, isolation_level=None)
    koneksi.execute("BEGIN DEFERRED")
    print("[A] baca stok:", koneksi.execute(
        "SELECT jumlah FROM stok").fetchone()[0])
    time.sleep(0.4)  # memberi B waktu membaca lebih dulu
    koneksi.execute("UPDATE stok SET jumlah = jumlah - 1")
    print("[A] UPDATE berhasil (memegang kunci tulis)")
    koneksi.execute("COMMIT")
    koneksi.close()


def transaksi_b():
    time.sleep(0.1)  # mulai sedikit setelah A
    koneksi = sqlite3.connect(DB, timeout=5, isolation_level=None)
    koneksi.execute("BEGIN DEFERRED")
    print("[B] baca stok:", koneksi.execute(
        "SELECT jumlah FROM stok").fetchone()[0])
    time.sleep(0.4)  # A sudah menulis saat B mencoba menulis
    try:
        koneksi.execute("UPDATE stok SET jumlah = jumlah - 1")
        print("[B] UPDATE berhasil")
        koneksi.execute("COMMIT")
    except sqlite3.OperationalError as galat:
        print("[B] UPDATE gagal seketika (padahal timeout 5 detik):", galat)
    finally:
        koneksi.close()


t1 = threading.Thread(target=transaksi_a)
t2 = threading.Thread(target=transaksi_b)
t1.start()
t2.start()
t1.join()
t2.join()

cek = sqlite3.connect(DB)
print("stok akhir:", cek.execute("SELECT jumlah FROM stok").fetchone()[0])
cek.close()

Output yang Dihasilkan:

[A] baca stok: 10
[B] baca stok: 10
[A] UPDATE berhasil (memegang kunci tulis)
[B] UPDATE gagal seketika (padahal timeout 5 detik): database is locked
stok akhir: 9

Mari kita telaah baris-baris kode di atas:

1. Baris 17-26 (transaksi_a): transaksi A membaca stok lebih dulu (BEGIN DEFERRED mengambil kunci baca), lalu time.sleep(0.4) memastikan B sempat membaca sebelum A menulis.
2. Baris 29-43 (transaksi_b): transaksi B juga membaca dulu, lalu mencoba menulis setelah A jelas memegang kunci tulis; try/except menangkap hasilnya.
3. Baris 46-55: thread utama menunggu kedua transaksi dengan join(), lalu memeriksa stok akhir dari koneksi segar.

Hasilnya mengejutkan: B sudah mengatur timeout=5, tetapi galat muncul seketika. Alasannya, B menahan kunci baca sejak SELECT-nya, dan SQLite mendeteksi pola deadlock potensial — bila B menunggu kunci tulis A sementara COMMIT A menunggu kunci baca B lepas, keduanya saling mengunci selamanya. Busy handler sengaja tidak dipanggil untuk situasi ini; kegagalan seketika lebih baik daripada kebuntuan.

Solusinya adalah BEGIN IMMEDIATE: transaksi yang pasti akan menulis mengambil kunci tulis sejak awal, sebelum membaca. Skrip kedua hanya mengubah bentuk BEGIN-nya, dengan hasil yang berbeda total.

import sqlite3
import threading
import time
from pathlib import Path

DB = Path("toko.db")
if DB.exists():
    DB.unlink()

setup = sqlite3.connect(DB)
setup.execute("CREATE TABLE stok (produk TEXT PRIMARY KEY, jumlah INTEGER)")
setup.execute("INSERT INTO stok VALUES ('Kemeja Batik', 10)")
setup.commit()
setup.close()


def transaksi_a():
    koneksi = sqlite3.connect(DB, timeout=5, isolation_level=None)
    koneksi.execute("BEGIN IMMEDIATE")
    print("[A] BEGIN IMMEDIATE: kunci tulis diperoleh")
    jumlah = koneksi.execute("SELECT jumlah FROM stok").fetchone()[0]
    print("[A] baca stok:", jumlah)
    time.sleep(1.0)
    koneksi.execute("UPDATE stok SET jumlah = jumlah - 1")
    koneksi.execute("COMMIT")
    print("[A] COMMIT berhasil")
    koneksi.close()


def transaksi_b():
    time.sleep(0.2)
    koneksi = sqlite3.connect(DB, timeout=5, isolation_level=None)
    awal = time.perf_counter()
    koneksi.execute("BEGIN IMMEDIATE")  # menunggu sampai A selesai
    durasi = time.perf_counter() - awal
    print(f"[B] BEGIN IMMEDIATE berhasil setelah menunggu {durasi:.2f} detik")
    jumlah = koneksi.execute("SELECT jumlah FROM stok").fetchone()[0]
    print("[B] baca stok:", jumlah)
    koneksi.execute("UPDATE stok SET jumlah = jumlah - 1")
    koneksi.execute("COMMIT")
    print("[B] COMMIT berhasil")
    koneksi.close()


t1 = threading.Thread(target=transaksi_a)
t2 = threading.Thread(target=transaksi_b)
t1.start()
t2.start()
t1.join()
t2.join()

cek = sqlite3.connect(DB)
print("stok akhir:", cek.execute("SELECT jumlah FROM stok").fetchone()[0])
cek.close()

Output yang Dihasilkan:

[A] BEGIN IMMEDIATE: kunci tulis diperoleh
[A] baca stok: 10
[A] COMMIT berhasil
[B] BEGIN IMMEDIATE berhasil setelah menunggu 0.83 detik
[B] baca stok: 9
[B] COMMIT berhasil
stok akhir: 8

Kali ini B sungguh-sungguh menunggu sekitar 0,8 detik (angkanya bervariasi antar eksekusi) sampai A selesai, lalu membaca stok 9 — data hasil commit A, bukan angka basi 10 — dan menuliskan 8. Dua transaksi berjalan bergiliran tanpa kegagalan dan tanpa data basi. Perilaku BEGIN IMMEDIATE terdokumentasi di SQLite Transaction ; cara PostgreSQL memadukan MVCC dengan kunci baris dibahas di MVCC PostgreSQL .

Kontrol konkurensi kini lengkap dari sudut pandang satu koneksi. Namun aplikasi nyata membuka puluhan koneksi sekaligus, dan setiap koneksi punya harga: handshake jaringan, autentikasi, alokasi memori. Chapter Connection Pooling menjelaskan cara mendaur ulang koneksi agar biaya tersebut dibayar sekali, bukan pada setiap request.