Mencegah Write Skew dengan Pengasingan Transaksi

Gesaan dan Konteks yang Berkenaan

Jadual doctor_shifts(team_id, shift_date, doctor_id, on_call) menyimpan status atas panggilan. Peraturan perniagaan menyatakan bahawa setiap pasukan dan syif mesti mengekalkan sekurang-kurangnya 1 doktor atas panggilan. Alice dan Bob kedua-duanya aktif, dan dua permintaan cuba mengeluarkan doktor yang berbeza daripada tugas atas panggilan secara serentak. Setiap transaksi terlebih dahulu mengira doktor yang aktif. Jika jumlahnya melebihi 1, ia mengemas kini baris doktornya sendiri.

Soalan ini disasarkan kepada PostgreSQL 18. Kiraan aktif awal ialah 2. Kedua-dua transaksi menyelesaikan bacaan masing-masing sebelum mana-mana transaksi melakukan commit, dan kemudian mengemas kini baris yang berbeza. Nombor dan skema ini merupakan andaian temu duga, bukan model data penjagaan kesihatan universal. Masalah terasnya ialah predikat berbilang baris count(on_call) >= 1; aliran kerja kelulusan, zon masa, dan kebenaran adalah di luar skop.

Corak yang sama wujud dalam peraturan seperti inventori yang tidak boleh menjadi negatif, akaun yang perlu mengekalkan sekurang-kurangnya satu pelulus, atau kluster yang perlu mengekalkan sekurang-kurangnya satu nod utama (primary). Mengatakan "masukkan ke dalam transaksi" adalah tidak lengkap. Keatoman (atomicity) menjadikan satu transaksi berjaya atau gagal sebagai satu unit. Tahap pengasingan menentukan apa yang boleh diperhatikan oleh transaksi serentak dan sama ada pangkalan data menolak hasil yang tidak mempunyai penjelasan bersiri yang sah.

Perkara yang Dinilai oleh Penemu Duga

Isyarat pertama ialah mengenalpasti write skew. Kedua-dua transaksi membaca predikat yang sama tetapi menulis ke baris yang berbeza, jadi ia tidak menghasilkan konflik penulisan baris sama yang biasa. Setiap transaksi melepasi semakannya secara terasing, namun hasil gabungannya meninggalkan sifar doktor aktif. Menyifatkan ini sebagai dirty read atau lost update yang mudah akan membawa kepada pembetulan yang salah.

Isyarat kedua ialah memisahkan nama standard daripada pelaksanaan pangkalan data. Read Committed dalam PostgreSQL mengambil snapshot baharu bagi setiap penyataan. Repeatable Read menggunakan snapshot transaksi yang stabil dan dilaksanakan sebagai pengasingan snapshot, yang masih membenarkan anomali pensiratan (serialization anomalies). Serializable menjejaki kebergantungan baca/tulis di atas bacaan gaya snapshot yang serupa dan menggugurkan transaksi apabila hasilnya tidak sepadan dengan sebarang urutan bersiri. Tahap pengasingan dengan nama yang sama boleh menggunakan semantik penguncian dan snapshot yang berbeza dalam pangkalan data lain.

Isyarat ketiga ialah memilih sempadan keserentakan daripada invarian tersebut. Apabila invarian merangkumi beberapa baris, mengunci "doktor yang bakal saya kemas kini" tidak menyebabkan kedua-dua transaksi bertembung. Reka bentuk yang selamat mesti memaksa transaksi bersaing pada satu objek yang boleh dikunci, mengurangkan peraturan tersebut kepada syarat atomik pada satu baris, atau membiarkan Serializable mengesan konflik tersebut.

Akhir sekali, penemu duga melihat pengendalian kegagalan. Kedua-dua Serializable dan penguncian eksplisit boleh menggugurkan transaksi. Kod pensiratan PostgreSQL yang biasa ialah 40001; susunan kunci yang tidak konsisten juga boleh menghasilkan 40P01. Jawapan yang mantap mencuba semula transaksi yang lengkap, mengehadkan bilangan percubaan, dan memindahkan emel atau kesan luaran lain ke laluan idempoten selepas commit yang berjaya.

