Transaksi SQLite: Penjelasan BEGIN, COMMIT, ROLLBACK

Transaksi Adalah Paket Semua atau Tidak Sama Sekali

Transaksi mengelompokkan beberapa pernyataan sehingga semuanya berlaku, atau tidak ada satu pun yang berlaku. Jika ada yang salah di tengah jalan, kamu bisa melakukan rollback dan database kembali persis seperti semula.

Contoh klasiknya adalah memindahkan uang:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

Kedua UPDATE itu satu paket. Jika database crash di antara keduanya, uang Ada berkurang 2000 sen dan Boris tidak mendapat tambahan apa pun. Membungkusnya dengan BEGIN ... COMMIT membuat pasangan itu atomik: keduanya terjadi, atau tidak satu pun.

Autocommit: Default yang Sudah Kamu Pakai

Setiap pernyataan SQL yang sudah kamu jalankan sejauh ini adalah sebuah transaksi. Secara default SQLite berada di mode autocommit: setiap pernyataan dibungkus BEGIN dan COMMIT implisitnya sendiri.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');
INSERT INTO notes (body) VALUES ('third');

SELECT * FROM notes;

Tiga insert, tiga transaksi terpisah, tiga kali ke disk untuk fsync perubahannya. Tidak masalah untuk penulisan sesekali, tapi lambat untuk memuat data dalam jumlah besar, dan artinya kamu tidak bisa membatalkan sekelompok pernyataan sebagai satu kesatuan. BEGIN mematikan autocommit sampai COMMIT atau ROLLBACK berikutnya.

ROLLBACK: Anggap Saja Tidak Pernah Terjadi

ROLLBACK membuang semua yang dilakukan sejak BEGIN pasangannya. Database kembali ke keadaan sebelum transaksi.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Baik UPDATE maupun DELETE lenyap: tabelnya terlihat seperti sebelum BEGIN. Inilah jaring pengaman yang membuat kode aplikasi bisa membatalkan dengan bersih saat menemui error di tengah operasi yang terdiri dari banyak pernyataan.

Omong-omong, pelanggaran constraint di dalam transaksi tidak otomatis me-rollback semuanya. Yang dibatalkan hanya pernyataan yang bermasalah, dan transaksinya tetap terbuka, menunggu keputusanmu. Kalau kamu mau semua atau tidak sama sekali, aplikasi harus mengirim ROLLBACK saat melihat error.

Mempercepat Insert dalam Jumlah Besar

Karena setiap pernyataan autocommit melakukan fsync sendiri, membungkus sekumpulan insert dalam satu transaksi sering kali 100x lebih cepat:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Satu kali sinkronisasi disk saat COMMIT, bukan satu kali per baris. Kalau kamu pernah mengimpor ribuan baris dan bertanya-tanya kenapa prosesnya merayap, ini hampir selalu jawabannya.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN menerima mode yang mengatur kapan SQLite mengambil kunci:

• BEGIN DEFERRED (default): tidak ada kunci sama sekali sampai kamu membaca atau menulis. Kunci tulis baru diambil pada pernyataan tulis pertama.
• BEGIN IMMEDIATE: langsung ambil kunci tulis. Koneksi lain masih bisa membaca, tapi tidak ada koneksi lain yang bisa mulai menulis.
• BEGIN EXCLUSIVE: seperti IMMEDIATE, ditambah tidak ada koneksi lain yang bisa membaca. Di mode WAL perilakunya sama dengan IMMEDIATE; perbedaannya hanya berarti di mode rollback journal yang lebih lama.

BEGIN DEFERRED;     -- same as plain BEGIN
BEGIN IMMEDIATE;    -- reserve the write lock now
BEGIN EXCLUSIVE;    -- reserve everything (rollback-journal mode)

Pilihan ini penting untuk konkurensi. Dengan BEGIN biasa, dua koneksi bisa sama-sama memulai transaksi, sama-sama membaca dengan lancar, lalu berebut saat mencoba menulis: koneksi kedua yang meminta kunci tulis mendapat SQLITE_BUSY, dan lebih buruk lagi, ia sudah melakukan beberapa pembacaan yang sekarang harus dibuang.

BEGIN IMMEDIATE mengatasinya: kalau kamu tahu akan menulis, minta kunci tulis lebih dulu. Koneksi kedua langsung tertahan (atau cepat gagal), sebelum mengerjakan apa pun yang nanti harus dibuang.

Patokan praktisnya: jika transaksimu akan menulis, pakai BEGIN IMMEDIATE.

Pembacaan di Dalam Transaksi Melihat Snapshot

Selama transaksi terbuka, pembacaanmu melihat snapshot database yang konsisten, seperti saat transaksi dimulai (di mode WAL) atau saat kamu pertama kali membaca (di mode rollback journal). Perubahan yang di-commit koneksi lain tidak akan tiba-tiba muncul di query-mu.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, your own write is visible
COMMIT;

SELECT n FROM counter;

Kamu melihat penulisanmu sendiri yang belum di-commit; koneksi lain tidak. Begitu kamu COMMIT, nilai barunya terlihat oleh semua orang. Inilah maksud orang saat mengatakan SQLite itu serializable: tidak ada pengaturan READ COMMITTED yang bisa diubah, karena default-nya sudah merupakan level terkuat.

Transaksi di Kode Aplikasi

Dalam program sungguhan, polanya biasanya try/except (atau try/catch) di sekitar isi transaksi, dengan ROLLBACK di jalur error:

-- Pseudocode for any client library
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

Sebagian besar library client (sqlite3 milik Python, better-sqlite3, dan lainnya) sudah membungkus pola ini untukmu dengan blok with atau helper transaction(). Ada baiknya mengecek dokumentasi library-mu, karena default-nya tidak selalu sesuai dugaan. sqlite3 milik Python khususnya dulu punya perilaku autocommit yang aneh; versi-versi terbaru menambahkan parameter autocommit yang layak untuk memperbaikinya.

Hal-Hal yang Sering Menjebak

• DDL di dalam transaksi bisa berjalan. CREATE TABLE, ALTER TABLE, bahkan DROP TABLE bisa di-rollback. SQLite tidak biasa dalam hal ini, karena banyak database melakukan auto-commit untuk DDL.
• VACUUM tidak bisa dijalankan di dalam transaksi. Begitu juga beberapa perintah pemeliharaan lainnya. Jalankan di mode autocommit.
• COMMIT yang gagal tetap kegagalan sungguhan. Jika COMMIT mengembalikan SQLITE_BUSY (jarang, tapi mungkin), transaksinya tidak di-commit. Kodemu harus menanganinya, biasanya dengan mencoba lagi.
• Transaksi yang panjang menghalangi penulis. Transaksi yang terbuka selama beberapa menit akan menghalangi penulis lain selama beberapa menit. Buka selambat mungkin, commit secepat mungkin.

Berikutnya: Savepoint

BEGIN dan COMMIT bersifat semua atau tidak sama sekali. Kadang kamu hanya ingin me-rollback sebagian transaksi, misalnya membatalkan satu langkah yang berisiko tapi mempertahankan sisanya. Itulah gunanya savepoint, dan itu yang dibahas berikutnya.