ACID dan InnoDB

Empat jaminan ACID menjadikan sekelompok pernyataan SQL berperilaku sebagai satu kesatuan kerja yang andal, bukan kumpulan perubahan yang berdiri sendiri-sendiri. Topik ini membahas arti setiap jaminan beserta wujud konkretnya pada arsitektur InnoDB, mulai dari undo log untuk pembatalan, redo log untuk ketahanan data, sampai mekanisme multiversi dan lock untuk isolasi. Setelah membacanya, pembatalan maupun penyimpanan sebuah transaksi dapat dijelaskan pada komponen InnoDB yang mengerjakannya.

Empat jaminan ACID#

ACID adalah singkatan dari atomicity (atomisitas), consistency (konsistensi), isolation (isolasi), dan durability (durabilitas). Empat jaminan tersebut merupakan kontrak yang diberikan basis data transaksional kepada aplikasinya. Atomisitas menjamin bahwa seluruh pernyataan dalam satu transaksi diperlakukan sebagai satu kesatuan, yaitu seluruhnya berhasil tersimpan atau seluruhnya dibatalkan. Konsistensi menjamin bahwa transaksi membawa basis data dari satu keadaan yang sah menuju keadaan sah berikutnya, dengan seluruh constraint tetap terpenuhi.

Dua jaminan sisanya berkaitan dengan cara transaksi berdampingan. Isolasi menjamin bahwa transaksi yang berjalan bersamaan tidak melihat separuh pekerjaan transaksi lain yang belum selesai, sehingga keadaan yang terbaca selalu masuk akal. Durabilitas menjamin bahwa perubahan yang sudah dinyatakan selesai dengan COMMIT tetap bertahan meskipun server mati seketika setelahnya. Tabel berikut merangkum keempatnya beserta komponen InnoDB yang mewujudkan jaminan tersebut.

Diagram berikut memetakan keempat jaminan tersebut ke mekanisme InnoDB yang mengerjakannya.

Atomisitas pada undo log#

Undo log merekam keadaan lama dari setiap baris yang diubah sebelum perubahan ditulis, sehingga pembatalan cukup dilakukan dengan menerapkan kembali keadaan lama tersebut. InnoDB memakai log ini setiap kali pernyataan ROLLBACK dijalankan, dan log yang sama dipakai pembacaan multiversi untuk menyusun keadaan lama bagi transaksi lain. Pembatalan berjalan cepat karena server tidak perlu menebak keadaan awal, melainkan tinggal membaca catatannya sendiri.

Basis data latihan pada modul ini adalah perpustakaan berisi tabel buku, anggota, dan peminjaman. Jalankan berkas skema berikut untuk membuat ulang basis data tersebut dari nol.

-- 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');

# membuat ulang basis data perpustakaan beserta isinya
mysql -u root < skema-perpustakaan.sql

Percobaan berikut menyisipkan satu baris buku baru dan menyetel stok salah satu buku menjadi nol di dalam satu transaksi, lalu membatalkan seluruh perubahan dengan ROLLBACK.

# transaksi dua perubahan yang dibatalkan seluruhnya
mysql -t -u root perpustakaan <<'SQL'
START TRANSACTION;
INSERT INTO buku (judul, penulis, stok) VALUES ('Kriptografi Terapan', 'Dewi Sartika', 7);
UPDATE buku SET stok = 0 WHERE id = 2;
ROLLBACK;
SELECT COUNT(*) AS jumlah_buku, SUM(stok) AS total_stok FROM buku;
SQL

+-------------+------------+
| jumlah_buku | total_stok |
+-------------+------------+
|           5 |         20 |
+-------------+------------+

Keluarannya menunjukkan jumlah buku kembali lima dan total stok kembali dua puluh, persis seperti sebelum transaksi dimulai. Baris sisipan yang belum di-COMMIT tidak pernah menjadi bagian dari keadaan basis data, sedangkan pembaruan stok dipulihkan dari nilai lamanya yang tersimpan di undo log. Inilah wujud atomisitas yang dapat diamati langsung tanpa alat tambahan.

