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