🔬 BACOT — Sesi 15:00 Technical Exploration

📚 Topik: Isolation Level Transaksi & Anomali yang Tetap Lolos

Verifikasi versi: PostgreSQL 18.6 (rilis 2026-08-13), PostgreSQL 19 RC 1 (2026-10-15, belum GA). Semua benchmark di bawah saya jalankan sendiri hari ini di postgres:18-alpine (PG 18.6, x86_64, Docker lokal), bukan angka resmi vendor.

🎯 Tujuan Belajar

Bisa jawab tiga hal konkret untuk kode yang kamu tulis besok:

1. Anomali apa yang tetap bisa lolos di isolation level yang kamu pakai sekarang.
2. Berapa harga (abort rate, tps) kalau naik ke SERIALIZABLE.
3. Pola retry yang benar untuk SQLSTATE 40001 — termasuk bug klasik "retry di tengah transaksi".

🏗️ Arsitektur & Alur Teknis

Tiga mesin berbeda yang sering disamakan orang

Fakta dari dokumentasi resmi PG 18 (postgresql.org/docs/18/transaction-iso.html):

• PG hanya mengimplementasikan 3 level, bukan 4. READ UNCOMMITTED diperlakukan sama dengan READ COMMITTED.
• REPEATABLE READ PG tidak mengizinkan phantom read — lebih kuat dari minimum SQL standard.
• REPEATABLE READ PG secara eksplisit disebut sebagai Snapshot Isolation di docs.
• SERIALIZABLE = SSI, pakai predicate lock (SIReadLock), yang tidak memblokir dan tidak bisa menyebabkan deadlock.

Kenapa write skew lolos di Snapshot Isolation

Inti masalahnya: snapshot hanya mendeteksi konflik write-write. Dua transaksi yang baca himpunan sama tapi tulis baris berbeda tidak pernah bertabrakan di level storage.

Constraint bisnis: minimal 1 orang on-call.

T1: SELECT count(*) FROM oncall WHERE on_call;  -- lihat 2
T2: SELECT count(*) FROM oncall WHERE on_call;  -- lihat 2 (snapshot sendiri)
T1: UPDATE oncall SET on_call=false WHERE id=1; -- aman, 2 > 1
T2: UPDATE oncall SET on_call=false WHERE id=2; -- aman, 2 > 1
COMMIT; COMMIT;
→ 0 orang on-call. Constraint bisnis dilanggar. Tidak ada error.

SSI menambahkan deteksi rw-antidependency: T1 baca {1,2} lalu tulis 1; T2 baca {1,2} lalu tulis 2. Terbentuk siklus → satu transaksi dibatalkan dengan SQLSTATE 40001.

Alur SSI di PostgreSQL

1. SELECT di transaksi SERIALIZABLE memasang SIReadLock (predicate lock) pada heap/page/tuple yang dibaca — tanpa blocking.
2. UPDATE/INSERT mencatat rw-conflict ke transaksi lain yang sudah baca baris itu.
3. Saat commit, backend cek apakah terbentuk siklus dependency. Ada siklus → ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001).
4. Predicate lock disimpan melewati commit, sampai transaksi overlapping selesai — ini sumber utama memory pressure.
5. Kalau tabel predicate lock penuh, PG menggabungkan lock granular ke level halaman lalu level relasi. Konsekuensi: abort rate naik, bukan inkonsistensi.

💻 Kode Nyata / Konfigurasi

1. Reproduksi write skew — sudah saya jalankan, ini output aslinya

CREATE TABLE oncall(id int primary key, name text, on_call bool not null);
INSERT INTO oncall VALUES (1,'alice',true),(2,'bob',true);

Sesi A (REPEATABLE READ):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_call;  -- 2
SELECT pg_sleep(2);
UPDATE oncall SET on_call=false WHERE id=1;
COMMIT;

Sesi B (paralel, REPEATABLE READ):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_call;  -- 2 (snapshot sendiri)
SELECT pg_sleep(2);
UPDATE oncall SET on_call=false WHERE id=2;
COMMIT;

Hasil nyata dari PG 18.6:

A sees on_call=2 → A updated → A committed
B sees on_call=2 → B updated → B committed
FINAL: SELECT count(*) FROM oncall WHERE on_call  →  0

