Transaksi di SQLite

• Dalam beberapa tahun terakhir, SQLite semakin mendapat sorotan sebagai engine database SQL in-process yang andal untuk backend proses server
• Popularitasnya melonjak meskipun para pengembang SQLite nyaris secara aktif menyarankan agar SQLite tidak digunakan untuk tujuan seperti ini, alih-alih peran tradisionalnya sebagai aplikasi klien atau edge

Alasan utama saya mulai tertarik pada SQLite:

• Sederhana secara konseptual: bayangkan B-tree dari baris/tuple yang dipartisi berdasarkan primary key. Ini telah diuji secara ekstensif agar tetap tersimpan dengan aman di disk, lalu ditambahkan lapisan interaksi SQL
• Strategi backup yang praktis bisa disusun melalui Litestream. WAL dibackup ke lokasi jarak jauh dan direplikasi secara berkelanjutan. Backup bisa dipulihkan otomatis saat startup dengan perintah sederhana.
• Saya masih menyukai lingkungan pengembangan yang sepenuhnya lengkap dan bisa dijalankan secara offline
• Dengan file::memory: pekerjaan in-memory dimungkinkan, sehingga kode pengujian dapat dengan mudah memulai dan menghentikan instance sesuai kebutuhan

Batas single-writer

• "Keterbatasan SQLite di server" telah didokumentasikan dengan baik oleh para pengembang SQLite, dan konfigurasi sisi server terbaiknya telah dianalisis. Tetapi batasan yang paling menonjol adalah situs web dengan trafik tinggi, yaitu situs yang banyak melakukan write
• Dalam mode WAL, SQLite secara desain menggunakan satu Writer. Ini memungkinkan paling banyak 1 transaksi write dan banyak transaksi read-only secara bersamaan
• Desain ini menempatkan bottleneck situs web dengan trafik tinggi yang berfokus pada write pada pengelolaan throughput Writer tunggal tersebut. Ini membawa kita kembali pada salah satu komponen inti teknologi modern

SQLite

• SQLite pada dasarnya menyediakan transaksi dengan isolasi SERIALIZABLE yang ketat. Ini adalah tingkat jaminan Isolation yang paling kuat
• Dengan menggunakan satu Writer, SQLite memakai bentuk pessimistic concurrency control yang dapat dengan mudah menjamin bahwa data dasar tidak berubah selama transaksi write berlangsung

Postgres

• Postgres pada praktiknya berbeda dari default SERIALIZABLE yang didefinisikan oleh standar SQL, dan memilih READ COMMITTED yang lebih longgar (meskipun memakai multiversion concurrency control yang jauh lebih kompleks)
  • Pelonggaran ini membawa risiko non-repeatable reads. Artinya, bahkan dalam transaksi yang sama, jika nilai diubah di latar belakang oleh transaksi lain yang sudah COMMITTED, menjalankan query baca yang sama beberapa kali bisa menghasilkan hasil yang berbeda
  • Dengan memilih tingkat Isolation ini, Postgres membuka risiko bahwa transaksi bekerja di atas data usang. Pengembang perlu mengingat hal ini
• Jika diatur ke SERIALIZABLE, Postgres menggunakan skema optimistic-concurrency control untuk melacak data yang diakses selama transaction dan memeriksa apakah data tersebut belum berubah sebelum commit
  • Postgres melakukan ini berdasarkan lock pada level row atau page tergantung transaction untuk mengelola penggunaan memori
  • Pola ini disebut optimistic karena diasumsikan data dasar tidak akan berubah, sebab semakin terperinci data yang dipantau oleh transaction, semakin kecil kemungkinan data itu berubah saat transaction di-commit

FoundationDB

• Transaksi tidak terbatas pada database relasional. Untuk mencapai jaminan SERIALIZABLE di penyimpanan key-value terdistribusi, digunakan optimistic concurrency control
• Saat NoSQL pertama kali muncul, penyimpanan NoSQL terdistribusi dengan jaminan ACID belum umum. FoundationDB menulis manifesto transaksi untuk menekankan bahwa pengembang bisa sangat diuntungkan dari jaminan ACID
• FoundationDB memberikan saran tentang kapan dan bagaimana menulis kode untuk optimistic concurrency control, serta fakta bahwa kadang data berubah karena konflik transaksi serentak dan transaksi akan otomatis di-retry