Soalan untuk Dijelaskan Sebelum Menjawab

• Pangkalan data dan versi manakah yang digunakan? Jawapan ini menyasarkan PostgreSQL 18. MySQL, SQL Server, dan pangkalan data teragih memerlukan semakan baharu terhadap tahap pengasingan dan ralat percubaan semula dengan nama yang sama.
• Adakah peraturan ini merangkumi tepat satu syif? Invarian yang berkuncikan (team_id, shift_date) boleh mempunyai satu titik persaingan yang stabil bagi setiap syif. Peraturan yang merangkumi pelbagai wilayah atau pangkalan data tidak boleh dilindungi oleh kunci baris dalam satu pangkalan data sahaja.
• Laluan manakah yang boleh mengubah status atas panggilan? Permohonan cuti, pertukaran syif, import, dan pembaikan pentadbiran semuanya mesti mematuhi protokol yang sama. Membetulkan satu API sahaja meninggalkan laluan penulisan pintas yang boleh merosakkan pembilang atau melangkau kunci.
• Adakah konflik berlaku sekali-sekala atau sentiasa hangat? Serializable dengan percubaan semula selalunya paling jelas pada persaingan rendah. Kemas kini bersyarat satu baris boleh mengelakkan kerja yang sia-sia pada syif yang hangat, tetapi ia juga menjadikan syif tersebut titik tumpuan (hotspot) yang disirikan.
• Berapa lamakah permintaan dibenarkan menunggu? Kunci pesimistik menunggu pemegang kunci. Belanjawan kependaman yang ketat memerlukan transaksi pendek, had masa menunggu kunci, dan hasil eksplisit "keadaan berubah, cuba semula".
• Adakah transaksi melaksanakan kesan sampingan luaran? Percubaan semula akan menjalankan semula logik transaksi. Emel, panggilan perkhidmatan jadual tugas, atau mesej bukan transaksional mesti berlaku selepas commit atau menggunakan outbox transaksional dengan penggunaan yang idempoten.

Kerangka Jawapan 30 Saat

"Ini ialah write skew: kedua-dua transaksi membuat keputusan berdasarkan set atas panggilan yang sama tetapi mengemas kini baris doktor yang berbeza, jadi snapshot Repeatable Read yang stabil pun boleh membenarkan kedua-duanya melakukan commit. PostgreSQL Serializable menjejaki kebergantungan baca/tulis tersebut dan menggugurkan sekurang-kurangnya satu transaksi; aplikasi mesti menjalankan semula keseluruhan keputusan pada 40001. Tanpa Serializable, saya akan memetakan setiap syif kepada satu baris jadual tugas (roster), mengurangkan nilainya secara atomik di bawah Read Committed hanya apabila active_count > 1, dan mengemas kini doktor dalam transaksi yang sama. Pilihan lain ialah mengunci baris jadual tugas sebelum mengira semula. Saya akan menguji dengan dua sambungan dan satu halangan (barrier) selepas kedua-dua bacaan, membuktikan bahawa pengasingan yang lemah menghasilkan sifar manakala reka bentuk yang selamat mengekalkan tepat satu doktor."

Jawapan Mendalam Langkah demi Langkah

Mulakan dengan sejarah serentak. Pada mulanya, 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 dahulu dalam pelaksanaan bersiri, T2 akan membaca 1 dan menolak perubahan tersebut. Jika T2 berjalan dahulu, T1 akan menolaknya. Hasil sebenar ialah sifar, yang tidak bersamaan dengan mana-mana urutan bersiri dan oleh itu merupakan anomali pensiratan. Perbezaan utama berbanding lost update ialah T1 dan T2 tidak pernah menulis ganti baris yang sama, jadi kunci penulisan tahap baris biasa tidak berkonflik secara semula jadi.