Durabilitas pada redo log#

Redo log adalah catatan fisik yang menuliskan setiap perubahan halaman data sebelum halaman itu benar-benar ditulis ke berkas tabel. Pola ini disebut write-ahead logging, yakni pencatatan lebih dahulu sebelum penulisan sebenarnya. Ketika COMMIT dijalankan, InnoDB tidak menunggu seluruh halaman data tersimpan di disk, melainkan cukup memastikan catatan redo-nya sudah ditulis, karena catatan tersebut mampu membangun ulang halaman yang rusak atau hilang setelah server mati mendadak.

Dua variabel berikut menentukan seberapa keras jaminan durabilitas ditegakkan. Periksa nilainya pada server latihan.

# membaca pengaturan durabilitas redo log dan doublewrite
mysql -t -u root perpustakaan -e "SELECT @@innodb_flush_log_at_trx_commit AS flush_log_at_trx_commit, @@innodb_doublewrite AS doublewrite;"

+-------------------------+-------------+
| flush_log_at_trx_commit | doublewrite |
+-------------------------+-------------+
|                       1 | ON          |
+-------------------------+-------------+

Nilai 1 pada innodb_flush_log_at_trx_commit berarti redo log ditulis dan di-flush ke disk pada setiap COMMIT, sehingga transaksi yang telah di-COMMIT tidak mungkin hilang karena kerusakan server. Nilai ON pada innodb_doublewrite berarti setiap halaman ditulis dua kali, yaitu disalin lebih dahulu ke area khusus sebelum ditempatkan pada posisi akhirnya, sehingga halaman yang setengah jalan tertulis akibat mati listrik dapat dipulihkan. Kedua pengaturan bawaan ini lazim dipakai pada basis data transaksional, dan pengubahannya selalu menimbulkan pertimbangan antara keandalan dan kecepatan.

Isolasi dan konsistensi#

Isolasi pada InnoDB berdiri di atas dua mekanisme yang saling melengkapi. Pembacaan biasa memakai MVCC (multiversion concurrency control), yakni teknik menyajikan potret keadaan data pada satu titik waktu dengan bantuan undo log, sehingga pembaca tidak terganggu oleh penulis yang sedang bekerja. Perubahan yang saling bertabrakan disalurkan melalui lock, yaitu izin baca atau tulis yang wajib dipegang sebuah transaksi pada baris yang tersenggolnya.

Konsistensi berbeda dari tiga jaminan lainnya karena tidak diwujudkan oleh satu komponen khusus. Sifat ini merupakan hasil akhir dari atomisitas, isolasi, serta penerapan constraint dan foreign key yang tidak boleh dilanggar oleh transaksi mana pun. Perilaku antarsesi pada setiap level isolasi dan jenis-jenis lock memerlukan pembahasan yang rinci, sehingga keduanya diperdalam pada topik tersendiri di modul ini.

Ringkasan#

• ACID terdiri atas atomisitas, konsistensi, isolasi, dan durabilitas, dan InnoDB mewujudkannya lewat komponen yang berbeda untuk setiap jaminan.
• Undo log merekam keadaan lama setiap perubahan, sehingga ROLLBACK mengembalikan transaksi tanpa meninggalkan jejak pada data.
• Redo log dengan innodb_flush_log_at_trx_commit = 1 serta doublewrite buffer aktif menjamin perubahan yang telah di-COMMIT bertahan terhadap kerusakan server.
• Isolasi berdiri di atas MVCC untuk pembacaan dan lock untuk perubahan, sedangkan konsistensi merupakan hasil akhir dari ketiga jaminan lainnya ditambah constraint.

Referensi#

• Glossary — Oracle — Definisi resmi istilah ACID, undo log, redo log, dan doublewrite buffer pada manual MySQL 8.0.
• InnoDB Undo Logs — Oracle — Peran undo log dalam pembatalan transaksi dan pembacaan multiversi.
• InnoDB Redo Log — Oracle — Cara redo log dan pengaturan flush menjaga durabilitas perubahan yang telah di-COMMIT.