La concurrence dans SQLite, et pourquoi il faut en tenir compte

• La structure basée sur un fichier de SQLite est simple, mais lorsqu’il faut effectuer plusieurs écritures en même temps, des conflits de verrouillage (locking) peuvent survenir
• Jellyfin utilise SQLite depuis longtemps, mais sur certains systèmes, l’application pouvait s’interrompre à cause d’erreurs de base de données verrouillée pendant une transaction
• En exploitant la fonctionnalité d’intercepteur (interceptor) d’EF Core, trois stratégies de verrouillage (No-Lock, Optimistic, Pessimistic) ont été mises en place pour atténuer le problème
• L’approche Optimistic minimise la perte de performances grâce aux nouvelles tentatives, tandis que l’approche Pessimistic améliore la stabilité au prix d’un ralentissement
• Cette approche, facilement applicable à d’autres applications EF Core, propose une alternative concrète pour résoudre les problèmes de concurrence de SQLite

---

Structure de base et limites de SQLite

• SQLite est un moteur de base de données relationnelle basé sur un fichier, exécuté à l’intérieur de l’application
  • Toutes les données sont stockées dans un seul fichier, sans nécessiter d’application serveur distincte
• Comme l’application gère entièrement ce fichier unique, il existe un risque de conflit lorsque plusieurs processus y accèdent simultanément
• Par conséquent, une application qui utilise SQLite doit n’effectuer qu’une seule opération d’écriture à la fois

Mode Write-Ahead-Log (WAL)

• SQLite atténue ses contraintes de concurrence via la fonctionnalité WAL (Write-Ahead-Log)
  • Le fichier WAL joue le rôle de fichier journal qui enregistre les modifications de la base de données
  • Il met en file d’attente plusieurs écritures en parallèle et applique les changements du WAL lors de la lecture
• Cependant, le WAL n’est pas parfait et, dans certaines situations, des conflits de verrouillage surviennent encore

Problèmes de transaction dans SQLite

• Les transactions servent à garantir l’atomicité des modifications et à contrôler le blocage des lectures
• Sur certains systèmes Jellyfin, SQLite renvoyait une erreur « database is locked » pendant une transaction et s’arrêtait immédiatement
  • Ce problème a été signalé indépendamment du système d’exploitation, de la vitesse du disque ou de la présence de virtualisation
  • Comme il est difficile à reproduire et survient de manière irrégulière, il est compliqué d’en identifier la cause

Manière dont Jellyfin utilise SQLite et points problématiques

• Dans l’environnement recommandé (stockage non réseau, SSD), le problème reste rare, mais à cause d’un bug de limitation des tâches parallèles dans les versions antérieures à 10.11
  • Les tâches d’analyse de bibliothèque s’exécutaient avec un parallélisme excessif, provoquant des milliers de requêtes d’écriture simultanées
  • Cela dépassait les limites de nouvelle tentative et de délai d’attente du moteur SQLite, entraînant une surcharge de la base de données et des erreurs
• Les transactions longues et les requêtes inefficaces aggravent également le problème

Solution basée sur EF Core

• En migrant sa base de code vers EF Core, Jellyfin a pu obtenir un contrôle structurel plus fin
• En utilisant les intercepteurs (Interceptors) d’EF Core, l’exécution de toutes les commandes et transactions est interceptée pour mettre en place un contrôle de verrouillage transparent
• Trois stratégies de verrouillage ont été introduites
  1. No-Lock : mode par défaut, sans verrouillage supplémentaire. Utilisé dans la plupart des cas pour éviter une baisse de performances
  2. Optimistic Locking : en cas d’échec, des nouvelles tentatives sont effectuées avec la bibliothèque Polly
  3. Pessimistic Locking : avant chaque écriture, toute la base de données est verrouillée avec ReaderWriterLockSlim

Fonctionnement de l’Optimistic Locking

• Cette méthode part du principe que l’opération va réussir, puis relance en cas d’échec
  • Si deux écritures entrent en conflit, l’une échoue, attend un certain temps, puis réessaie
• La bibliothèque Polly est utilisée pour ne réessayer que les échecs dus au verrouillage
• Par rapport à l’approche Pessimistic, elle entraîne moins de surcharge et moins de perte de performances