Di bawah Read Committed, setiap penyataan kiraan memerhatikan data yang telah dicommit apabila penyataan tersebut bermula. Dengan halangan dalam gesaan, kedua-dua penyataan membaca 2 sebelum mana-mana kemas kini berlaku. Penyataan UPDATE menyasarkan baris yang berbeza dan kedua-duanya boleh melakukan commit. Penyataan yang kemudian akan menerima snapshot yang lebih baharu, tetapi PostgreSQL tidak membatalkan keputusan aplikasi yang telah dibuat secara retroaktif.

PostgreSQL Repeatable Read mengekalkan satu snapshot untuk transaksi. Ia menghalang nonrepeatable read dan phantom read dalam pelaksanaan tersebut, tetapi membenarkan anomali pensiratan. Kedua-dua transaksi mengemas kini rantaian versi yang berbeza, jadi tiada kemas kini serentak pada baris yang sama yang mencetuskan pengembalian (rollback) dan kedua-duanya boleh melakukan commit. MVCC menjelaskan bagaimana pembaca mengelak daripada menyekat penulis. Ia tidak sinonim dengan Serializable, dan ia tidak membuat inferens terhadap peraturan perniagaan "sekurang-kurangnya satu doktor kekal atas panggilan."

Pilihan selamat yang pertama ialah 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;

Apabila permintaan bertindih, PostgreSQL mengesan bahawa bacaan predikat dan penulisan serentak membentuk kebergantungan yang tidak boleh disirikan. Ia tidak boleh membenarkan kedua-dua transaksi melakukan commit. Transaksi yang gagal mengembalikan SQLSTATE 40001. Percubaan semula mesti bermula sebelum BEGIN dan menjalankan semula kiraan serta keputusan untuk mengemas kini. Mencuba semula hanya pada UPDATE terakhir akan mengekalkan keputusan lapuk. Gunakan undur masa berjulat (jittered backoff), had percubaan maksimum, dan had masa keseluruhan kerana percubaan semula boleh berkonflik lagi di bawah persaingan tinggi.

Serializable membolehkan aplikasi menyatakan peraturan berdasarkan predikat sebenarnya tanpa perlu mencipta kunci secara manual untuk setiap invarian. Kosnya ialah overhed penjejakan kebergantungan dan percubaan semula akibat konflik. Set bacaan yang lebih besar, transaksi yang lebih panjang, dan persaingan yang tertumpu secara umumnya mewujudkan lebih banyak peluang pertindihan. Indeks pada (team_id, shift_date), transaksi pendek, dan ketiadaan masa menunggu rangkaian sebelum commit mengurangkan tetingkap tersebut.

Pilihan kedua memetakan invarian berbilang baris kepada satu baris, on_call_rosters(team_id, shift_date, active_count), dan menggunakan kemas kini 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-dua permintaan kini bersaing pada baris jadual tugas yang sama. Selepas menunggu kemas kini serentak di bawah PostgreSQL Read Committed, pengemas kini memeriksa semula active_count > 1 terhadap versi baris baharu. Permintaan pertama menukar 2 kepada 1; syarat permintaan kedua gagal dan tidak mengembalikan sebarang baris. CHECK (active_count >= 1) tahap baris boleh menyediakan kawalan keselamatan terakhir. Kosnya ialah setiap laluan pengaktifan, penyahaktifan, import, dan pembaikan mesti mengekalkan pembilang dalam transaksi yang sama. Sistem ini juga perlu menyelaraskan pembilang dengan baris terperinci dan memberi amaran jika berlaku perbezaan (drift), dan bukannya menulis ganti secara senyap.

