Mencegah Write Skew dengan Isolasi Transaksi

Prompt dan Konteks yang Berlaku

Tabel doctor_shifts(team_id, shift_date, doctor_id, on_call) menyimpan status on-call. Aturan bisnis menyatakan bahwa setiap tim dan shift harus mempertahankan setidaknya 1 dokter on-call. Alice dan Bob sama-sama aktif, dan dua permintaan secara bersamaan mencoba menonaktifkan dokter yang berbeda dari status on-call. Setiap transaksi pertama-tama menghitung dokter yang aktif. Jika jumlahnya lebih dari 1, transaksi tersebut memperbarui baris dokternya sendiri.

Pertanyaan ini dibatasi pada PostgreSQL 18. Jumlah aktif awal adalah 2. Kedua transaksi menyelesaikan pembacaannya sebelum salah satunya melakukan commit, lalu keduanya memperbarui baris yang berbeda. Angka dan skema ini merupakan asumsi wawancara, bukan model data layanan kesehatan universal. Masalah intinya adalah predikat multi-baris count(on_call) >= 1; alur kerja persetujuan, zona waktu, dan otorisasi berada di luar cakupan.

Pola yang sama muncul dalam aturan seperti inventaris yang tidak boleh bernilai negatif, akun yang harus mempertahankan setidaknya satu pemberi persetujuan, atau klaster yang harus mempertahankan setidaknya satu primer. Mengatakan "masukkan ke dalam transaksi" tidaklah lengkap. Atomisitas membuat satu transaksi berhasil atau gagal sebagai satu kesatuan. Tingkat isolasi menentukan apa yang dapat diamati oleh transaksi konkuren dan apakah database menolak hasil yang tidak memiliki penjelasan serial yang valid.

Apa yang Dievaluasi Pewawancara

Sinyal pertama adalah mengenali write skew. Kedua transaksi membaca predikat yang sama tetapi menulis ke baris yang berbeda, sehingga mereka tidak menimbulkan konflik penulisan pada baris yang sama secara biasa. Setiap transaksi lolos pemeriksaannya sendiri secara terisolasi, namun hasil gabungannya menyisakan nol dokter aktif. Menyebut ini sebagai dirty read atau lost update sederhana akan mengarah pada perbaikan yang salah.

Sinyal kedua adalah memisahkan nama standar dari implementasi database. PostgreSQL Read Committed mengambil snapshot baru untuk setiap pernyataan. Repeatable Read menggunakan snapshot transaksi yang stabil dan diimplementasikan sebagai isolasi snapshot, yang masih memungkinkan anomali serialisasi. Serializable melacak dependensi baca/tulis di atas pembacaan bergaya snapshot serupa dan membatalkan transaksi ketika hasilnya tidak dapat cocok dengan urutan serial apa pun. Tingkat isolasi dengan nama yang sama dapat menggunakan semantik penguncian dan snapshot yang berbeda di database lain.

Sinyal ketiga adalah memilih batasan konkurensi dari invarian tersebut. Ketika invarian mencakup beberapa baris, mengunci "dokter yang akan saya perbarui" tidak membuat kedua transaksi tersebut saling bertubrukan. Rancangan yang aman harus membuat keduanya bersaing pada satu objek yang dapat dikunci, mereduksi aturan tersebut menjadi kondisi atomik pada satu baris, atau membiarkan Serializable mendeteksi konflik tersebut.

Terakhir, pewawancara mencari penanganan kegagalan. Baik Serializable maupun penguncian eksplisit dapat membatalkan transaksi. Kode serialisasi PostgreSQL yang umum adalah 40001; urutan penguncian yang tidak konsisten juga dapat menghasilkan 40P01. Jawaban yang kuat akan mengulang seluruh transaksi, membatasi jumlah percobaan, dan memindahkan email atau efek eksternal lainnya ke jalur idempoten setelah commit berhasil.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

