KAL
Menu

BEGIN, COMMIT, dan ROLLBACK

Disiplin transaksi: batas eksplisit, autocommit, dan pembatalan perubahan.

Daftar isi

Transaksi dibatasi oleh tiga pernyataan pengendali, yaitu BEGIN atau START TRANSACTION untuk memulai, COMMIT untuk menetapkan seluruh perubahan, dan ROLLBACK untuk membatalkannya. Topik ini membahas mode autocommit yang mengubah makna ketiganya, disiplin menetapkan batas transaksi secara eksplisit, serta pernyataan-pernyataan yang diam-diam mengakhiri transaksi tanpa diminta. Setelah membacanya, keadaan data pada setiap titik percobaan dapat diprediksi, dan penyebab transaksi yang berakhir tanpa diminta dapat dikenali dari gejalanya.

Autocommit sebagai mode bawaan#

MySQL secara bawaan menjalankan setiap pernyataan dalam transaksinya sendiri yang langsung di-COMMIT, dan perilaku ini disebut autocommit. Variabel sesi autocommit menandai mode tersebut dengan nilai 1.

bash 
# memeriksa mode autocommit pada sesi latihan
mysql -t -u root perpustakaan -e "SELECT @@autocommit AS autocommit;"
text Keluaran 
+------------+
| autocommit |
+------------+
|          1 |
+------------+

Dalam mode ini, pernyataan tunggal tidak pernah berada setengah jalan, sehingga kegagalan satu pernyataan tidak meninggalkan sisa perubahan. Konsekuensinya muncul ketika sebuah proses memerlukan beberapa pernyataan yang harus berhasil bersama, misalnya menyisipkan baris peminjaman sekaligus mengurangi stok buku. Tanpa batas transaksi eksplisit, kegagalan di antara kedua pernyataan itu meninggalkan data yang setengah jadi, dan itulah alasan pernyataan pengendali transaksi diperlukan. Basis data latihan perpustakaan dibuat ulang oleh skema berikut, sebagaimana pada setiap topik di modul ini.