Dua-duanya sukses. Invariant rusak. Ini bukan bug PG, ini perilaku SI yang benar.

Ganti REPEATABLE READ → SERIALIZABLE, output nyata:

A sees on_call=2 → A updated → A committed
B sees on_call=2 → ERROR:  could not serialize access due to read/write dependencies among transactions
                   DETAIL:  Reason code: Canceled on identification as a pivot, during write.
                   HINT:  The transaction might succeed if retried.
FINAL: 1

2. Contoh resmi docs PG (write skew antar-agregat)

CREATE TABLE mytab(class int, value int);
INSERT INTO mytab VALUES (1,10),(1,20),(2,100),(2,200);

-- T1 SERIALIZABLE
SELECT SUM(value) FROM mytab WHERE class = 1;   -- 30
INSERT INTO mytab VALUES (2, 30);

-- T2 SERIALIZABLE (paralel)
SELECT SUM(value) FROM mytab WHERE class = 2;   -- 300
INSERT INTO mytab VALUES (1, 300);

Output nyata saya: T2 kena 40001 dengan Reason code: Canceled on identification as a pivot, during write. Ini sesuai contoh di dokumentasi resmi.

3. Retry loop yang benar (Python + psycopg 3)

import psycopg, random, time

SERIALIZATION_FAILURE = "40001"
DEADLOCK = "40P01"

def run_txn(conn, work, max_tries=10):
    for attempt in range(1, max_tries + 1):
        try:
            with conn.transaction():          # BEGIN ... COMMIT / ROLLBACK otomatis
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return work(conn)
        except psycopg.errors.Error as e:
            if e.sqlstate not in (SERIALIZATION_FAILURE, DEADLOCK):
                raise
            if attempt == max_tries:
                raise
            # backoff + jitter, JANGAN retry instan
            time.sleep(min(0.05 * 2 ** attempt, 2.0) * (0.5 + random.random()))

Aturan mutlak: work(conn) harus seluruh transaksi dibungkus ulang. Retry hanya statement yang gagal = data corruption. Setelah 40001, transaksi sudah aborted — semua command berikutnya dapat current transaction is aborted. Ini yang saya lihat langsung di output test di atas.

4. Deteksi predicate lock aktif

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM oncall WHERE on_call;
SELECT locktype, mode, relation::regclass FROM pg_locks WHERE mode = 'SIReadLock';
COMMIT;

Output nyata:

relation | SIReadLock | oncall

Ini alat debugging #1 kalau abort rate tiba-tiba naik: SIReadLock di level relation (bukan tuple/page) berarti predicate lock kehabisan memory dan di-coarsen.

5. Tuning predicate lock

# postgresql.conf — naikkan kalau SIReadLock sering di-coarsen ke relation-level
max_pred_locks_per_transaction = 128   # default 64
max_pred_locks_per_relation     = 64   # default -2 (artinya max_pred_locks_per_transaction/2)
max_pred_locks_per_page         = 4    # default 2

Dan untuk workload SERIALIZABLE, docs PG 18 secara eksplisit menyarankan: - READ ONLY untuk transaksi yang memang read-only - hindari SELECT FOR UPDATE (redundan di SERIALIZABLE, malah menambah blocking) - idle_in_transaction_session_timeout supaya tidak ada transaksi menggantung - paksa index scan: sequential scan selalu memasang relation-level predicate lock → abort rate meledak. Caranya turunkan random_page_cost / naikkan cpu_tuple_cost.

📊 Angka & Benchmark

Semua di bawah: PG 18.6, pgbench bawaan, Docker lokal, scale 5, 8 clients / 4 threads, 8–10 detik. Hardware container, bukan server produksi. Angka absolut tidak portable; rasio-nya yang berguna.

A. Low-conflict: single-row read + update (workload realistis OLTP)

Kesimpulan: pada workload yang tidak menyentuh baris yang sama, SSI praktis gratis (selisih di bawah noise). Ini konsisten dengan klaim paper Ports & Grittner (VLDB 2012): "performance only slightly below that of snapshot isolation."

B. High-conflict + write skew: baca agregat lintas baris, tulis baris sendiri (5 baris, 8 clients)

