Konkurensi SQLite dan alasan mengapa Anda perlu mempertimbangkannya

• Struktur berbasis file SQLite memang sederhana, tetapi saat beberapa operasi tulis berjalan bersamaan, konflik penguncian (locking) dapat terjadi
• Jellyfin telah lama menggunakan SQLite, namun pada beberapa sistem muncul masalah aplikasi berhenti karena kesalahan database terkunci selama transaksi
• Dengan memanfaatkan fitur interceptor di EF Core, mereka menerapkan tiga strategi penguncian (No-Lock, Optimistic, Pessimistic) untuk meredakan masalah
• Pendekatan Optimistic meminimalkan penurunan performa lewat mekanisme retry, sementara pendekatan Pessimistic meningkatkan stabilitas dengan konsekuensi penurunan kecepatan
• Pendekatan ini memiliki struktur yang mudah diterapkan juga pada aplikasi EF Core lainnya, sehingga menawarkan alternatif praktis untuk mengatasi masalah konkurensi SQLite

---

Struktur dasar dan batasan SQLite

• SQLite adalah mesin basis data relasional berbasis file yang berjalan di dalam aplikasi
  • Semua data disimpan dalam satu file, sehingga tidak memerlukan aplikasi server terpisah
• Karena satu file tersebut sepenuhnya dikelola oleh aplikasi, ada risiko benturan jika beberapa proses mengaksesnya secara bersamaan
• Karena itu, aplikasi yang menggunakan SQLite harus memastikan hanya ada satu operasi tulis yang berjalan pada saat yang sama

Mode Write-Ahead-Log (WAL)

• SQLite meredakan batasan konkurensi melalui fitur WAL (Write-Ahead-Log)
  • File WAL berperan sebagai file jurnal yang mencatat perubahan pada basis data
  • Beberapa operasi tulis dapat diantrekan secara paralel, lalu perubahan dalam WAL diterapkan saat pembacaan
• Namun WAL juga tidak sempurna, dan dalam situasi tertentu konflik penguncian tetap terjadi

Masalah transaksi di SQLite

• Transaksi bertugas menjamin atomisitas operasi perubahan dan mengendalikan pemblokiran pembacaan
• Pada sebagian sistem Jellyfin, saat transaksi berlangsung, SQLite mengembalikan error “database is locked” lalu langsung berhenti
  • Masalah ini dilaporkan terlepas dari sistem operasi, kecepatan disk, maupun apakah berjalan di lingkungan virtualisasi
  • Karena sulit direproduksi dan muncul tidak teratur, akar penyebabnya sulit diidentifikasi

Cara Jellyfin menggunakan SQLite dan titik masalahnya

• Pada lingkungan yang direkomendasikan (storage non-jaringan, SSD), masalah jarang muncul, tetapi karena bug pembatasan pekerjaan paralel di versi sebelum 10.11
  • pekerjaan pemindaian library berjalan terlalu paralel sehingga memicu ribuan permintaan tulis secara bersamaan
  • hal ini melampaui batas retry dan timeout milik mesin SQLite, sehingga menyebabkan beban berlebih pada database dan error
• Transaksi yang panjang dan query yang tidak efisien juga memperburuk keadaan

Solusi berbasis EF Core

• Saat Jellyfin memigrasikan codebase ke EF Core, kontrol yang lebih terstruktur menjadi memungkinkan
• Dengan memanfaatkan Interceptor di EF Core, semua perintah dan eksekusi transaksi dapat dicegat untuk menerapkan kontrol penguncian yang transparan
• Tiga strategi penguncian diperkenalkan
  1. No-Lock: mode dasar, tanpa penguncian tambahan. Digunakan di sebagian besar kasus untuk mencegah penurunan performa
  2. Optimistic Locking: jika gagal, dilakukan retry menggunakan library Polly
  3. Pessimistic Locking: sebelum semua operasi tulis, seluruh basis data dikunci dengan ReaderWriterLockSlim

Cara kerja Optimistic Locking

• Pendekatan ini mengasumsikan operasi akan berhasil, lalu melakukan retry jika terjadi kegagalan
  • Jika dua operasi tulis saling bentrok, salah satunya gagal lalu menunggu sejenak sebelum mencoba lagi
• Dengan menggunakan library Polly, hanya kegagalan akibat penguncian yang diperlakukan sebagai target retry
• Dibanding pendekatan Pessimistic, cara ini memiliki overhead lebih kecil dan kehilangan performa yang lebih rendah

