Lompat ke konten

Skenario

Setiap anomali klasik sebagai jadwal yang bisa dijalankan, dengan penggambaran yang membuat risikonya jelas dan daftar level yang mengizinkannya.

  • Membaca nilai yang sudah di-rollback

    dirty-read

    Mendokumentasikan: Dirty read P1 ada dalam daftar ANSI SQL-92

    Situasinya: Sebuah pengembalian dana sedang diproses. Transaksi pengembalian itu sudah menurunkan saldo menjadi 0 ketika sebuah query pelaporan membacanya, lalu pengembalian itu gagal dan di-rollback.

    Pelajarannya: ANSI mendefinisikan READ UNCOMMITTED sebagai level yang mengizinkan ini. PostgreSQL menerima namanya lalu memberi Anda READ COMMITTED, jadi query pelaporan melihat 100 di semua level — saldo yang benar-benar commit. MySQL InnoDB adalah kebalikannya: READ UNCOMMITTED-nya sungguhan, dan query itu membaca 0 di sana — saldo yang tidak pernah ada. SQL Server sama sungguhannya. Oracle menolak nama levelnya sama sekali: ia tidak punya READ UNCOMMITTED dan tidak pernah mengizinkan dirty read.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  w1[1=0]
 3  T2  r2[1]
 4  T1  a1
 5  T2  r2[1]
 6  T2  c2

    Diizinkan pada

    PostgreSQL 16
    tidak diizinkan pada level mana pun di mesin ini
    MySQL InnoDB 8.4
    RU
    Microsoft SQL Server 2022
    RU
    Microsoft SQL Server 2022
    RU
    Oracle Database 23ai Free
    tidak diizinkan pada level mana pun di mesin ini

  • Dua transaksi menulis baris yang sama sebelum salah satunya berakhir

    dirty-write

    Mendokumentasikan: Dirty write P0 tidak ada dalam daftar ANSI SQL-92

    Situasinya: Dua administrator menyunting pengaturan yang sama pada waktu yang sama. Suntingan yang pertama masih belum commit ketika yang kedua menulis.

    Pelajarannya: Tulisan kedua menunggu transaksi pertama berakhir, bukan menimpa nilai yang belum commit — first updater wins. Karena transaksi pertama di-rollback, yang kedua melanjutkan dari baris aslinya.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  w1[1=10]
 3  T2  w2[1=20]
 4  T1  a1
 5  T2  c2

    Diizinkan pada

    PostgreSQL 16
    tidak diizinkan pada level mana pun di mesin ini
    MySQL InnoDB 8.4
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Oracle Database 23ai Free
    tidak diizinkan pada level mana pun di mesin ini

  • Baris yang sama, dibaca dua kali, dengan dua nilai berbeda

    non-repeatable-read

    Mendokumentasikan: Non-repeatable read P2 ada dalam daftar ANSI SQL-92

    Situasinya: Sebuah proses checkout membaca ulang harga barang untuk menghitung pajak setelah sebelumnya membacanya untuk menghitung subtotal. Di antara dua pembacaan itu, sebuah perubahan harga commit.

    Pelajarannya: Pada READ COMMITTED setiap statement mengambil snapshot baru, jadi kedua pembacaan itu sah-sah saja berbeda. REPEATABLE READ mengambil satu snapshot untuk seluruh transaksi dan kedua pembacaan mengembalikan 100.

    Buka di partitur
     0  T1  b1
 1  T1  r1[1]
 2  T2  b2
 3  T2  w2[1=150]
 4  T2  c2
 5  T1  r1[1]
 6  T1  c1

    Diizinkan pada

    PostgreSQL 16
    RU, RC
    MySQL InnoDB 8.4
    RU, RC
    Microsoft SQL Server 2022
    RU, RC
    Microsoft SQL Server 2022
    RU, RC
    Oracle Database 23ai Free
    RC

  • Sebuah transfer yang terlihat setengah jadi

    read-skew

    Mendokumentasikan: Read skew A5A tidak ada dalam daftar ANSI SQL-92

    Situasinya: Rekening 1 dan 2 masing-masing berisi 100 dan invariannya adalah totalnya 200. Sebuah audit membaca rekening 1, lalu transfer 50 dari rekening 1 ke rekening 2 commit, lalu audit itu membaca rekening 2.

    Pelajarannya: Audit itu membaca 100 lalu 150 dan melaporkan total 250. Tidak ada pembacaan yang melihat data belum commit dan kedua nilai itu sudah commit — hanya saja tidak pada waktu yang sama. Inilah sebabnya sebuah laporan butuh satu snapshot, bukan dua pembacaan yang masing-masing benar.

    Buka di partitur
     0  T1  b1
 1  T1  r1[1]
 2  T2  b2
 3  T2  w2[1=50]
 4  T2  w2[2=150]
 5  T2  c2
 6  T1  r1[2]
 7  T1  c1

    Diizinkan pada

    PostgreSQL 16
    RU, RC
    MySQL InnoDB 8.4
    RU, RC
    Microsoft SQL Server 2022
    RU, RC
    Microsoft SQL Server 2022
    RU, RC
    Oracle Database 23ai Free
    RC

  • Dua pengurangan stok menjadi satu

    lost-update

    Mendokumentasikan: Lost update P4 tidak ada dalam daftar ANSI SQL-92

    Situasinya: Stok sepuluh unit. Dua pesanan masing-masing membaca stok, mengurangi satu, lalu menuliskannya kembali. Yang tercatat sembilan unit, dan satu unit terjual dua kali.

    Pelajarannya: Tidak ada transaksi yang salah bila dilihat sendiri, tidak ada kesalahan yang muncul, dan hitungannya keliru. READ COMMITTED menerapkan ulang tulisan kedua ke versi yang baru commit, jadi ia menulis 9 di atas 9. REPEATABLE READ menolaknya dan membatalkan dengan 40001 — kesalahan yang bisa dicoba ulang oleh kode Anda. MySQL InnoDB mengizinkannya di REPEATABLE READ juga: DML-nya selalu bekerja pada baris terbaru yang commit dan tidak pernah menaikkan serialization error, jadi jadwal yang gagal berisik di PostgreSQL berhasil diam-diam di sana.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  r1[1]
 3  T2  r2[1]
 4  T1  w1[1=9]
 5  T1  c1
 6  T2  w2[1=9]
 7  T2  c2

    Diizinkan pada

    PostgreSQL 16
    RU, RC
    MySQL InnoDB 8.4
    RU, RC, RR
    Microsoft SQL Server 2022
    RU, RC
    Microsoft SQL Server 2022
    RU, RC
    Oracle Database 23ai Free
    RC

  • Dua pengurangan yang sama, dengan barisnya di-lock

    lost-update-locked

    Mendokumentasikan: Lost update P4 tidak ada dalam daftar ANSI SQL-92

    Situasinya: Dua pesanan yang sama, tetapi masing-masing membaca stok dengan SELECT ... FOR UPDATE sebelum menuliskannya kembali.

    Pelajarannya: Locking read pesanan kedua menunggu yang pertama commit. Pada READ COMMITTED ia lalu mengembalikan nilai baru, 9, jadi pengurangan kedua dihitung dari apa yang benar-benar ada. Pada REPEATABLE READ, locking read atas baris yang berubah di bawah snapshot justru dibatalkan dengan 40001 — perlindungan yang sama, disampaikan sebagai kesalahan.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  r1[1]•
 3  T2  r2[1]•
 4  T1  w1[1=9]
 5  T1  c1
 6  T2  w2[1=8]
 7  T2  c2

    Diizinkan pada

    PostgreSQL 16
    tidak diizinkan pada level mana pun di mesin ini
    MySQL InnoDB 8.4
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Oracle Database 23ai Free
    tidak diizinkan pada level mana pun di mesin ini

  • Sebuah pemesanan yang muncul di tengah satu transaksi

    phantom-read

    Mendokumentasikan: Phantom read P3 ada dalam daftar ANSI SQL-92

    Situasinya: Slot 1 dan 2 dari kalender lima slot sudah dipesan. Sebuah laporan menghitung jumlah pemesanan, lalu seseorang memesan slot 3 dan commit, lalu laporan itu menghitung lagi untuk menampilkan totalnya.

    Pelajarannya: Pada READ COMMITTED hitungan kedua mengembalikan tiga baris padahal yang pertama dua. REPEATABLE READ pada PostgreSQL mencegah ini — yang tidak diwajibkan standar SQL untuk level itu, dan justru itulah sebabnya nama level tidak bisa dipercaya antar mesin. SQL Server menutup perdebatan itu: REPEATABLE READ-nya memegang shared lock atas baris yang dibaca tetapi tidak bisa me-lock baris yang belum ada, jadi phantom muncul di sana dan tidak di PostgreSQL.

    Buka di partitur
     0  T1  b1
 1  T1  r1[P:1..5]
 2  T2  b2
 3  T2  i2[3=1]
 4  T2  c2
 5  T1  r1[P:1..5]
 6  T1  c1

    Diizinkan pada

    PostgreSQL 16
    RU, RC
    MySQL InnoDB 8.4
    RU, RC
    Microsoft SQL Server 2022
    RU, RC, RR
    Microsoft SQL Server 2022
    RU, RC, RR
    Oracle Database 23ai Free
    RC

  • Jadwal jaga yang menjadi kosong

    write-skew

    Mendokumentasikan: Write skew A5B tidak ada dalam daftar ANSI SQL-92

    Situasinya: Dua dokter sedang berjaga dan setidaknya satu harus tetap berjaga. Masing-masing membuka jadwal, melihat bahwa yang lain berjaga, lalu mengeluarkan dirinya. Keduanya commit.

    Pelajarannya: Inilah write skew: anomali yang tidak ada dalam daftar ANSI. REPEATABLE READ pada PostgreSQL adalah snapshot isolation, dan mengizinkannya: kedua transaksi commit dan tidak ada yang berjaga. Hanya SERIALIZABLE yang menangkapnya, dan caranya dengan membatalkan transaksi kedua yang commit karena read/write dependency — bukan dengan memblokir. MySQL InnoDB juga mengizinkannya di REPEATABLE READ, dan di SERIALIZABLE ia tidak mendeteksi apa pun — ia deadlock. SQL Server mengizinkannya di SNAPSHOT. Dan Oracle mengakhiri perdebatan: ia mengizinkannya di level yang bernama SERIALIZABLE, karena SERIALIZABLE pada Oracle adalah snapshot isolation.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  r1[P:1..2]
 3  T2  r2[P:1..2]
 4  T1  w1[1=0]
 5  T2  w2[2=0]
 6  T1  c1
 7  T2  c2

    Diizinkan pada

    PostgreSQL 16
    RU, RC, RR
    MySQL InnoDB 8.4
    RU, RC, RR
    Microsoft SQL Server 2022
    RU, RC, SI
    Microsoft SQL Server 2022
    RU, RC, SI
    Oracle Database 23ai Free
    RC, SER

  • Jadwal jaga yang sama, dibaca dengan FOR UPDATE

    write-skew-locked

    Mendokumentasikan: Write skew A5B tidak ada dalam daftar ANSI SQL-92

    Situasinya: Dua dokter yang sama, tetapi masing-masing membaca kedua baris dengan SELECT ... FOR UPDATE sebelum menulis, yang biasanya disarankan ketika Anda tidak bisa memakai SERIALIZABLE.

    Pelajarannya: Lock itu men-serialkan kedua transaksi. Locking read dokter kedua menunggu, lalu mengembalikan jadwal yang di dalamnya dokter pertama sudah keluar, sehingga jadwal ini setara dengan menjalankan T1 lalu T2 — tidak ada lagi anomali yang bisa diizinkan basis data. Jadwal jaga tetap kosong, karena jadwal ini menulis tanpa memeriksa ulang apa yang dikembalikan locking read itu; bug yang tersisa itu milik aplikasi, dan sekarang terlihat pada nilai yang terbaca. Pada REPEATABLE READ, locking read itu dibatalkan dengan 40001.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  r1[1]•
 3  T1  r1[2]•
 4  T2  r2[1]•
 5  T1  w1[1=0]
 6  T1  c1
 7  T2  r2[2]•
 8  T2  w2[2=0]
 9  T2  c2

    Diizinkan pada

    PostgreSQL 16
    tidak diizinkan pada level mana pun di mesin ini
    MySQL InnoDB 8.4
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Oracle Database 23ai Free
    tidak diizinkan pada level mana pun di mesin ini

  • Dua pemesanan untuk kalender yang kosong ketika keduanya melihat

    phantom-insert-race

    Mendokumentasikan: Write skew A5B tidak ada dalam daftar ANSI SQL-92

    Situasinya: Belum ada yang memesan slot 1 sampai 5. Dua orang masing-masing memastikan rentang itu kosong, lalu masing-masing memesan slot yang berbeda di dalamnya.

    Pelajarannya: Kedua range read tidak mengembalikan apa pun, dan kedua insert berhasil, jadi kalender berakhir dengan dua pemesanan padahal setiap pemesan yakin hanya akan ada satu. Tidak ada baris yang ditulis dua kali, jadi tidak ada yang berkonflik — ini write skew yang berpakaian phantom, dan hanya SERIALIZABLE yang menghentikannya.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  r1[P:1..5]
 3  T2  r2[P:1..5]
 4  T1  i1[1=1]
 5  T2  i2[2=1]
 6  T1  c1
 7  T2  c2

    Diizinkan pada

    PostgreSQL 16
    RU, RC, RR
    MySQL InnoDB 8.4
    RU, RC, RR
    Microsoft SQL Server 2022
    RU, RC, RR, SI
    Microsoft SQL Server 2022
    RU, RC, RR, SI
    Oracle Database 23ai Free
    RC, SER

  • Dua transfer yang me-lock rekening yang sama dengan urutan berlawanan

    deadlock

    Mendokumentasikan respons mesin, bukan sebuah anomali

    Situasinya: Dua transfer berjalan bersamaan. Yang satu memindahkan uang dari rekening 1 ke rekening 2 dan me-lock keduanya dengan urutan itu; yang lain memindahkan dari 2 ke 1 dan me-lock dengan urutan sebaliknya. Masing-masing memegang apa yang berikutnya dibutuhkan yang lain.

    Pelajarannya: Tidak ada transaksi yang melakukan hal aneh, dan tidak ada isolation level yang mencegah ini — me-lock dengan urutan yang konsisten adalah tugas aplikasi. Yang berbeda adalah jawaban mesinnya. PostgreSQL dan MySQL InnoDB me-rollback satu transaksi dan menyebutkannya, sehingga yang lain bisa lanjut. SQL Server memilih korbannya berdasarkan perkiraan biaya internal, dan Oracle menyatakan terang-terangan bahwa session mana pun bisa mendapat error itu — jadi untuk ketiganya model ini menolak menyebut siapa yang kalah daripada mengarangnya, dan Oracle pun hanya me-rollback statement-nya, membiarkan transaksinya tetap terbuka.

    Buka di partitur
     0  T1  b1
 1  T2  b2
 2  T1  r1[1]•
 3  T2  r2[2]•
 4  T1  r1[2]•
 5  T2  r2[1]•
 6  T1  c1
 7  T2  c2

    Diizinkan pada

    PostgreSQL 16
    tidak diizinkan pada level mana pun di mesin ini
    MySQL InnoDB 8.4
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Microsoft SQL Server 2022
    tidak diizinkan pada level mana pun di mesin ini
    Oracle Database 23ai Free
    tidak diizinkan pada level mana pun di mesin ini