Idempotence

• Transaksi idempoten adalah transaksi yang memberikan efek yang sama baik di-commit satu kali maupun dua kali
• FoundationDB menyediakan pola untuk membuat transaksi menjadi idempoten agar masalah bisa dihindari bila transaksi perlu di-retry beberapa kali akibat konflik

Kalau begitu, dengan semua hal ini dalam pikiran, opsi apa yang disediakan SQLite?

BEGIN …

SQLite menyediakan beberapa cara bagi pengembang untuk memberi tahu engine bagaimana transaksi harus berjalan, dalam bentuk keyword IMMEDIATE, EXCLUSIVE, DEFERRED, yang dalam mode WAL pada akhirnya dapat disederhanakan menjadi DEFERRED vs IMMEDIATE

DEFERRED

• Transaksi dimulai dalam mode READ sehingga bisa berjalan bersamaan dengan transaksi baca atau tulis lain
• Hanya saat query yang memodifikasi status DB (INSERT, UPDATE, DELETE) dijalankan, transaksi akan di-upgrade menjadi transaksi READ-WRITE yang memblokir
• Jika saat upgrade DB sedang dikunci oleh transaksi lain, akan mengembalikan error SQLITE_BUSY. Klien harus menanganinya

IMMEDIATE

• Transaksi langsung dimulai dalam mode READ-WRITE
• Jika transaksi write lain sudah berjalan, segera mengembalikan SQLITE_BUSY
• Klien harus memutuskan cara menanganinya

CONCURRENT

• SQLite memiliki cabang eksperimental yang memindahkan transaksi dari Pessimistic menjadi bentuk Optimistic yang terbatas
• Disebut terbatas karena optimistic locking bekerja pada level page DB (default 4096 bytes), bukan level row/tuple
• Dalam mode CONCURRENT, SQLite dapat mengaktifkan beberapa transaksi write secara bersamaan, tetapi sebelum commit akan memeriksa apakah page yang diakses selama transaksi belum berubah sejak transaksi dimulai
• Jika tidak ada konflik, perubahan di-commit secara berurutan dan jaminan SERIALIZABLE yang ketat tercapai. Jika terjadi konflik, akan mengembalikan SQLITE_BUSY

HC-Tree

• Cabang eksperimental lain SQLite adalah [HC-Tree], pekerjaan yang masih berlangsung dan bertujuan menyediakan optimistic locking pada level row/tuple. Salah satu hasil menariknya adalah tersedianya kumpulan benchmark yang sangat baik yang menunjukkan keunggulan performa desain tersebut dibanding cabang BEGIN CONCURRENT

Bagaimana jika pendekatan benchmarking mereka diambil lalu dijalankan pada opsi standar?

Benchmarking

nUpdate=1, nScan=0

• Transaksi write-only ini dengan jelas menunjukkan keuntungan IMMEDIATE vs DEFERRED. Locking terjadi segera dan transaksi tidak terkena biaya upgrade
• CONCURRENT menunjukkan peningkatan throughput saat jumlah thread bertambah dan konflik meningkat

nUpdate=10, nScan=0

• Sesuai dugaan, batching write sangat membantu jumlah baris yang di-update pada 16 thread. CONCURRENT meningkat dari ~12k/sec menjadi ~19k/sec
• IMMEDIATE vs DEFERRED menjadi kurang penting. Ini karena biaya update itu sendiri menjadi lebih penting daripada biaya upgrade transaksi

nUpdate=1, nScan=10

• Transaksi ini seharusnya memperlihatkan kelemahan locking CONCURRENT level page karena random read
• Ini langsung menunjukkan mengapa menggunakan IMMEDIATE untuk transaksi yang akan melakukan update lebih penting daripada biaya upgrade DEFERRED
• Untuk CONCURRENT, hasil ini sangat solid karena konflik dasar ternyata tidak meningkat banyak

nUpdate=0, nScan=10

• Transaksi batch read-only ini menunjukkan dampak pessimistic concurrency control
• Ini menunjukkan mengapa IMMEDIATE tidak boleh dijadikan default untuk semua transaksi
• CONCURRENT vs IMMEDIATE menunjukkan bahwa ada sedikit kerugian saat menggunakan mode CONCURRENT. "Performa sedikit lebih buruk di semua kasus"
  • Namun, CONCURRENT akan menjadi opsi default yang baik