Cara kerja Pessimistic Locking

• Pada setiap operasi tulis, seluruh basis data dikunci
  • Selama penulisan, semua operasi baca dan tulis lain akan diblokir
• Pendekatan ini paling stabil, tetapi juga paling lambat
  • Misalnya, walaupun secara teori dimungkinkan menulis ke tabel “Bob” saat membaca tabel “Alice”, pendekatan ini tetap tidak mengizinkannya
• Dengan ReaderWriterLockSlim, banyak pembacaan diperbolehkan, tetapi hanya satu penulisan yang diizinkan

Rencana berikutnya: Smart Locking

• Sedang dipertimbangkan penerapan Smart Locking yang menggabungkan pendekatan Optimistic dan Pessimistic
  • Tujuannya adalah menggabungkan keunggulan keduanya untuk menyeimbangkan performa dan stabilitas

Hasil dan kemungkinan penerapan

• Hasil pengujian awal menunjukkan kedua mode penguncian efektif dalam mengatasi masalah
• Walau akar penyebab mendasarnya masih belum jelas, pengguna kini memiliki opsi untuk menggunakan Jellyfin dengan lebih stabil
• Di internet juga banyak laporan error serupa, tetapi belum ada solusi yang benar-benar tuntas
• Implementasi Jellyfin memiliki struktur berbasis interceptor EF Core yang mudah disalin dan diterapkan
  • Pemanggil tidak perlu menyadari bagaimana mekanisme penguncian bekerja di dalam
• Ini juga bisa langsung dimanfaatkan pada aplikasi EF Core lain yang mengalami masalah konkurensi SQLite yang sama

[GN⁺]

Komentar Hacker News

• Dulu pernah mengalami masalah blocking di SQLite, dan penyebabnya ternyata fragmentasi disk
  Pengguna yang memakai aplikasi di tablet Android lama selama bertahun-tahun, 8 jam sehari, mulai mengeluhkan perlambatan dan error lock
  Saat data disalin dan dikirim untuk diperiksa, masalahnya tidak bisa direproduksi, tetapi setelah perangkatnya diterima langsung dan dicek, ternyata masalah hilang sepenuhnya setelah DB file di-"defrag" dengan cara menyalinnya ke lokasi baru lalu mengembalikannya ke nama semula
  Peningkatan performa yang sama juga dialami pada DB Jellyfin

  • Kemungkinan besar itu bukan fragmentasi, melainkan degradasi memori flash. Penasaran apakah itu tablet murah dengan penyimpanan eMMC
  • Penasaran apakah efek yang sama bisa didapat dengan fungsi VACUUM di SQLite
  • Ini kasus yang menarik. Tapi kita tidak bisa menyuruh pengguna melakukan defrag sendiri, jadi perlu solusi yang realistis

• Transaksi SQLite secara default dimulai dalam mode "deferred"
  Artinya, write lock tidak diambil sampai benar-benar mencoba melakukan operasi tulis
  Error SQLITE_BUSY terjadi saat transaksi baca mencoba berubah menjadi tulis, tetapi transaksi lain sudah lebih dulu memegang write lock
  Solusinya adalah mengatur busy_timeout dan memulai transaksi yang mencakup penulisan dalam mode "immediate"
  Penjelasan terkait dirangkum dengan baik di posting blog ini

  • Saya juga awalnya mengira ini masalah SQLITE_BUSY. Saya mengumpulkan kasus-kasus terkait di sini
  • Saya menganggap SQLITE_BUSY sebagai semacam bau arsitektur. Dalam mode WAL, saya memisahkan connection pool read-only dan connection pool tulis tunggal. Dengan begitu, status kepemilikan lock bisa dipahami dengan jelas dan situasi kontensi bisa dirancang sejak awal
  • busy_timeout tidak berlaku dalam kasus ini. Dalam mode WAL, page ditambahkan ke satu file log, jadi jika mencoba berubah dari baca ke tulis saat sedang membaca, SQLite langsung gagal demi menjaga jaminan serialisasi. Mode "immediate" mencegah hal itu
  • Pada akhirnya, siapa pun yang memakai SQLite kemungkinan akan kena batunya sekali dan menghabiskan waktu melacak penyebab masalah ini
  • Sepertinya posting blog itu tidak menyebut SQLITE_BUSY, mungkin karena konfigurasinya belum diatur