Fonctionnement du Pessimistic Locking

• Lors de chaque opération d’écriture, l’ensemble de la base de données est verrouillé
  • Pendant l’écriture, toutes les opérations de lecture et d’écriture sont bloquées
• C’est la méthode la plus stable, mais aussi la plus lente
  • Par exemple, même s’il serait possible d’écrire dans la table « Bob » pendant qu’on lit la table « Alice », cette approche ne l’autorise pas
• ReaderWriterLockSlim permet plusieurs lectures simultanées, mais une seule écriture à la fois

Plan futur : Smart Locking

• L’introduction d’un Smart Locking combinant les approches Optimistic et Pessimistic est à l’étude
  • L’objectif est d’équilibrer performances et stabilité en réunissant les avantages des deux méthodes

Résultats et possibilités d’application

• Les premiers tests montrent que les deux modes de verrouillage sont efficaces pour résoudre le problème
• Même si la cause profonde du problème reste inconnue, les utilisateurs disposent désormais d’options pour utiliser Jellyfin de manière stable
• De nombreux signalements d’erreurs similaires existaient déjà sur internet, mais aucune solution complète n’avait été trouvée
• L’implémentation de Jellyfin repose sur des intercepteurs EF Core et peut être copiée et appliquée facilement
  • L’appelant n’a pas besoin d’être conscient du fonctionnement interne du verrouillage
• Elle peut aussi être utilisée immédiatement dans d’autres applications EF Core confrontées aux mêmes problèmes de concurrence avec SQLite

[GN⁺]

Commentaires sur Hacker News

• J’ai déjà rencontré un problème de blocage de SQLite, et la cause était la fragmentation du disque
  Des utilisateurs qui se servaient de l’application 8 heures par jour pendant des années sur de vieilles tablettes Android se plaignaient de lenteurs et d’erreurs de verrouillage
  Quand on récupérait une copie des données, le problème ne se reproduisait pas, mais après avoir finalement récupéré l’appareil lui-même, on a constaté que le problème disparaissait complètement en « défragmentant » la base, c’est-à-dire en copiant le fichier DB vers un nouvel emplacement puis en lui redonnant son nom d’origine
  J’ai observé le même gain de performance avec la DB de Jellyfin

  • Cela ressemble davantage à une dégradation de la mémoire flash qu’à de la fragmentation. Je me demande si c’était une tablette bas de gamme avec stockage eMMC
  • Je me demande si la fonction VACUUM de SQLite permettrait d’obtenir le même effet
  • C’est un cas intéressant. Mais on ne peut pas demander directement aux utilisateurs de défragmenter, donc il faut une solution réaliste

• Les transactions SQLite démarrent par défaut en mode « deferred »
  Autrement dit, elles ne prennent pas de write lock tant qu’une écriture effective n’est pas tentée
  L’erreur SQLITE_BUSY survient lorsqu’une transaction de lecture essaie de se transformer en écriture alors qu’une autre transaction détient déjà le write lock
  La solution consiste à définir busy_timeout et à démarrer en mode « immediate » les transactions qui incluent des écritures
  C’est bien expliqué dans ce billet de blog

  • Au départ, j’ai moi aussi pensé qu’il s’agissait d’un problème de SQLITE_BUSY. J’ai rassemblé des cas similaires ici
  • Je considère SQLITE_BUSY comme une sorte d’odeur architecturale. En mode WAL, je conçois séparément un pool de connexions en lecture seule et un pool avec une unique connexion en écriture. Cela permet de savoir clairement qui détient les verrous et de concevoir à l’avance les situations de contention
  • busy_timeout ne s’applique pas dans ce cas. En mode WAL, les pages sont ajoutées à un unique fichier journal, donc si l’on passe d’une lecture à une écriture, SQLite échoue immédiatement afin de garantir la sérialisation. Le mode « immediate » évite cela
  • Au fond, toute personne qui utilise SQLite finira tôt ou tard par se brûler sur ce problème et passera du temps à en traquer la cause
  • Le billet de blog ne mentionnait pas SQLITE_BUSY, donc il manquait probablement un élément de configuration