• Database dan versi apa yang digunakan? Jawaban ini ditujukan untuk PostgreSQL 18. MySQL, SQL Server, dan database terdistribusi memerlukan pemeriksaan ulang terhadap tingkat isolasi bernama sama dan error percobaan ulang mereka.
• Apakah aturan ini mencakup tepat satu shift? Invarian yang menggunakan kunci (team_id, shift_date) dapat memiliki satu titik persaingan yang stabil per shift. Aturan yang mencakup beberapa wilayah atau database tidak dapat dilindungi oleh kunci baris hanya dalam satu database.
• Jalur mana saja yang dapat mengubah status on-call? Pengajuan cuti, pertukaran jadwal, impor, dan perbaikan administratif semuanya harus mengikuti protokol yang sama. Memperbaiki satu API menyisakan jalur penulisan pintas (bypass) yang dapat merusak penghitung atau melewatkan penguncian.
• Apakah konflik terjadi sesekali atau terus-menerus tinggi? Serializable dengan percobaan ulang sering kali paling jelas pada tingkat persaingan rendah. Pembaruan kondisional satu baris dapat menghindari pekerjaan yang sia-sia pada shift yang padat, tetapi juga mengubah shift tersebut menjadi hotspot yang diserialisasi.
• Berapa lama permintaan boleh menunggu? Kunci pesimistik menunggu pemegang kunci. Anggaran latensi yang ketat menuntut transaksi singkat, batas waktu tunggu kunci, dan hasil eksplisit "status berubah, coba lagi".
• Apakah transaksi melakukan efek samping eksternal? Percobaan ulang akan menjalankan kembali logika transaksi. Email, panggilan ke layanan roster, atau pesan non-transaksional harus dilakukan setelah commit atau menggunakan outbox transaksional dengan konsumsi yang idempoten.

Kerangka Jawaban 30 Detik

"Ini adalah write skew: kedua transaksi mengambil keputusan dari set on-call yang sama tetapi memperbarui baris dokter yang berbeda, sehingga snapshot Repeatable Read yang stabil pun dapat membiarkan keduanya melakukan commit. PostgreSQL Serializable melacak dependensi baca/tulis tersebut dan membatalkan setidaknya satu transaksi; aplikasi harus menjalankan kembali seluruh keputusan pada 40001. Tanpa Serializable, saya akan memetakan setiap shift ke satu baris roster, melakukan dekremen secara atomik di bawah Read Committed hanya jika active_count > 1, dan memperbarui dokter dalam transaksi yang sama. Opsi lain adalah mengunci baris roster sebelum menghitung ulang. Saya akan menguji dengan dua koneksi dan sebuah barrier setelah kedua operasi baca, membuktikan bahwa isolasi yang lemah menghasilkan nol sementara rancangan yang aman mempertahankan tepat satu dokter."

Jawaban Mendalam Langkah demi Langkah

Mulailah dengan riwayat konkuren. Awalnya, Alice=true, Bob=true:

T1: read count(on_call) = 2
T2: read count(on_call) = 2
T1: update Alice to false
T2: update Bob to false
T1: commit
T2: commit

Jika T1 berjalan lebih dulu dalam eksekusi serial, T2 akan membaca 1 dan menolak perubahan tersebut. Jika T2 berjalan lebih dulu, T1 akan menolaknya. Hasil aktualnya adalah nol, yang tidak setara dengan urutan serial mana pun dan oleh karena itu merupakan anomali serialisasi. Perbedaan utama dari lost update adalah bahwa T1 dan T2 tidak pernah menimpa baris yang sama, sehingga kunci penulisan tingkat baris biasa tidak saling berkonflik secara alami.