• Sepertinya sebagian penjelasan di artikel itu keliru
  SQLite mengelola lock sendiri, jadi aplikasi tidak perlu mengendalikan akses file secara langsung
  Selain itu, WAL tidak memungkinkan beberapa penulisan paralel. WAL hanya memungkinkan pembacaan dan satu penulisan berjalan bersamaan

  • Saya juga sangat suka SQLite, tetapi artikel ini salah bahkan dari konsep dasar konkurensi, jadi saya tidak bisa merekomendasikannya

• SQLite adalah database yang luar biasa, tetapi sayang nilai default-nya terlalu konservatif
  Untuk benar-benar dipakai di layanan produksi, berbagai pengaturan PRAGMA perlu disesuaikan

  • Saya ingin tahu PRAGMA mana yang bagus untuk diaktifkan secara default
  • Dalam situasi seperti ini, mungkin lebih baik di-fork saja untuk membuat default baru

• Begitu fitur baru hctree di SQLite sudah stabil, saya berencana hanya memakai SQLite
  hc pada namanya kemungkinan singkatan dari High Concurrency
  Tautan dokumentasi resmi

• Melihat tulisan seperti ini, terasa seperti hanya berhenti pada solusi tambal sulam, bukan analisis akar masalah
  Rasanya akan lebih bernilai jika penyebab pastinya diungkap lewat debugging dan riset yang lebih dalam

  • Mungkin penulisnya hanya menyelidiki sebagian lalu membagikan solusi yang belum lengkap. Bisa juga tujuannya untuk memancing jawaban yang lebih baik di HN. Seperti ungkapan, "kalau ingin cepat dapat jawaban benar, kirim saja jawaban yang salah"

• Sepertinya penulis juga belum memahami bahwa mode WAL pada akhirnya tetap merupakan struktur single writer, multiple readers
  Penulisan paralel tidak mungkin dilakukan; WAL hanya mencegah transaksi baca diblokir oleh penulisan
  Akan bagus kalau ada MVCC penuh, tetapi struktur saat ini juga bekerja sangat baik asalkan prinsipnya dipahami

• Saya juga pernah mengalami masalah serupa di Jellyfin
  Biasanya berjalan normal, tetapi dalam kondisi tertentu DB menjadi macet dalam keadaan terkunci
  Log hanya menyisakan pesan "database is locked", dan akhirnya harus me-restart container Docker agar pulih
  Ini biasanya terjadi saat beberapa tombol di UI TV ditekan cepat secara berurutan

• Sedikit topik lain, tetapi jika memakai in-memory DB SQLite untuk operasi insert/delete dalam jumlah besar, penggunaan memori terus meningkat
  Misalnya jika 100 ribu baris dimasukkan lalu dihapus setiap 5 menit selama beberapa hari, memori di macOS bisa naik sampai 1GB
  Penasaran apakah ada pengaturan yang layak disesuaikan untuk kasus seperti ini

  • Coba pastikan apakah VACUUM dijalankan secara berkala, dan apakah auto_vacuum aktif
    Dokumentasi VACUUM
  • Bisa jadi ini perilaku normal karena buffer menyesuaikan diri secara dinamis dengan pola penggunaan
  • Jika semua baris dihapus, mungkin akan lebih efisien jika drop tabel lalu buat ulang saja

• SQLite memang bagus, tetapi melihat masalah seperti ini kadang terasa lebih baik langsung memakai Postgres
  Jika bukan demi portabilitas file tunggal atau penggunaan embedded, Postgres menangani masalah konkurensi dengan lebih sederhana

  • Tapi Jellyfin adalah server media self-hosted, jadi kalau mewajibkan Postgres, instalasi dan perawatannya jadi lebih rumit. SQLite lebih cocok
  • Kebanyakan penggunaan Jellyfin ada di lingkungan rumahan dengan satu pengguna, jadi SQLite sudah cukup. Hanya saja, konfigurasi saat ini tampaknya belum optimal
  • Mengabaikan kelebihan SQLite lalu beralih ke Postgres itu seperti berkata, "mau berkemah tapi malah disuruh membangun kabin"
  • Kalau memakai Postgres, selain instalasi, kita juga harus memikirkan migrasi saat upgrade versi. SQLite tidak punya beban seperti itu
  • Jellyfin baru-baru ini me-rewrite kode DB-nya dengan Entity Framework, jadi ke depan mereka sedang menyiapkan fleksibilitas untuk memilih DB

[ndrgrd]

Hah? Ada bagian yang bikin bingung, jadi saya langsung cek komentar dulu, dan seperti dugaan...