Jika mengekalkan pembilang tidak diingini, kekalkan baris jadual tugas yang stabil bagi setiap syif. Sebagai langkah pertama dalam transaksi Read Committed, jalankan SELECT ... FOR UPDATE pada baris tersebut, kemudian kira baris terperinci dan kemas kini doktor. Selepas permintaan kedua menunggu, SELECT seterusnya mendapat snapshot yang merangkumi commit pertama. Setiap laluan penulisan mesti mengunci baris jadual tugas yang sama terlebih dahulu, dan transaksi mesti kekal pendek. Variasi tidak selamat yang halus adalah mewujudkan snapshot Repeatable Read yang lama dan kemudian mengunci baris pengawal yang tidak diubah oleh sesiapa: memperoleh FOR UPDATE tidak menyegarkan snapshot transaksi tersebut.

Mengunci semua baris doktor yang aktif secara langsung juga memerlukan ketelitian. Set kunci datang daripada predikat, dan baris baharu, susunan pertanyaan yang berbeza, atau penulisan pintas boleh mengubah hujah keselamatan. Susunan yang tidak konsisten merentasi berbilang kunci baris boleh menyebabkan deadlock. Jika reka bentuk ini diperlukan, peroleh kunci mengikut urutan kunci utama yang stabil dan jalankan semula keseluruhan transaksi pada 40P01. Mengunci hanya "doktor yang saya keluarkan daripada tugas atas panggilan" adalah jelas tidak mencukupi kerana permintaan masih mengunci baris yang berbeza.

Jangan sahkan ini dengan memanggil dua API secara berurutan. Gunakan dua sambungan pangkalan data bebas dan halangan ujian. Kedua-dua sesi memulakan Repeatable Read, mengira baris, mengesahkan bahawa setiap satu membaca 2, dan hanya selepas itu meneruskan kemas kini dan commit. Garis dasar sepatutnya menghasilkan dua commit dan kiraan akhir sifar secara konsisten. Di bawah Serializable, sahkan bahawa dua kejayaan adalah mustahil: satu transaksi menerima 40001, dan percubaan semula sepenuhnya membaca 1 lalu menolak perubahan tersebut. Di bawah reka bentuk jadual tugas, sahkan bahawa tepat satu kemas kini bersyarat mengembalikan baris dan kedua-dua butiran serta active_count berakhir pada 1.

Ulangi kes serentak dengan urutan commit yang dirombak. Tambahkan permintaan pendua untuk doktor yang sama, penyertaan doktor ketiga, pembalikan (rollback) antara dua kemas kini, had masa menunggu kunci, dan kes deadlock. Dalam pengeluaran, pantau 40001, 40P01, percubaan semula, masa menunggu kunci, tempoh transaksi, kadar kegagalan muktamad, dan perbezaan antara pembilang jadual tugas dan baris butiran. Peningkatan mendadak dalam kadar konflik biasanya menandakan titik tumpuan, transaksi yang panjang, atau laluan penulisan baharu yang memasuki sempadan; percubaan semula tanpa had hanya menyembunyikan dan memburukkan masalah tersebut.

Contoh Jawapan Berkualiti Tinggi

"Saya akan mengenal pasti perkara ini sebagai write skew terlebih dahulu. Kedua-dua transaksi menyemak peraturan merentas baris yang sama, tetapi Alice dan Bob mengemas kini baris masing-masing, jadi kunci penulisan tahap baris tidak berkonflik. Berdasarkan jadual di mana kedua-duanya membaca 2 terlebih dahulu, Read Committed boleh membenarkan kedua-duanya melakukan commit. PostgreSQL Repeatable Read memberikan setiap transaksi snapshot yang stabil, namun masih boleh menghasilkan nilai akhir sifar, yang tidak dapat dijelaskan oleh sebarang susunan bersiri.

