Apa itu transaksi database?

• Transaksi adalah struktur untuk menjalankan beberapa operasi di database sebagai satu unit atomik, mencakup baca, tulis, pembaruan, dan penghapusan
• MySQL dan Postgres mengontrol transaksi dengan begin; dan commit;, lalu membatalkan perubahan dengan rollback; saat terjadi kegagalan atau error
• Kedua database sama-sama menjamin consistent read, tetapi Postgres menggunakan MVCC (Multi-Version Concurrency Control), sedangkan MySQL menggunakan undo log
• Isolation level mengontrol interferensi data antartransaksi, dan dibagi menjadi empat tingkat dari Serializable hingga Read Uncommitted
• Postgres dan MySQL menangani konflik penulisan bersamaan dengan cara berbeda; Postgres memakai validasi optimistis, sementara MySQL menggunakan row-level locking

---

Konsep dasar transaksi

• Transaksi adalah struktur yang mengelompokkan beberapa operasi SQL di database menjadi satu unit eksekusi atomik
  • Dimulai dengan begin; dan diakhiri dengan commit;, dengan beberapa kueri dapat dijalankan di antaranya
  • Pada saat commit;, semua perubahan diterapkan sekaligus
• Saat terjadi gangguan tak terduga (mati listrik, error disk, dan sebagainya) atau pembatalan yang disengaja, perubahan dapat dikembalikan dengan rollback;
  • Postgres mendukung pemulihan dengan WAL (Write-Ahead Log)
• Data yang diubah selama transaksi akan terisolasi dan tidak terlihat dari sesi lain
  • Saat rollback;, semua perubahan dibatalkan sehingga database kembali ke keadaan semula

Consistent read

• Transaksi harus mempertahankan tampilan data yang konsisten yang tidak terpengaruh oleh perubahan eksternal selama eksekusi
• MySQL dan Postgres mendukung hal ini pada mode REPEATABLE READ ke atas, tetapi implementasinya berbeda
  • Postgres: mengelola versi setiap baris dengan MVCC (Multi-Version Concurrency Control)
  • MySQL: merekonstruksi versi lama dengan undo log

Penyimpanan multi-versi baris di Postgres

• Setiap kali baris diperbarui, versi baru dibuat; versi lama mencatat ID transaksi di xmax, dan versi baru di xmin
• Sebelum transaksi di-commit, sesi lain tidak dapat melihat perubahan tersebut
• Setelah commit, versi baru diterapkan ke seluruh database
• Saat rollback;, perubahan dibuang dan data asli tetap dipertahankan
• Versi baris lama dibersihkan dengan perintah VACUUM FULL untuk merebut kembali ruang penyimpanan

Undo log di MySQL

• MySQL menimpa baris secara langsung, tetapi mencatat nilai sebelumnya di undo log sehingga dapat dipulihkan bila diperlukan
• Setiap baris memiliki metadata xid (ID transaksi modifikasi terbaru) dan ptr (pointer undo log)
• Saat beberapa transaksi berjalan bersamaan, setiap transaksi secara selektif mengambil versi yang dibutuhkan melalui undo log
• Beberapa catatan undo log dapat ada untuk baris yang sama, dan versi yang sesuai dipilih berdasarkan ID transaksi

Isolation level

• Ini adalah pengaturan untuk mengontrol interferensi data antartransaksi, dengan tingkat pelonggaran berurutan Serializable → Repeatable Read → Read Committed → Read Uncommitted
• Serializable: semua transaksi berjalan seolah-olah dieksekusi secara berurutan
• Repeatable Read: hasil tetap sama saat kueri yang sama dijalankan ulang, tetapi phantom read masih mungkin terjadi
• Read Committed: dapat membaca perubahan dari transaksi lain yang sudah di-commit
• Read Uncommitted: mengizinkan dirty read, tingkat perlindungan paling rendah tetapi performanya tinggi

Penulisan bersamaan

• Cara menangani dua transaksi yang memodifikasi baris yang sama secara bersamaan berbeda-beda tergantung database

MySQL: Row-level locking

• Shared lock (S lock) memungkinkan beberapa transaksi membaca secara bersamaan
• Exclusive lock (X lock) hanya memungkinkan satu transaksi memodifikasi baris
• Dalam mode SERIALIZABLE, setiap pembaruan harus memperoleh X lock, dan deadlock dapat terjadi saat ada konflik
• MySQL mendeteksi deadlock dan menghentikan salah satu transaksi

Postgres: Serializable Snapshot Isolation

• Postgres menggunakan predicate lock untuk melacak akses pada tingkat himpunan baris
  • Contoh: lock untuk kondisi WHERE id BETWEEN 10 AND 20
• Akses nyata tidak langsung diblokir; sistem mendeteksi konflik dan menghentikan transaksi bila terjadi pelanggaran
• Dengan optimistic conflict resolution, deadlock dapat dihindari
• Sama seperti MySQL, saat konflik terjadi satu transaksi akan dihentikan dan aplikasi perlu menerapkan logika retry

Kesimpulan

• Transaksi adalah komponen inti database yang menjamin ACID (Atomicity, Consistency, Isolation, Durability)
• Postgres dan MySQL mencapai tujuan yang sama dengan struktur internal yang berbeda
• Memahami empat isolation level dan cara kerja transaksi membantu pengoperasian database yang lebih stabil