sql skema-perpustakaan.sql 
-- tidak-dijalankan
DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE buku (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  judul   VARCHAR(120) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  stok    TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE anggota (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE peminjaman (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT UNSIGNED NOT NULL,
  buku_id    INT UNSIGNED NOT NULL,
  jumlah     TINYINT UNSIGNED NOT NULL DEFAULT 1,
  FOREIGN KEY (anggota_id) REFERENCES anggota (id),
  FOREIGN KEY (buku_id)    REFERENCES buku (id)
) ENGINE=InnoDB;

INSERT INTO buku (judul, penulis, stok) VALUES
  ('Dasar Basis Data Relasional', 'Dewi Sartika',  5),
  ('Struktur Data dan Algoritma', 'Ali Rahman',    3),
  ('Jaringan Komputer Dasar',     'Budi Hartono',  4),
  ('Pemrograman SQL Praktis',     'Citra Lestari', 6),
  ('Sistem Operasi Modern',       'Rina Kartika',  2);

INSERT INTO anggota (nama) VALUES ('Ali'), ('Budi'), ('Citra'), ('Dewi');
bash 
# membuat ulang basis data perpustakaan beserta isinya
mysql -u root < skema-perpustakaan.sql

COMMIT dan ROLLBACK eksplisit#

Permasalahan stok pada topik ini mengikuti pola yang sama sepanjang modul, yaitu stok buku berkurang setiap ada peminjaman. Pernyataan START TRANSACTION membuka transaksi, lalu setiap pernyataan berikutnya menjadi bagian dari kesatuan tersebut sampai batasnya ditetapkan. Percobaan pertama mengurangi stok lalu membatalkannya dengan ROLLBACK.

bash 
# mengurangi stok di dalam transaksi, lalu membatalkannya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
UPDATE buku SET stok = stok - 1 WHERE id = 1;
SELECT stok FROM buku WHERE id = 1;
ROLLBACK;
SELECT stok FROM buku WHERE id = 1;
SQL
text Keluaran 
+------+
| stok |
+------+
|    4 |
+------+
+------+
| stok |
+------+
|    5 |
+------+

Pembacaan di dalam transaksi menunjukkan stok lima berkurang satu menjadi empat, sedangkan pembacaan setelah ROLLBACK menunjukkan nilai semula, yaitu lima. Pembacaan di tengah transaksi melihat keadaan yang belum ditetapkan, sehingga nilai empat tersebut belum pernah berlaku bagi sesi lain. Percobaan kedua mengulang perubahan yang sama dengan COMMIT sebagai batasnya.

bash 
# mengurangi stok di dalam transaksi, lalu menetapkannya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
UPDATE buku SET stok = stok - 1 WHERE id = 1;
COMMIT;
SELECT stok FROM buku WHERE id = 1;
SQL
text Keluaran 
+------+
| stok |
+------+
|    4 |
+------+

Setelah COMMIT, perubahan menjadi permanen dan sesi lain yang membaca tabel melihat stok empat. BEGIN dan START TRANSACTION memiliki perilaku yang sama, sehingga keduanya dapat saling menggantikan pada batas transaksi.

Commit implisit#

Sebuah transaksi juga dapat berakhir tanpa COMMIT maupun ROLLBACK yang tertulis. InnoDB mengakhiri transaksi yang sedang berjalan secara implisit ketika pernyataan tertentu dijalankan, dan pernyataan BEGIN merupakan salah satunya. Transaksi baru yang dibuka di tengah transaksi aktif tidak bersarang, melainkan menetapkan seluruh pekerjaan sebelumnya terlebih dahulu.

bash 
# BEGIN di tengah transaksi menetapkan pekerjaan sebelumnya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
INSERT INTO buku (judul, penulis, stok) VALUES ('Uji Begin Bersarang', 'Ali Rahman', 1);
BEGIN;
ROLLBACK;
SELECT COUNT(*) AS jumlah_buku FROM buku;
SQL
text Keluaran 
+-------------+
| jumlah_buku |
+-------------+
|           6 |
+-------------+

Baris sisipan selamat dari ROLLBACK karena BEGIN di tengah transaksi telah menetapkannya terlebih dahulu. ROLLBACK hanya membatalkan transaksi baru yang tidak berisi apa-apa. Kelompok pernyataan DDL seperti CREATE TABLE dan ALTER TABLE berperilaku serupa, yaitu commit implisit dijalankan sebelum dan sesudah pernyataan itu dieksekusi.

bash 
# DDL di tengah transaksi menetapkan pekerjaan sebelumnya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
INSERT INTO buku (judul, penulis, stok) VALUES ('Kriptografi Terapan', 'Dewi Sartika', 7);
CREATE TABLE catatan_audit (id INT PRIMARY KEY, keterangan VARCHAR(100));
ROLLBACK;
SELECT id, judul FROM buku ORDER BY id;
SQL
text Keluaran 
+----+-----------------------------+
| id | judul                       |
+----+-----------------------------+
|  1 | Dasar Basis Data Relasional |
|  2 | Struktur Data dan Algoritma |
|  3 | Jaringan Komputer Dasar     |
|  4 | Pemrograman SQL Praktis     |
|  5 | Sistem Operasi Modern       |
|  6 | Uji Begin Bersarang         |
|  7 | Kriptografi Terapan         |
+----+-----------------------------+

Pernyataan CREATE TABLE menetapkan sisipan yang sedang berjalan, sehingga ROLLBACK tidak menyisakan apa pun untuk dibatalkan dan baris buku ketujuh tetap ada. Kedua contoh tersebut membuktikan bahwa commit implisit tidak mencetak peringatan apa pun pada klien, sehingga satu-satunya pertahanan adalah kebiasaan menjaga transaksi tetap pendek dan menutupnya sebelum menyentuh pernyataan DDL.

Transaksi baca saja#

Kata kunci tambahan pada START TRANSACTION mempersempit maksud transaksi sejak awal. Bentuk START TRANSACTION READ ONLY menyatakan bahwa transaksi hanya akan membaca, dan server menegakkan janji tersebut dengan menolak setiap pernyataan penulisan di dalamnya. Batasan semacam ini berguna untuk pekerjaan pelaporan, karena transaksi yang dinyatakan baca saja memberi server kebebasan mengatur potret keadaannya tanpa menyiapkan kemampuan pembatalan penulisan.

bash 
# transaksi baca saja menolak pernyataan penulisan
mysql -u root perpustakaan <<'SQL' 2>&1
START TRANSACTION READ ONLY;
INSERT INTO anggota (nama) VALUES ('Zaki');
SQL
text Keluaran 
ERROR 1792 (25006) at line 2: Cannot execute statement in a READ ONLY transaction.

Server menghentikan penyisipan dengan error 1792 sebelum pernyataan itu menyentuh data, sedangkan transaksi itu sendiri tetap terbuka untuk pembacaan sampai COMMIT atau ROLLBACK diberikan. Bentuk pasangannya, START TRANSACTION READ WRITE, hanya menuliskan maksud yang sudah menjadi perilaku bawaan, sedangkan opsi WITH CONSISTENT SNAPSHOT membuka transaksi dan membentuk potret keadaannya dalam satu langkah tanpa menunggu pembacaan pertama.

Kesalahan umum#

Kesalahan pertama adalah memperlakukan START TRANSACTION sebagai pengantar yang boleh dihilangkan. Beberapa pernyataan penulisan yang dijalankan berturut-turut tanpa batas transaksi tetap ditetapkan satu per satu, sehingga kegagalan di tengah rangkaian meninggalkan data setengah jadi. Tutup setiap rangkaian dengan START TRANSACTION di awal dan COMMIT di akhir agar kesatuannya dijamin.

Kesalahan kedua adalah membuka BEGIN di dalam transaksi yang belum selesai dengan maksud menyarakannya. Kedua transaksi tidak bertumpuk, karena BEGIN menetapkan pekerjaan sebelumnya dan memulai transaksi kosong. Struktur bertingkat yang benar untuk pembatalan parsial adalah SAVEPOINT.

Kesalahan ketiga adalah menaruh pernyataan DDL di tengah transaksi yang panjang, misalnya membuat tabel bantu ketika data belum ditetapkan. Commit implisit menetapkan seluruh perubahan sebelumnya, sehingga ROLLBACK yang diandalkan tidak berdaya. Pisahkan pekerjaan DDL dan pekerjaan data ke dalam transaksi yang berbeda.

Ringkasan#

  • Autocommit bernilai 1 secara bawaan, sehingga setiap pernyataan tunggal menjadi transaksi tersendiri yang langsung ditetapkan.
  • START TRANSACTION membuka batas eksplisit, dan seluruh pernyataan sesudahnya berada di bawah kendali COMMIT atau ROLLBACK.
  • COMMIT menetapkan perubahan secara permanen, sedangkan ROLLBACK memulihkan keadaan awal melalui undo log.
  • BEGIN di tengah transaksi dan pernyataan DDL sama-sama menyebabkan commit implisit, sehingga transaksi aktif berakhir tanpa diminta.

Referensi#