Pilihan pertama saya adalah menyatakan peraturan tersebut dalam transaksi Serializable. Saya mengira doktor aktif untuk syif tersebut dan mengemas kini hanya apabila kiraan melebihi 1. PostgreSQL menjejaki kebergantungan antara bacaan predikat dan penulisan serentak. Dalam kes ini, kedua-dua transaksi tidak boleh melakukan commit; satu akan menerima 40001. Aplikasi menangkap ralat tersebut dan menjalankan semula kiraan serta kemas kini dari awal dengan dasar percubaan semula yang terhad. Saya mengekalkan pemberitahuan di luar transaksi yang boleh dicuba semula dan memprosesnya daripada outbox idempoten selepas commit.

Bagi syif dengan persaingan berterusan, saya akan mempertimbangkan baris on_call_rosters. Di bawah Read Committed, transaksi permohonan keluar menggunakan UPDATE ... SET active_count = active_count - 1 WHERE active_count > 1 RETURNING ... untuk bersaing pada satu baris tersebut, kemudian mengemas kini butiran doktor. Jika mana-mana penyataan gagal mengubah tepat satu baris, keseluruhan transaksi akan dikembalikan (rollback). Permintaan kedua memeriksa semula syaratnya pada versi baris terkini dan gagal. Komprominya ialah setiap laluan penulisan mesti mengekalkan pembilang tersebut.

Saya akan mengesahkan reka bentuk ini dengan dua sambungan dan halangan yang menetapkan jalinan pelaksanaan (interleaving). Mula-mula saya akan membuktikan bahawa Repeatable Read boleh membenarkan kedua-dua sesi membaca 2 dan mencapai sifar. Kemudian saya akan membuktikan bahawa Serializable menggugurkan tepat satu transaksi dan percubaan semulanya menolak perubahan tersebut. Reka bentuk pembilang sepatutnya membenarkan hanya satu kemas kini bersyarat. Akhir sekali, saya akan memantau kegagalan pensiratan, deadlock, masa menunggu kunci, dan perbezaan pembilang supaya jaminan keselamatan tidak bergantung pada jadual ujian yang bernasib baik semata-mata."

Kesilapan Biasa

• "Ia berada di dalam transaksi, jadi ia selamat" → Keatoman tidak menyatakan apa-apa tentang keterlihatan dan susunan commit merentasi transaksi serentak → Namakan tahap pengasingan dan tuliskan jalinan pelaksanaan yang memecahkan invarian tersebut.
• Menganggap write skew sebagai lost update → Transaksi menulis ke baris yang berbeza, jadi semakan versi pada satu baris tidak meliputi predikat dikongsi mereka → Kenal pasti invarian merentas baris dan gunakan titik persaingan bersama atau Serializable.
• Mendakwa Repeatable Read bersamaan dengan Serializable → Snapshot yang stabil masih boleh menghasilkan hasil gabungan tanpa penjelasan bersiri → Terangkan anomali pensiratan yang dibenarkan oleh PostgreSQL Repeatable Read.
• Mengunci baris doktor bagi setiap transaksi → Kunci milik Alice dan Bob tidak berkonflik → Kunci satu baris jadual tugas, kemas kini satu baris pembilang, atau biarkan SSI mengesan kebergantungan predikat.
• Menambah FOR UPDATE selepas snapshot Repeatable Read yang lama → Mengunci baris pengawal yang tidak berubah tidak menyegarkan snapshot transaksi → Kunci dan baca semula di bawah Read Committed, atau gunakan Serializable atau baris pembilang boleh ubah.
• Mencuba semula hanya COMMIT atau penyataan SQL terakhir selepas 40001 → Keputusan perniagaan masih berpunca daripada snapshot yang lapuk → Jalankan semula transaksi lengkap dan semua logik aplikasi yang memilih SQL tersebut.
• Mencuba semula serta-merta tanpa had → Permintaan bertembung serentak dan meningkatkan beban pangkalan data → Gunakan undur masa berjulat (jittered backoff), had percubaan maksimum, dan had masa keseluruhan.
• Menghantar pemberitahuan sebelum commit → Percubaan semula pensiratan boleh menghantar pemberitahuan lebih daripada sekali → Tangguhkan kesan luaran dan gunakan outbox transaksional dengan pengguna yang idempoten.
• Mengekalkan pembilang sambil membenarkan penulisan butiran pintas → active_count menyimpang daripada kiraan atas panggilan sebenar dan syarat kehilangan makna → Pastikan setiap laluan penulisan menggunakan protokol transaksi yang sama dan selaraskannya secara berterusan.

