Transaksi dan isolasi

Transaksi adalah satu satuan kerja yang seluruhnya terjadi atau tidak terjadi sama sekali.

BEGIN;
  UPDATE account SET cents = cents - 1000 WHERE id = 1;
  UPDATE account SET cents = cents + 1000 WHERE id = 2;
COMMIT;

(account adalah contoh buku teks, bukan salah satu dari empat tabel kita — transfer dua sisi adalah hal terpendek yang menunjukkan kenapa atomisitas bukan pilihan.)

Kedua mesin berjalan dalam autocommit secara bawaan: pernyataan di luar transaksi eksplisit adalah transaksinya sendiri. Jadi kode di atas tanpa BEGIN adalah dua transaksi terpisah, dan kegagalan di antaranya menghilangkan uangnya. Sebagian besar pustaka koneksi dan ORM mengurus ini untukmu — artinya hal pertama yang perlu kamu tahu tentang tumpukanmu adalah di mana batas transaksinya sebenarnya berada.

ROLLBACK membatalkan segalanya sejak BEGIN. SAVEPOINT membuatmu bisa membatalkan sebagiannya, dan begitulah ORM mewujudkan transaksi bersarang.

Isolasi itu melindungimu dari apa

Ketika transaksi bertumpang tindih, ada empat hal yang bisa salah. Namanya pantas diketahui karena begitulah level-levelnya didefinisikan:

Dan level-levelnya, makin ke bawah makin ketat:

Bawaannya berbeda, dan itu penting

Dua akibat untuk kode yang harus jalan di keduanya:

SELECT yang diulang dalam satu transaksi berperilaku berbeda. Pada bawaan PostgreSQL dia melihat snapshot baru di setiap pernyataan, jadi sebuah baris bisa berubah di bawahmu. Pada bawaan MySQL dia melihat snapshot dari pembacaan pertama, jadi tidak bisa. Laporan yang menjalankan beberapa query dan mengharapkan hasilnya sepakat itu benar karena kebetulan di MySQL dan salah secara bawaan di PostgreSQL.

REPEATABLE READ dan SERIALIZABLE PostgreSQL bisa membatalkan transaksimu. Kamu dapat could not serialize access due to concurrent update — SQLSTATE 40001 — dan transaksinya harus dicoba lagi dari awal. Ini bukan error untuk dicatat lalu dilupakan; ini hasil normal yang harus ditangani pemanggilnya.

Lost update, dan dua cara mencegahnya

Ini bug konkurensi yang benar-benar muncul di aplikasi.

-- Dua permintaan menjalankan ini bersamaan. Keduanya membaca 10, keduanya menulis 11.
-- Satu penambahan hilang, dan tidak ada yang gagal.
SELECT quantity FROM order_item WHERE order_id = 1 AND product_id = 5;   -- 10
UPDATE order_item SET quantity = 11 WHERE order_id = 1 AND product_id = 5;

Tiga perbaikan:

-- 1. Lakukan aritmetikanya di basis data. Benar di setiap isolation level.
UPDATE order_item SET quantity = quantity + 1 WHERE order_id = 1 AND product_id = 5;

-- 2. Lock pesimistis: ambil barisnya dan buat transaksi lain menunggu.
BEGIN;
  SELECT quantity FROM order_item
   WHERE order_id = 1 AND product_id = 5
     FOR UPDATE;
  -- ... hitung di aplikasi ...
  UPDATE order_item SET quantity = :new WHERE order_id = 1 AND product_id = 5;
COMMIT;

-- 3. Lock optimistis: kolom versi, dan pastikan kamu memperbarui yang kamu baca.
UPDATE order_item SET quantity = :new, version = version + 1
 WHERE order_id = 1 AND product_id = 5 AND version = :version_yang_kubaca;
-- 0 baris terpengaruh berarti orang lain sampai lebih dulu. Baca ulang lalu coba lagi.

Pilihan 1 setiap kali nilai barunya adalah fungsi dari nilai lamanya. Pilihan 3 mengembang lebih baik daripada pilihan 2 di bawah perebutan, karena tidak ada yang menunggu — tapi kamu harus menangani percobaan ulangnya.

Pembacaan yang mengunci, dan dua klausa yang membuat antrean bisa jalan

SELECT ... FOR UPDATE;     -- eksklusif: tidak ada yang boleh baca-untuk-update atau menulis
SELECT ... FOR SHARE;      -- bersama: yang lain boleh ikut membaca, tak ada yang boleh menulis

Secara bawaan pembacaan yang mengunci akan menunggu siapa pun yang memegang barisnya. Kedua mesin membolehkanmu mengubah itu:

SELECT * FROM "order" WHERE status = 'new'
ORDER BY placed_at LIMIT 1
FOR UPDATE SKIP LOCKED;    -- lewati baris yang dipegang orang lain; ambil yang bebas berikutnya

SELECT ... FOR UPDATE NOWAIT;   -- langsung gagal alih-alih menunggu

SKIP LOCKED adalah cara kamu membangun antrean kerja di SQL, dan dia didukung PostgreSQL 9.5+ dan MySQL 8.0+. Tanpa dia, sepuluh worker yang memeriksa tabel yang sama semuanya tertahan di belakang baris yang sama dan kamu punya antrean berkapasitas satu.

Gap lock: perilaku tambahan MySQL

Pada REPEATABLE READ, InnoDB tidak hanya mengunci baris yang ditemukannya — dia mengunci celah antar nilai index, untuk mencegah transaksi lain menyisipkan baris yang akan mengubah hasilnya. Ini disebut next-key lock.

-- InnoDB mengunci rentangnya, jadi INSERT bersamaan dengan placed_at = '2026-01-15' menunggu
SELECT * FROM `order`
 WHERE placed_at BETWEEN '2026-01-01' AND '2026-01-31'
   FOR UPDATE;

Ini mencegah phantom, dan dia juga menyebabkan deadlock yang mengejutkan orang, karena transaksi saling menghalangi atas baris yang tidak ada. Kalau beban kerja MySQL mengalami deadlock dengan cara yang tidak masuk akal dari penalaran tingkat baris, gap lock adalah tempat pertama yang perlu dilihat. READ COMMITTED mematikan sebagian besar darinya, dengan biaya kehilangan jaminannya.

PostgreSQL tidak punya gap lock. REPEATABLE READ-nya mendapat perlindungan yang sama dari snapshot, dan SERIALIZABLE mendeteksi konfliknya saat commit alih-alih mencegahnya dengan lock.

Deadlock itu normal

Dua transaksi yang masing-masing memegang apa yang diinginkan yang lain. Mesinnya mendeteksi siklusnya, mematikan salah satu, lalu mengembalikan error:

Deadlock bukan bug yang harus dihapuskan; dia kondisi yang harus ditangani. Coba ulang seluruh transaksinya — mengulang pernyataannya saja tak bermakna karena transaksinya sudah di-rollback.

Kurangi frekuensinya dengan membuat setiap transaksi menyentuh baris dalam urutan yang sama. Sebagian besar deadlock aplikasi adalah dua jalur kode yang memperbarui dua tabel yang sama dalam urutan berlawanan.

Transaksi panjang itu masalah operasional

Transaksi yang dibiarkan terbuka beberapa menit berbiaya lebih besar daripada lock yang dipegangnya.

• PostgreSQL: VACUUM tidak bisa membuang versi baris yang mungkin masih dibutuhkan transaksi terbuka, jadi baris mati menumpuk — table bloat. Satu transaksi yang terlupa bisa menumbuhkan tabel selama berjam-jam. Pantau pg_stat_activity untuk state = 'idle in transaction'.
• MySQL: undo log-nya bertumbuh dengan alasan yang sama, dan panjang history list ikut bertambah.

Jaga transaksi tetap pendek dan jangan menahan satu transaksi terbuka melewati panggilan jaringan ke layanan lain. Itu pola yang mengubah pihak ketiga yang lambat jadi insiden basis data.

Satu perbedaan lagi: DDL

Karena itulah migrasi PostgreSQL bisa ditulis sebagai satu langkah atomik dan migrasi MySQL tidak. Di MySQL, setiap pernyataan harus aman secara mandiri kalau sudah diterapkan sendiri — dan itu disiplin yang sama yang dianjurkan materi berikutnya.

Yang perlu dibawa pulang

• PostgreSQL bawaannya READ COMMITTED, MySQL REPEATABLE READ. Kode yang sama punya anomali yang berbeda.
• Level yang lebih ketat di PostgreSQL membatalkan transaksi dengan 40001. Tangani percobaan ulangnya.
• Cegah lost update dengan menghitung di basis data, FOR UPDATE, atau kolom versi.
• SKIP LOCKED adalah yang membuat antrean kerja di SQL mungkin.
• Gap lock MySQL mencegah phantom dan menyebabkan deadlock yang mengejutkan.
• Coba ulang transaksinya saat deadlock, dan urutkan penulisanmu secara konsisten untuk menguranginya.
• DDL MySQL melakukan commit implisit; migrasi yang separuh diterapkan tetap terterap.