• Une partie des explications dans l’article semble erronée
  SQLite gère lui-même les verrous, donc l’application n’a pas besoin de contrôler directement l’accès au fichier
  Par ailleurs, WAL n’autorise pas plusieurs écritures parallèles. Il permet seulement d’effectuer simultanément des lectures et une seule écriture

  • J’adore vraiment SQLite moi aussi, mais je ne peux pas recommander cet article : il se trompe dès les notions de base sur la concurrence

• SQLite est une excellente base de données, mais ses valeurs par défaut sont trop conservatrices, ce qui est regrettable
  Pour l’utiliser en production, il faut ajuster plusieurs PRAGMA

  • J’aimerais savoir quels PRAGMA il est bon d’activer par défaut
  • Dans ce genre de situation, je me dis qu’il vaudrait presque mieux faire un fork pour définir de nouvelles valeurs par défaut

• Quand la nouvelle fonctionnalité hctree de SQLite sera stabilisée, je pense que je n’utiliserai plus que SQLite
  Le hc du nom signifie probablement High Concurrency
  Lien vers la documentation officielle

• Ce type d’article donne l’impression de s’arrêter à des solutions de contournement plutôt qu’à une analyse de la cause racine du problème
  Il serait bien plus utile de pousser le débogage et la recherche pour identifier précisément la cause

  • L’auteur a sans doute seulement enquêté en partie et partagé une solution incomplète. Il voulait peut-être susciter de meilleures réponses sur HN. Un peu dans l’esprit du dicton : « si tu postes une mauvaise réponse, tu obtiendras plus vite la bonne »

• Il semble aussi ne pas avoir compris que le mode WAL reste fondamentalement un modèle une seule écriture, plusieurs lectures
  Les écritures parallèles sont impossibles ; cela évite seulement qu’une transaction de lecture soit bloquée par une écriture
  Un vrai MVCC serait appréciable, mais la structure actuelle fonctionne déjà très bien si l’on en comprend les principes

• J’ai rencontré un problème similaire avec Jellyfin
  En temps normal, tout fonctionne bien, mais dans certaines situations la DB reste bloquée avec un verrou
  Les logs ne contiennent qu’un « database is locked », et il faut finalement redémarrer le conteneur Docker pour résoudre le problème
  Cela se produit surtout quand on appuie rapidement sur plusieurs boutons dans l’interface TV

• C’est un peu un autre sujet, mais si l’on utilise une DB in-memory SQLite pour beaucoup d’insertions/suppressions, l’usage mémoire augmente progressivement
  Par exemple, si l’on insère puis supprime 100 000 lignes toutes les 5 minutes pendant plusieurs jours, la mémoire peut monter jusqu’à 1 Go sur macOS
  Je me demande s’il y a des réglages à ajuster dans ce cas

  • Je recommanderais de vérifier si vous exécutez VACUUM périodiquement et si auto_vacuum est activé
    Documentation VACUUM
  • Il est aussi possible que ce soit un comportement normal, où les buffers s’ajustent dynamiquement au profil d’utilisation
  • Si vous supprimez toutes les lignes, il est plus efficace de simplement supprimer puis recréer la table

• SQLite est excellent, mais face à ce genre de problème, on se dit parfois qu’il vaudrait mieux utiliser Postgres
  En dehors des usages embarqués ou de la portabilité d’un fichier unique, Postgres résout plus simplement les problèmes de concurrence

  • Mais Jellyfin est un serveur multimédia auto-hébergé, donc exiger Postgres compliquerait l’installation et la maintenance. SQLite est plus adapté
  • Jellyfin fonctionne le plus souvent dans un environnement domestique mono-utilisateur, donc SQLite suffit largement. Cela dit, la configuration actuelle ne semble pas optimale
  • Ignorer les avantages de SQLite pour passer à Postgres revient un peu à dire : « tu veux faire du camping, alors construis une cabane »
  • Avec Postgres, il faut non seulement gérer l’installation, mais aussi les migrations lors des montées de version. SQLite n’impose pas cette charge
  • Jellyfin a récemment réécrit son code DB avec Entity Framework, ce qui devrait lui permettre à l’avenir de rendre le choix de la base de données plus flexible

[ndrgrd]

Hein ? Il y avait un passage qui m'a fait tiquer, alors je suis allé vérifier directement les commentaires, et évidemment...