Soalan Susulan dan Maklum Balas

Soalan Susulan 1: Mengapakah satu UPDATE atomik boleh menghalang inventori negatif tetapi tidak menyelesaikan masalah ini secara automatik?

Satu baris inventori boleh menggunakan UPDATE inventory SET stock = stock - 1 WHERE stock > 0. Syarat dan sasaran penulisan adalah baris yang sama, jadi pengemas kini serentak menyemak semula syarat pada versi terkini. Di sini, syaratnya ialah kiraan merentasi baris manakala penulisan menyentuh satu doktor. Untuk memperoleh sifat yang sama, wujudkan kiraan tersebut pada satu baris jadual tugas atau gunakan Serializable untuk mengesan kebergantungan predikat.

Soalan Susulan 2: Bagaimana jika kadar kegagalan Serializable tinggi?

Mula-mula pendekkan transaksi, indekskan predikat, keluarkan panggilan rangkaian daripada transaksi, dan ukur konflik bagi setiap syif. Jika beberapa syif yang hangat menyumbang kepada kebanyakan ralat 40001, alihkan hanya penulisan tersebut ke kemas kini bersyarat jadual tugas supaya persaingan beratur pada satu baris. Jika setiap syif mengalami persaingan yang tinggi, nilai semula operasi kelompok dan sempadan transaksi. Had percubaan semula yang lebih besar bukan pengganti kepada analisis kapasiti.

Soalan Susulan 3: Bolehkah kekangan CHECK sahaja menjamin bahawa satu doktor kekal atas panggilan?

Semakan yang diletakkan pada satu baris doctor_shifts boleh mengesahkan medan pada baris tersebut, tetapi baris tersebut tidak dapat membuktikan secara bebas bahawa doktor lain kekal aktif untuk syif yang sama. Selepas memetakan invarian ke active_count jadual tugas, CHECK (active_count >= 1) menjadi kawalan keselamatan peringkat pangkalan data. Butiran dan pembilang masih perlu berubah dalam transaksi yang sama dan diselaraskan.

Soalan Susulan 4: Apakah yang berlaku jika perkhidmatan terhenti (crash) selepas mengurangkan kiraan jadual tugas?

Jika pembilang dan status doktor berada dalam satu transaksi pangkalan data, sesi yang terputus akan mengembalikan (rollback) transaksi yang belum dicommit, jadi perubahan separuh jalan tidak akan kekal. Jika commit berjaya tetapi klien terlepas respons, hasilnya tidak diketahui. Percubaan semula perlu membawa ID operasi perniagaan dan memeriksa keadaan semasa doktor sebelum mengira tindakan keluar yang sama sekali lagi.

Soalan Susulan 5: Bagaimana jika peraturan tersebut merangkumi doktor yang disimpan dalam dua pangkalan data berbeza?

Serializable pangkalan data tunggal dan kunci baris hanya melindungi data yang kelihatan pada pangkalan data tersebut. Pilih satu sempadan penulisan yang berwibawa (authoritative), seperti perkhidmatan jadual tugas dengan ketekalan kuat yang keadaannya dipetakan secara tak segerak ke pangkalan data lain, atau perkenalkan transaksi teragih dan terima kos koordinasi, ketersediaan, dan kependamannya. Jika kedua-dua pangkalan data membuat keputusan secara tempatan dan bergabung secara tak segerak, reka bentuk tersebut mesti membenarkan pelanggaran sementara secara eksplisit dan mentakrifkan pampasan; bukti keselamatan pangkalan data tunggal tidak lagi terpakai.