KAL
Menu

Transaksi

BEGIN, COMMIT, dan ROLLBACK serta jaminan ACID pada SQLite.

Daftar isi

Topik ini membahas cara SQLite mengelompokkan beberapa pernyataan menjadi satu unit kerja yang tidak terbagi. Pembahasan dimulai dari perilaku bawaan yang disebut autocommit, dilanjutkan dengan pernyataan BEGIN, COMMIT, dan ROLLBACK, lalu ditutup dengan pemetaan empat jaminan ACID ke mekanisme internal SQLite. Seluruh demonstrasi menggunakan satu basis data perpustakaan yang sama, dengan pemindahan saldo denda antar anggota sebagai contoh operasi yang harus terjadi seluruhnya atau tidak sama sekali.

Mode autocommit bawaan#

Apabila tidak ada transaksi eksplisit yang sedang terbuka, SQLite menjalankan setiap pernyataan SQL sebagai satu transaksi tersendiri. Mode ini disebut autocommit, yaitu keadaan ketika perubahan langsung menjadi permanen pada akhir setiap pernyataan tanpa perintah pemicu tambahan. Mode autocommit nyaman untuk operasi satu langkah, tetapi tidak memadai untuk tugas yang terdiri atas beberapa pernyataan. Kegagalan pada pernyataan kedua tidak menarik kembali pernyataan pertama yang sudah menjadi permanen.

Basis data perpustakaan berikut dipakai sepanjang topik ini. Kolom saldo_denda menyimpan denda yang masih harus ditanggung setiap anggota, sedangkan constraint CHECK menolak nilai negatif dan constraint UNIQUE menolak nama ganda. Seluruh pernyataan dikirim ke sqlite3 melalui heredoc, yaitu cara shell menyalurkan sebuah blok teks ke masukan sebuah perintah.

bash 
rm -f perpustakaan.db
sqlite3 perpustakaan.db <<'SQL'
CREATE TABLE anggota (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL UNIQUE,
  saldo_denda INTEGER NOT NULL DEFAULT 0 CHECK (saldo_denda >= 0)
);
INSERT INTO anggota (nama, saldo_denda) VALUES
  ('Budi Santoso', 3000),
  ('Citra Kirana', 1500),
  ('Dewi Lestari', 0);
SQL

Blok berikut menyimulasikan pendaftaran dua anggota baru dalam mode autocommit. Pendaftaran pertama sah dan langsung tersimpan, sedangkan pendaftaran kedua ditolak karena namanya sudah terdaftar. Keadaan tabel pada akhir blok menunjukkan sisa kejadian itu.

bash 
sqlite3 perpustakaan.db <<'SQL'
INSERT INTO anggota (nama, saldo_denda) VALUES ('Eka Pratama', 0);
INSERT INTO anggota (nama, saldo_denda) VALUES ('Budi Santoso', 500);
SELECT id, nama, saldo_denda FROM anggota;
SQL
text Keluaran 
Error near line 2: UNIQUE constraint failed: anggota.nama
1|Budi Santoso|3000
2|Citra Kirana|1500
3|Dewi Lestari|0
4|Eka Pratama|0

Anggota Eka Pratama tetap tercatat meskipun pernyataan kedua gagal, karena kedua pernyataan tadi berjalan sebagai dua transaksi yang masing-masing selesai sendiri. Mode autocommit tidak menyediakan mekanisme untuk menarik kembali pernyataan yang sudah selesai. Keadaan tabel yang setengah berubah itulah yang menjadi alasan utama keberadaan transaksi eksplisit.

BEGIN, COMMIT, dan ROLLBACK#

Pernyataan BEGIN membuka transaksi eksplisit, COMMIT menetapkan seluruh perubahannya menjadi permanen, dan ROLLBACK membatalkannya sampai tidak ada satu pun perubahan yang tersisa. Bentuk BEGIN tanpa kata tambahan merupakan singkatan dari BEGIN DEFERRED, yaitu transaksi yang baru memperoleh lock ketika pernyataan pertamanya benar-benar membaca atau menulis. Bentuk lain, yaitu BEGIN IMMEDIATE, langsung mengamankan hak tulis pada saat transaksi dibuka dan dibahas lebih lanjut pada topik lock dan error busy.

Contoh kasusnya adalah pemindahan sebagian denda dari Budi Santoso ke tanggungan Dewi Lestari, misalnya karena satu buku yang hilang ternyata harus ditanggung bersama. Pemindahan ini terdiri atas dua UPDATE yang hanya masuk akal apabila keduanya terjadi bersamaan. Keduanya kini dibungkus satu transaksi yang ditutup COMMIT.

bash 
sqlite3 perpustakaan.db <<'SQL'
BEGIN;
UPDATE anggota SET saldo_denda = saldo_denda + 2000 WHERE nama = 'Dewi Lestari';
UPDATE anggota SET saldo_denda = saldo_denda - 2000 WHERE nama = 'Budi Santoso';
COMMIT;
SELECT nama, saldo_denda FROM anggota ORDER BY id;
SQL
text Keluaran 
Budi Santoso|1000
Citra Kirana|1500
Dewi Lestari|2000
Eka Pratama|0

Keluarannya menunjukkan saldo Budi Santoso turun menjadi 1000 dan saldo Dewi Lestari naik menjadi 2000 dalam satu langkah COMMIT. Apabila koneksi ditutup tanpa COMMIT sempat dijalankan, seluruh transaksi yang masih terbuka dibatalkan secara otomatis, sehingga kedua perubahan itu hilang bersamaan. Sifat serentaknya diuji lebih jauh oleh transaksi yang berisi satu pernyataan sah dan satu pernyataan yang melanggar constraint. Blok berikut menambahkan denda baru pada Citra Kirana, lalu mencoba mengurangi saldo Budi Santoso melebihi nol, dan diakhiri ROLLBACK.