Di bawah Read Committed, setiap pernyataan hitung mengamati data yang telah di-commit ketika pernyataan tersebut dimulai. Dengan barrier dalam prompt, kedua pernyataan membaca 2 sebelum salah satu pembaruan terjadi. Pernyataan UPDATE menargetkan baris yang berbeda dan keduanya dapat melakukan commit. Pernyataan yang lebih baru akan menerima snapshot yang lebih baru, tetapi PostgreSQL tidak secara retroaktif membatalkan keputusan aplikasi yang telah dibuat.

PostgreSQL Repeatable Read mempertahankan satu snapshot untuk transaksi. Ini mencegah nonrepeatable read dan phantom read dalam implementasi tersebut, tetapi mengizinkan anomali serialisasi. Kedua transaksi memperbarui rantai versi yang berbeda, sehingga tidak ada pembaruan konkuren pada baris yang sama yang memicu rollback dan keduanya dapat melakukan commit. MVCC menjelaskan bagaimana pembaca menghindari pemblokiran penulis. Ini tidak sama dengan Serializable, dan tidak menyimpulkan aturan bisnis "setidaknya satu dokter tetap on-call."

Opsi aman pertama adalah Serializable:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) AS active_count
FROM doctor_shifts
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND on_call;

-- Reject when active_count <= 1.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND doctor_id = 101
  AND on_call;

COMMIT;

Ketika permintaan tumpang tindih, PostgreSQL mendeteksi bahwa pembacaan predikat dan penulisan konkuren mereka membentuk dependensi yang tidak dapat diserialisasi. PostgreSQL tidak dapat mengizinkan kedua transaksi melakukan commit. Transaksi yang gagal mengembalikan SQLSTATE 40001. Percobaan ulang harus dimulai sebelum BEGIN dan menjalankan kembali penghitungan serta keputusan untuk memperbarui. Mengulang kembali hanya pada UPDATE terakhir akan mempertahankan keputusan yang basi. Gunakan jittered backoff, batas percobaan maksimum, dan batas waktu total karena percobaan ulang dapat berkonflik lagi di bawah persaingan yang tinggi.

Serializable memungkinkan aplikasi mengekspresikan aturan di sekitar predikat aslinya alih-alih membuat kunci manual untuk setiap invarian. Biayanya adalah overhead pelacakan dependensi dan percobaan ulang akibat konflik. Set baca yang lebih besar, transaksi yang lebih lama, dan persaingan yang terkonsentrasi umumnya menciptakan lebih banyak peluang untuk tumpang tindih. Indeks pada (team_id, shift_date), transaksi yang singkat, dan tidak adanya waktu tunggu jaringan sebelum commit akan memperkecil celah tersebut.

Opsi kedua memproyeksikan invarian multi-baris ke satu baris, on_call_rosters(team_id, shift_date, active_count), dan menggunakan pembaruan atomik di bawah Read Committed:

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE on_call_rosters
SET active_count = active_count - 1
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND active_count > 1
RETURNING active_count;

-- Continue only when exactly one roster row was returned.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND doctor_id = 101
  AND on_call;

-- Commit only when both updates changed exactly one row; otherwise roll back.
COMMIT;

Kedua permintaan kini bersaing pada baris roster yang sama. Setelah menunggu pembaruan konkuren di bawah PostgreSQL Read Committed, pembaru memeriksa ulang active_count > 1 terhadap versi baris yang baru. Permintaan pertama mengubah 2 menjadi 1; kondisi pada permintaan kedua gagal dan tidak mengembalikan baris apa pun. CHECK (active_count >= 1) tingkat baris dapat memberikan perlindungan terakhir. Konsekuensinya adalah setiap jalur aktivasi, deaktivasi, impor, dan perbaikan harus mempertahankan penghitung dalam transaksi yang sama. Sistem juga harus merekonsiliasi penghitung terhadap baris detail dan memberikan peringatan jika terjadi pergeseran (drift), alih-alih menimpanya secara diam-diam.