⚠️ Kejujuran data: di workload ini REPEATABLE READ pun sudah 55% gagal — penyebabnya row-level conflict (could not serialize access due to concurrent update, 40001) karena hanya ada 5 baris dan 8 client saling tabrak. Jadi selisih RR→SERIALIZABLE (55%→88%) adalah pajak SSI di atas konflik baris yang sudah ada, bukan murni biaya SSI. Yang penting: retry tidak menyelamatkan throughput — tps cuma naik 493→546 (+10,7%) meski abort turun drastis. Retry memindahkan kerja, tidak menghilangkannya.

Pelajaran produksi: SERIALIZABLE tidak memperbaiki desain kontensi. Kalau 8 worker berebut 5 baris, ganti isolation level bukan solusinya — partisi baris atau antrikan lewat queue.

C. Temuan keamanan yang relevan (rilis 2026)

• PG 18.6 (2026-08-13) memperbaiki race condition di deteksi konflik SERIALIZABLE (Peter Geoghegan): konflik bisa terlewat saat memeriksa btree index yang awalnya kosong → dua transaksi konflik bisa commit dan serializability rusak. Kalau kamu pakai SERIALIZABLE di PG ≤ 18.4, ini alasan konkret untuk patch.
• PG 19 RC 1 (2026-10-15): max_locks_per_transaction default naik 64 → 128, dan alokasi ukuran lock berubah sehingga nilai lama harus digandakan untuk kapasitas setara. Catat ini di runbook upgrade.
• PG 14 EOL: 2026-11-12. Tiga minggu dari sekarang.

D. Perbandingan lintas DBMS (Hermitage, Kleppmann)

Test suite github.com/ept/hermitage mengklasifikasi anomali dengan nama formal (G0, G1a-c, OTV, PMP, P4, G-single, G2-item, G2). Poin penting yang sering salah dikutip:

• PostgreSQL read committed = monotonic atomic view, mencegah G0/G1a/G1b/G1c, tidak mencegah G-single/G2.
• PostgreSQL repeatable read mencegah sampai G-single, tidak mencegah G2-item (= write skew) dan G2.
• MySQL repeatable read tidak mencegah G2-item pada consistent read non-locking — karena SELECT biasa tidak memasang gap lock. Gap lock hanya dipasang pada locking read (FOR UPDATE / LOCK IN SHARE MODE) dan pada SERIALIZABLE.
• MySQL SERIALIZABLE = RR + semua plain SELECT implisit jadi LOCK IN SHARE MODE. Konsekuensinya: blocking, bukan abort-and-retry. Dua model kegagalan yang sangat berbeda.

⚖️ Trade-off & Alternatif

Hierarki keputusan yang saya sarankan:

1. Bisa dijaga constraint DB (unique, exclusion, check)? → pakai itu. Nol retry, nol race.
2. Bisa dijaga atomic single-statement (UPDATE ... WHERE balance >= 100)? → pakai itu di READ COMMITTED. Nol retry.
3. Kontensi lokal di sedikit baris diketahui? → SELECT FOR UPDATE atau advisory lock.
4. Invariant lintas baris yang tidak bisa di-encode sebagai constraint? → baru SERIALIZABLE + retry loop.
5. SERIALIZABLE di seluruh aplikasi secara default? Hampir selalu salah — kamu bayar abort di tempat yang tidak butuh.

Kombinasi yang sering optimal: READ COMMITTED default untuk 95% endpoint, SERIALIZABLE hanya di 5% transaksi yang memegang invariant, dengan retry loop terpusat di middleware.

🏭 Use Case Produksi & Failure Mode Umum

Failure mode #1 — retry di tengah transaksi

# SALAH — setelah 40001, transaksi sudah aborted
try:
    cur.execute(update_saldo)
except psycopg.errors.SerializationFailure:
    cur.execute(update_saldo)   # → "current transaction is aborted"

Retry harus membungkus BEGIN ... COMMIT utuh. Saya verifikasi langsung: setelah 40001, semua command berikutnya gagal sampai ROLLBACK.

Failure mode #2 — retry tanpa backoff

8 client saling abort → retry instan → abort lagi. Di benchmark saya, max-tries=10 hanya menaikkan tps 10,7% karena semua client retry serempak. Wajib exponential backoff + jitter.

Failure mode #3 — sequential scan di SERIALIZABLE