bash 
sqlite3 perpustakaan.db <<'SQL'
BEGIN;
UPDATE anggota SET saldo_denda = saldo_denda + 1000 WHERE nama = 'Citra Kirana';
UPDATE anggota SET saldo_denda = saldo_denda - 9000 WHERE nama = 'Budi Santoso';
ROLLBACK;
SELECT nama, saldo_denda FROM anggota ORDER BY id;
SQL
text Keluaran 
Error near line 3: CHECK constraint failed: saldo_denda >= 0
Budi Santoso|1000
Citra Kirana|1500
Dewi Lestari|2000
Eka Pratama|0

Pesan error CHECK constraint muncul pada pernyataan kedua, tetapi perubahan pertama pada Citra Kirana juga tidak tersisa karena ROLLBACK menarik seluruh transaksi sekaligus. Saldo setiap anggota kembali persis seperti sebelum BEGIN. Perbedaannya dengan mode autocommit terlihat jelas, yaitu pernyataan pertama tetap tersimpan ketika tidak ada transaksi yang membungkusnya.

Perilaku lanjut-setelah-error tersebut membuat keluaran contoh terakhir tetap memuat hasil SELECT di bawah pesan errornya. Program aplikasi sebaiknya memeriksa kode hasil setiap pernyataan dan memanggil ROLLBACK secara eksplisit pada jalur gagal, sehingga keadaan basis data tidak bergantung pada cara sesi ditutup. Pada modul sqlite3 bawaan Python, penanganan semacam ini ditulis sebagai blok try dan except yang cabang gagalnya diakhiri pemanggilan rollback.

Jaminan ACID pada SQLite#

Empat jaminan ACID, yaitu atomicity, consistency, isolation, dan durability, seluruhnya berlaku pada SQLite dan masing-masing ditopang mekanisme yang berbeda. Dokumentasi SQLite menyatakan transaksinya bersifat serializable dan tetap memenuhi keempat sifat itu meskipun program berhenti mendadak, sistem operasi mati, atau daya listrik padam. Pemetaan berikut menghubungkan setiap jaminan dengan mekanismenya.

  • Atomicity menuntut transaksi berlaku seluruhnya atau tidak sama sekali. SQLite menopangnya dengan journal, yakni berkas samping yang menyimpan salinan keadaan lama. COMMIT menerapkan seluruh perubahan sekaligus, sedangkan gangguan di tengah transaksi dipulihkan dengan menarik kembali salinan lama itu.
  • Consistency menuntut keadaan basis data yang selalu memenuhi seluruh constraint. SQLite menjaganya karena setiap constraint dievaluasi pada pernyataan yang melanggarnya, dan transaksi yang tidak mungkin dilanjutkan dibatalkan seluruhnya.
  • Isolation mencegah transaksi saling mengintip perubahan setengah jadi. Mode journal bawaan menopangnya dengan lock, sedangkan mode WAL menopangnya dengan snapshot.
  • Durability menjamin perubahan yang sudah COMMIT tidak hilang. SQLite menopangnya dengan menulis ke berkas basis data lalu memanggil fsync, yakni permintaan kepada sistem operasi agar data benar-benar sampai ke perangkat penyimpanan.

Kombinasi journal dan fsync menjelaskan mengapa COMMIT pada SQLite memiliki biaya nyata meskipun datanya kecil. Satu COMMIT memicu beberapa operasi tulis dan sinkronisasi perangkat, sehingga mengelompokkan seribu pernyataan kecil ke dalam satu transaksi jauh lebih cepat daripada mengeksekusinya satu per satu. Kebiasaan itu juga mengurangi jumlah pembaruan permanen yang bergantian terjadi pada berkas basis data.

ROLLBACK membatalkan perubahan dalam jumlah berapa pun sekaligus, sehingga operasi impor massal dapat diuji tanpa risiko. Blok berikut membuka transaksi, menyisipkan dua baris peminjaman, lalu membatalkannya pada basis data dalam memori.

sql 
CREATE TABLE peminjaman (
  id INTEGER PRIMARY KEY,
  buku_id INTEGER NOT NULL,
  anggota_id INTEGER NOT NULL
);
BEGIN;
INSERT INTO peminjaman (buku_id, anggota_id) VALUES (3, 7);
INSERT INTO peminjaman (buku_id, anggota_id) VALUES (5, 7);
ROLLBACK;
SELECT COUNT(*) FROM peminjaman;
text Keluaran 
0

Hasilnya nol baris karena kedua INSERT tadi berada di dalam transaksi yang dibatalkan ROLLBACK. Tanpa BEGIN, kedua baris itu akan tersimpan permanen. Blok ini sekaligus menunjukkan bahwa transaksi bekerja pada basis data dalam memori dengan semantik yang sama seperti basis data berkas.

Ringkasan#

  • SQLite berjalan dalam mode autocommit bawaan, yaitu setiap pernyataan menjadi satu transaksi tersendiri yang langsung permanen.
  • BEGIN membuka transaksi, COMMIT menetapkannya, dan ROLLBACK menarik seluruh perubahannya, sehingga tugas multi-pernyataan terjadi seluruhnya atau tidak sama sekali.
  • Atomicity ditopang journal, durability oleh penulisan dan fsync, isolation oleh lock dan snapshot, sedangkan consistency dijaga constraint.
  • Koneksi yang ditutup dengan transaksi masih terbuka memicu rollback otomatis atas transaksi tersebut.

Referensi#