Jika memelihara penghitung tidak diinginkan, pertahankan baris roster yang stabil untuk setiap shift. Sebagai langkah pertama dalam transaksi Read Committed, jalankan SELECT ... FOR UPDATE pada baris tersebut, lalu hitung baris detail dan perbarui dokternya. Setelah permintaan kedua selesai menunggu, SELECT berikutnya akan mendapatkan snapshot yang mencakup commit pertama. Setiap jalur penulisan harus mengunci baris roster yang sama terlebih dahulu, dan transaksi harus tetap singkat. Varian halus yang tidak aman adalah membuat snapshot Repeatable Read yang lama lalu mengunci baris penjaga yang tidak dimodifikasi oleh siapa pun: memperoleh FOR UPDATE tidak memperbarui snapshot transaksi tersebut.

Mengunci semua baris dokter yang aktif saat ini secara langsung juga memerlukan kehati-hatian. Set kunci berasal dari predikat, dan baris baru, urutan kueri yang berbeda, atau penulisan pintas dapat mengubah validitas keamanannya. Urutan yang tidak konsisten di berbagai kunci baris dapat menyebabkan deadlock. Jika rancangan ini diperlukan, dapatkan kunci dalam urutan primary-key yang stabil dan jalankan kembali seluruh transaksi pada 40P01. Mengunci hanya "dokter yang akan saya nonaktifkan dari on-call" sudah pasti tidak memadai karena permintaan-permintaan tersebut tetap mengunci baris yang berbeda.

Jangan memvalidasi ini dengan memanggil dua API secara berurutan. Gunakan dua koneksi database independen dan sebuah barrier pengujian. Kedua sesi memulai Repeatable Read, menghitung baris, memastikan masing-masing membaca 2, dan baru kemudian melanjutkan ke pembaruan dan commit mereka. Baseline harus secara andal menghasilkan dua commit dan hitungan akhir nol. Di bawah Serializable, pastikan bahwa dua keberhasilan adalah hal yang mustahil: satu transaksi menerima 40001, dan percobaan ulang penuhnya membaca 1 lalu menolak perubahan tersebut. Di bawah rancangan roster, pastikan bahwa tepat satu pembaruan kondisional mengembalikan baris dan bahwa detail serta active_count berakhir pada angka 1.

Ulangi kasus konkuren ini dengan urutan commit yang diacak. Tambahkan permintaan duplikat untuk dokter yang sama, dokter ketiga yang bergabung, rollback di antara dua pembaruan, batas waktu tunggu kunci (lock-wait timeout), dan kasus deadlock. Di lingkungan produksi, pantau 40001, 40P01, jumlah percobaan ulang, waktu tunggu kunci, durasi transaksi, tingkat kegagalan akhir, dan perbedaan antara penghitung roster dan baris detail. Lonjakan tiba-tiba pada tingkat konflik biasanya menandakan adanya hotspot, transaksi yang panjang, atau jalur penulisan baru yang masuk ke dalam batasan; percobaan ulang tanpa batas hanya akan menyembunyikan dan memperparah masalah tersebut.

Contoh Jawaban Berkualitas Tinggi

"Pertama, saya akan mengidentifikasi ini sebagai write skew. Kedua transaksi memeriksa aturan lintas-baris yang sama, tetapi Alice dan Bob memperbarui baris mereka sendiri, sehingga kunci penulisan tingkat baris tidak saling berkonflik. Berdasarkan jadwal di mana keduanya membaca 2 terlebih dahulu, Read Committed dapat membiarkan keduanya melakukan commit. PostgreSQL Repeatable Read memberi setiap transaksi snapshot yang stabil, namun masih dapat menghasilkan nilai akhir nol, yang tidak dapat dijelaskan oleh urutan serial mana pun.