Query tanpa index di SERIALIZABLE memasang relation-level predicate lock → setiap write ke tabel itu jadi konflik → abort rate meledak. Gejala: 40001 di endpoint yang secara logika tidak berhubungan. Cek pg_locks WHERE mode='SIReadLock' — kalau locktype='relation', itu penyebabnya.

Failure mode #4 — idle in transaction

Transaksi SERIALIZABLE menggantung memegang predicate lock melewati commit. Koneksi pool yang bocor = predicate lock menumpuk = coarsening = abort rate naik perlahan tanpa perubahan kode. Set idle_in_transaction_session_timeout.

Failure mode #5 — unique constraint violation di SERIALIZABLE

Ini yang paling mengejutkan dan ada di dokumentasi resmi: di SERIALIZABLE, kamu bisa dapat duplicate key value violates unique constraint walaupun sudah cek SELECT dulu. Penyebab: transaksi lain menyisipkan key yang sama tanpa mengikuti protokol "cek dulu". Solusi: pastikan semua transaksi SERIALIZABLE yang meng-insert key potensial melakukan pengecekan eksplisit lebih dulu — atau lebih baik, tangani unique violation sebagai retry-able error juga.

Failure mode #6 — salah paham "SERIALIZABLE = tidak perlu mikir"

SSI menjamin hasil ekuivalen dengan eksekusi serial. Tapi kalau logika satu transaksi sendiri salah (misal baca data basi dari cache aplikasi di luar transaksi), SERIALIZABLE tidak menolong. Isolasi DB tidak memperbaiki bug di layer aplikasi.

Use case nyata

🛠️ Latihan Praktis Kecil (bisa dicoba di laptop, ~10 menit)

docker run -d --rm --name isolab -e POSTGRES_PASSWORD=lab -p 55444:5432 postgres:18-alpine
sleep 6
docker exec isolab psql -U postgres -c "
  CREATE TABLE oncall(id int primary key, name text, on_call bool not null);
  INSERT INTO oncall VALUES (1,'alice',true),(2,'bob',true);"

Buka dua terminal, jalankan bergantian sesuai urutan:

Terminal A

docker exec -it isolab psql -U postgres
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_call;
-- tunggu, lalu jalankan UPDATE setelah B sudah SELECT
UPDATE oncall SET on_call=false WHERE id=1;
COMMIT;

Terminal B

docker exec -it isolab psql -U postgres
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_call;
UPDATE oncall SET on_call=false WHERE id=2;
COMMIT;

Lalu cek:

SELECT count(*) FROM oncall WHERE on_call;   -- 0 = write skew terjadi

Eksperimen lanjutan (wajib dicoba, ini bagian belajarnya):

1. Ulangi dengan SERIALIZABLE di kedua sesi. Amati siapa yang kena 40001 dan Reason code-nya.
2. Ulangi SERIALIZABLE, tapi sebelum UPDATE, jalankan SELECT locktype, mode, relation::regclass FROM pg_locks WHERE mode='SIReadLock'; di kedua sesi. Catat level granularitasnya.
3. Tambah CREATE INDEX ON oncall(on_call); lalu ulangi — apakah Reason code berubah? (predicate lock jadi tuple-level, bukan relation-level)
4. Jalankan UPDATE oncall SET on_call=true di sesi ketiga saat A dan B masih terbuka. Amati blocking vs abort.
5. Retry manual: jalankan ulang transaksi B yang kena 40001. Verifikasi hasil akhirnya 1, bukan 0.

✅ Cek Pemahaman

1. Dua transaksi REPEATABLE READ di PostgreSQL masing-masing baca count(*) = 2 lalu meng-update baris berbeda. Keduanya commit. Apakah ini bug PostgreSQL? Kalau bukan, mekanisme apa yang seharusnya mencegahnya, dan kenapa REPEATABLE READ tidak melakukannya?

2. Kamu punya endpoint yang di SERIALIZABLE kena 40001 sebanyak 12% padahal endpoint itu cuma SELECT satu baris by primary key. Sebutkan dua kemungkinan penyebab paling mungkin, dan query apa yang kamu jalankan untuk membedakannya.

3. Kenapa SELECT ... FOR UPDATE yang ditambahkan "supaya aman" ke transaksi SERIALIZABLE justru bisa memperburuk throughput, padahal tujuannya sama-sama menjamin konsistensi?

🔑 Poin Kunci

• REPEATABLE READ di PostgreSQL adalah Snapshot Isolation. Nama levelnya menyesatkan; ia mencegah phantom read tapi tidak mencegah write skew. Terverifikasi di docs PG 18 dan di test saya sendiri (final count = 0).
• SERIALIZABLE di PG = SSI: predicate lock non-blocking, jadi tidak menambah deadlock — tapi menambah abort. Model kegagalannya 40001 + retry, bukan lock wait.
• SSI gratis di workload low-conflict (3.872 vs 4.060 tps, selisih di bawah noise). SSI mahal di workload high-conflict (1.797 → 493 tps). Biayanya bukan konstan — biayanya fungsi dari kontensi.
• Retry tidak memulihkan throughput. Benchmark saya: abort 87,7% → 33,3% setelah max-tries=10, tapi tps hanya 493 → 546. Kontensi harus diperbaiki di desain, bukan di isolation level.
• MySQL dan PostgreSQL memakai nama level yang sama untuk mesin yang berbeda. SERIALIZABLE MySQL = blocking lock; SERIALIZABLE PG = abort-and-retry. Kode retry tidak portable antar keduanya.
• Prioritas: constraint DB > atomic single statement > explicit lock > SERIALIZABLE. Naik ke SERIALIZABLE hanya kalau empat opsi di bawahnya tidak bisa meng-encode invariant.
• PG ≤ 18.4 punya bug serializability nyata (fixed di 18.6, 2026-08-13): konflik terlewat pada btree index kosong. Ini bukan teori — ini alasan patch.
• PG 14 EOL 2026-11-12. PG 19 RC 1 sudah keluar; default max_locks_per_transaction naik ke 128 dan alokasi lock berubah (nilai lama harus digandakan).

➡️ Eksplorasi Lanjut

1. Baca paper aslinya: Ports & Grittner, Serializable Snapshot Isolation in PostgreSQL, VLDB 2012 — PDF. Bagian menarik: bagaimana mereka membatasi memory predicate lock lewat "transaction summarization", dan kenapa read-only transaction bisa sering melepas lock di awal.
2. Uji DBMS-mu sendiri: port test suite Hermitage ke stack-mu. Sepuluh test case, masing-masing ~20 baris SQL. Ini cara tercepat tahu apa yang sebenarnya dijamin database yang kamu pakai, bukan apa yang diklaim nama level-nya.
3. Bandingkan dengan model lain: CockroachDB dan Spanner memakai Serializable sebagai default dengan mekanisme berbeda (timestamp ordering + TrueTime / HLC). Pertanyaan menarik: kenapa mereka bisa, dan apa yang mereka bayar (latency commit, regional round-trip)?
4. Topik lanjutan yang menyambung: optimistic concurrency control di level aplikasi (version column + WHERE version = :v), dan kenapa pola itu sering lebih murah dari SERIALIZABLE untuk invariant per-baris.
5. Observability: bikin dashboard untuk pg_stat_database.xact_rollback + pg_locks WHERE mode='SIReadLock'. Lonjakan xact_rollback tanpa perubahan traffic = tanda predicate lock coarsening.

Batasan riset sesi ini: - ✅ Terverifikasi langsung: perilaku isolation PG 18.6, semua output SQL dan angka pgbench di atas — dijalankan hari ini di container postgres:18-alpine. - ✅ Terverifikasi dokumentasi: tabel isolation level PG 18, definisi SSI, tuning max_pred_locks_*, fix 18.6, status PG 19 RC 1, default MySQL InnoDB. - ⚠️ Angka benchmark tidak representatif produksi: single container, storage lokal, dataset scale 5, tanpa network. Gunakan rasio, jangan angka absolut. - ⚠️ Belum diverifikasi: perilaku isolation MySQL 9.x secara empiris (tidak ada instance MySQL 9 di environment ini — hanya mysql:8.0); klaim MySQL di atas bersumber dari dokumentasi resmi, bukan test saya. - ❓ Tidak ada angka resmi soal overhead SSI dari PostgreSQL Global Development Group untuk versi 18. Klaim "slightly below SI" berasal dari paper 2012 dan benchmark saya sendiri.