Pilihan pertama saya adalah mengekspresikan aturan tersebut dalam transaksi Serializable. Saya menghitung dokter aktif untuk shift tersebut dan memperbarui hanya jika hitungannya lebih dari 1. PostgreSQL melacak dependensi antara pembacaan predikat dan penulisan konkuren. Dalam kasus ini, kedua transaksi tidak dapat melakukan commit secara bersamaan; salah satunya akan menerima 40001. Aplikasi menangkap error tersebut dan menjalankan kembali penghitungan serta pembaruan dari awal dengan kebijakan percobaan ulang yang terbatas. Saya memisahkan notifikasi di luar transaksi yang dapat dicoba ulang dan memprosesnya dari outbox idempoten setelah commit.

Untuk shift dengan persaingan tinggi yang berkelanjutan, saya akan mempertimbangkan baris on_call_rosters. Di bawah Read Committed, transaksi cuti menggunakan UPDATE ... SET active_count = active_count - 1 WHERE active_count > 1 RETURNING ... untuk bersaing pada satu baris tersebut, lalu memperbarui detail dokter. Jika salah satu pernyataan gagal mengubah tepat satu baris, seluruh transaksi akan di-rollback. Permintaan kedua memeriksa ulang kondisinya pada versi baris terbaru dan gagal. Konsekuensinya adalah setiap jalur penulisan harus memelihara penghitung tersebut.

Saya akan memverifikasi rancangan ini dengan dua koneksi dan sebuah barrier yang mengatur persilangan eksekusi. Pertama, saya akan membuktikan bahwa Repeatable Read dapat membiarkan kedua sesi membaca 2 dan berakhir pada nol. Kemudian saya akan membuktikan bahwa Serializable membatalkan tepat satu transaksi dan bahwa percobaan ulangnya menolak perubahan tersebut. Rancangan penghitung harus mengizinkan hanya satu pembaruan kondisional. Terakhir, saya akan memantau kegagalan serialisasi, deadlock, waktu tunggu kunci, dan pergeseran penghitung agar klaim keamanan tidak bergantung pada jadwal pengujian yang kebetulan berhasil."

Kesalahan Umum

• "Ini berada di dalam transaksi, jadi pasti aman" → Atomisitas tidak mengatur visibilitas dan urutan commit antar-transaksi yang berjalan bersamaan → Sebutkan tingkat isolasi dan tulis persilangan eksekusi yang merusak invarian tersebut.
• Memperlakukan write skew sebagai lost update → Transaksi menulis ke baris yang berbeda, sehingga pemeriksaan versi pada satu baris tidak mencakup predikat bersama mereka → Identifikasi invarian lintas-baris dan gunakan titik persaingan bersama atau Serializable.
• Mengklaim Repeatable Read sama dengan Serializable → Snapshot yang stabil masih dapat menghasilkan hasil gabungan tanpa penjelasan serial yang valid → Jelaskan anomali serialisasi yang diizinkan oleh PostgreSQL Repeatable Read.
• Mengunci baris dokter dari masing-masing transaksi → Kunci milik Alice dan Bob tidak saling berkonflik → Kunci satu baris roster, perbarui satu baris penghitung, atau biarkan SSI mendeteksi dependensi predikat.
• Menambahkan FOR UPDATE setelah snapshot Repeatable Read yang lama → Mengunci baris penjaga yang tidak berubah tidak memperbarui snapshot transaksi → Kunci dan baca ulang di bawah Read Committed, atau gunakan Serializable atau baris penghitung yang dapat berubah.
• Mencoba ulang hanya pada COMMIT atau pernyataan SQL terakhir setelah 40001 → Keputusan bisnis tetap berasal dari snapshot yang sudah basi → Jalankan kembali seluruh transaksi dan semua logika aplikasi yang memilih SQL tersebut.
• Mencoba ulang seketika tanpa batas → Permintaan yang padat akan bertabrakan secara bersamaan dan melipatgandakan beban database → Gunakan jittered backoff, batas jumlah percobaan maksimum, dan batas waktu total.
• Mengirim notifikasi sebelum commit → Percobaan ulang serialisasi dapat mengirim notifikasi lebih dari sekali → Tunda efek eksternal dan gunakan outbox transaksional dengan konsumen yang idempoten.
• Memelihara penghitung sambil membiarkan penulisan detail secara pintas (bypass) → active_count bergeser dari jumlah on-call yang sebenarnya dan kondisinya kehilangan makna → Wajibkan setiap jalur penulisan menggunakan protokol transaksi yang sama dan lakukan rekonsiliasi secara berkelanjutan.

Pertanyaan Lanjutan dan Tanggapannya

Pertanyaan Lanjutan 1: Mengapa satu UPDATE atomik dapat mencegah inventaris bernilai negatif tetapi tidak secara otomatis menyelesaikan masalah ini?

Satu baris inventaris dapat menggunakan UPDATE inventory SET stock = stock - 1 WHERE stock > 0. Kondisi dan target penulisan berada pada baris yang sama, sehingga pembaru yang konkuren memeriksa ulang kondisi tersebut pada versi terbaru. Di sini, kondisinya adalah hitungan lintas-baris sementara penulisannya menyentuh satu dokter. Untuk mendapatkan properti yang sama, materialisasikan hitungan tersebut pada satu baris roster atau gunakan Serializable untuk mendeteksi dependensi predikat.

Pertanyaan Lanjutan 2: Bagaimana jika tingkat kegagalan Serializable tinggi?

Pertama, persingkat transaksi, beri indeks pada predikat, hapus panggilan jaringan dari transaksi, dan ukur konflik per shift. Jika beberapa shift yang padat menyumbang sebagian besar error 40001, pindahkan hanya operasi tulis tersebut ke pembaruan kondisional roster sehingga persaingan mengantre pada satu baris. Jika setiap shift mengalami persaingan yang tinggi, evaluasi kembali operasi batch dan batasan transaksi. Batas percobaan ulang yang lebih besar bukanlah pengganti untuk analisis kapasitas.

Pertanyaan Lanjutan 3: Bisakah batasan CHECK saja menjamin bahwa satu dokter tetap on-call?

Pemeriksaan yang ditempatkan pada satu baris doctor_shifts dapat memvalidasi kolom pada baris tersebut, tetapi baris tersebut tidak dapat secara independen membuktikan bahwa dokter lain tetap aktif untuk shift yang sama. Setelah memproyeksikan invarian ke active_count pada roster, CHECK (active_count >= 1) menjadi perlindungan tingkat database. Detail dan penghitung tetap perlu diubah dalam transaksi yang sama dan direkonsiliasi.

Pertanyaan Lanjutan 4: Apa yang terjadi jika layanan mengalami crash setelah menurunkan hitungan roster?

Jika penghitung dan status dokter berada dalam satu transaksi database, sesi yang terputus akan me-rollback transaksi yang belum di-commit, sehingga perubahan yang hanya setengah jalan tidak dapat tersimpan. Jika commit berhasil tetapi klien kehilangan responsnya, hasilnya tidak diketahui. Percobaan ulang harus menyertakan ID operasi bisnis dan memeriksa status dokter saat ini sebelum menghitung tindakan cuti yang sama lagi.

Pertanyaan Lanjutan 5: Bagaimana jika aturan tersebut mencakup dokter yang disimpan dalam dua database berbeda?

Serializable pada satu database dan kunci baris hanya melindungi data yang terlihat oleh database tersebut. Pilih satu batasan penulisan yang otoritatif, seperti layanan roster yang konsisten kuat yang statusnya diproyeksikan secara asinkron ke database lain, atau gunakan transaksi terdistribusi dan terima konsekuensi koordinasi, ketersediaan, serta latensinya. Jika kedua database mengambil keputusan secara lokal dan menggabungkannya secara asinkron, rancangan tersebut harus secara eksplisit mengizinkan pelanggaran sementara dan menentukan kompensasi; pembuktian keamanan satu database tidak lagi berlaku.