SQLite-Nebenläufigkeit und warum man sie berücksichtigen sollte

• Die dateibasierte Struktur von SQLite ist einfach, kann aber bei mehreren gleichzeitigen Schreibvorgängen zu Sperrkonflikten (Locking) führen
• Jellyfin nutzt seit Langem SQLite, doch auf einigen Systemen kam es zu Problemen, bei denen die Anwendung aufgrund von Datenbank-Sperrfehlern während Transaktionen abstürzte
• Mithilfe der Interceptor-Funktion von EF Core wurden drei Sperrstrategien (No-Lock, Optimistic, Pessimistic) implementiert, um das Problem zu entschärfen
• Der optimistische Ansatz minimiert Leistungsverluste durch Retry-Logik, während der pessimistische Ansatz die Stabilität erhöht, dafür aber Geschwindigkeit einbüßt
• Dieser Ansatz ist leicht auch auf andere EF-Core-Anwendungen übertragbar und bietet eine praktische Alternative zur Lösung von SQLite-Nebenläufigkeitsproblemen

---

Grundstruktur und Einschränkungen von SQLite

• SQLite ist eine dateibasierte relationale Datenbank-Engine, die innerhalb der Anwendung ausgeführt wird
  • Alle Daten werden in einer einzigen Datei gespeichert, und es ist keine separate Serveranwendung erforderlich
• Da die Anwendung diese einzelne Datei vollständig verwaltet, besteht bei gleichzeitigem Zugriff mehrerer Prozesse Kollisionsgefahr
• Daher sollten Anwendungen, die SQLite verwenden, immer nur einen Schreibvorgang gleichzeitig ausführen

Write-Ahead-Log-(WAL)-Modus

• SQLite lockert seine Nebenläufigkeitsbeschränkungen mit der WAL-(Write-Ahead-Log)-Funktion
  • Die WAL-Datei dient als Journaldatei, die Änderungen an der Datenbank aufzeichnet
  • Mehrere Schreibvorgänge können parallel in eine Queue gestellt werden, und beim Lesen werden die Änderungen aus dem WAL angewendet
• WAL ist jedoch nicht perfekt, und in bestimmten Situationen treten weiterhin Sperrkonflikte auf

SQLite-Transaktionsprobleme

• Transaktionen sorgen für die Atomarität von Änderungen und steuern die Blockierung von Lesezugriffen
• Auf einigen Jellyfin-Systemen trat während Transaktionen das Verhalten auf, dass SQLite den Fehler „database is locked“ zurückgab und sofort abbrach
  • Dieses Problem wurde unabhängig von Betriebssystem, Datenträgergeschwindigkeit oder Virtualisierung gemeldet
  • Es ist schwer reproduzierbar und tritt unregelmäßig auf, was die Ursachenanalyse erschwert

Wie Jellyfin SQLite nutzt und wo die Probleme liegen

• In empfohlenen Umgebungen (kein Netzwerk-Storage, SSD) tritt das Problem selten auf, aber durch einen Bug bei der Begrenzung paralleler Aufgaben in Versionen vor 10.11 kam es dazu, dass
  • Bibliotheksscans übermäßig parallel ausgeführt wurden und Tausende gleichzeitige Schreibanforderungen entstanden
  • die Retry- und Timeout-Grenzen der SQLite-Engine überschritten wurden, was zu Datenbanküberlastung und Fehlern führte
• Lange Transaktionen und ineffiziente Abfragen verschärften das Problem zusätzlich

Lösungsansatz auf Basis von EF Core

• Durch die Migration der Codebasis auf EF Core erhielt Jellyfin mehr strukturelle Kontrolle
• Mithilfe der Interceptors von EF Core werden alle Befehle und Transaktionsausführungen abgefangen, um transparente Sperrsteuerung zu implementieren
• Es wurden drei Sperrstrategien eingeführt
  1. No-Lock: Standardmodus ohne zusätzliche Sperren. Wird in den meisten Fällen genutzt, um Performance-Einbußen zu vermeiden
  2. Optimistic Locking: Bei Fehlschlägen werden mit der Polly-Bibliothek Retries ausgeführt
  3. Pessimistic Locking: Vor jedem Schreibvorgang wird die gesamte Datenbank mit ReaderWriterLockSlim gesperrt

Funktionsweise von Optimistic Locking

• Dieser Ansatz geht davon aus, dass ein Vorgang erfolgreich sein wird, und versucht ihn nur bei Fehlschlag erneut
  • Wenn zwei Schreibvorgänge kollidieren, schlägt einer fehl, wartet eine gewisse Zeit und wird dann erneut versucht
• Mit der Polly-Bibliothek werden nur durch Sperren verursachte Fehler als Retry-Kandidaten behandelt
• Im Vergleich zum pessimistischen Ansatz ist der Overhead geringer und der Performance-Verlust kleiner

Funktionsweise von Pessimistic Locking

• Zum Zeitpunkt jedes Schreibvorgangs wird die gesamte Datenbank gesperrt
  • Während des Schreibens werden alle Lese- und Schreibvorgänge blockiert
• Dieser Ansatz ist am stabilsten, aber auch am langsamsten
  • So würde zum Beispiel selbst dann kein Schreiben in die Tabelle „Bob“ erlaubt, wenn gerade nur die Tabelle „Alice“ gelesen wird
• Mit ReaderWriterLockSlim sind mehrere gleichzeitige Lesezugriffe erlaubt, aber immer nur ein einzelner Schreibzugriff

Ausblick: Smart Locking

• Die Einführung von Smart Locking, das optimistische und pessimistische Verfahren kombiniert, wird derzeit geprüft
  • Ziel ist es, die Vorteile beider Ansätze zu vereinen und ein Gleichgewicht zwischen Performance und Stabilität zu erreichen

Ergebnisse und Übertragbarkeit

• Erste Testergebnisse zeigen, dass beide Sperrmodi wirksam zur Lösung des Problems beitragen
• Die eigentliche Grundursache des Problems ist zwar weiterhin unklar, doch Nutzer haben nun Optionen, Jellyfin stabil zu verwenden
• Auch im Internet gab es viele Berichte über ähnliche Fehler, jedoch ohne vollständig überzeugende Lösung
• Die Implementierung von Jellyfin ist auf EF-Core-Interceptors aufgebaut und lässt sich leicht kopieren und anwenden
  • Aufrufer müssen die interne Sperrlogik nicht kennen
• Damit ist der Ansatz auch sofort in anderen EF-Core-Anwendungen nutzbar, die unter denselben SQLite-Nebenläufigkeitsproblemen leiden

[GN⁺]

Hacker-News-Kommentare

• Ich hatte früher einmal ein Blocking-Problem mit SQLite; die Ursache war eine Fragmentierung auf dem Datenträger.
  Nutzer, die die App auf alten Android-Tablets über Jahre hinweg täglich 8 Stunden benutzt hatten, beklagten Verlangsamungen und Lock-Fehler.
  Wenn ich mir die Daten kopieren ließ, konnte ich das Problem nicht reproduzieren. Als ich schließlich das Gerät selbst bekam und nachsah, verschwand das Problem vollständig, nachdem ich die DB-Datei an einen neuen Speicherort kopiert und dann unter dem ursprünglichen Namen zurückkopiert hatte – also praktisch „defragmentiert“ hatte.
  Dasselbe Vorgehen brachte auch bei der Jellyfin-DB einen Leistungsschub.

  • Das war wahrscheinlich eher Flash-Speicher-Verschleiß als Fragmentierung. Ich frage mich, ob das billige Tablets mit eMMC-Speicher waren.
  • Ich frage mich, ob man mit der VACUUM-Funktion von SQLite denselben Effekt erzielen könnte.
  • Ein interessanter Fall. Aber man kann Nutzer nicht einfach selbst defragmentieren lassen, also braucht es eine praktikable Lösung.

• SQLite-Transaktionen starten standardmäßig im „deferred“-Modus.
  Das heißt, bis tatsächlich ein Schreibvorgang versucht wird, wird kein Write Lock gehalten.
  Der Fehler SQLITE_BUSY tritt auf, wenn eine Lese-Transaktion in eine Schreib-Transaktion wechseln will, während bereits eine andere Transaktion den Write Lock hält.
  Die Lösung ist, busy_timeout zu setzen und Transaktionen mit Schreibvorgängen im „immediate“-Modus zu starten.
  Das ist in diesem Blogbeitrag gut erklärt.

  • Ich dachte anfangs auch, das sei ein SQLITE_BUSY-Problem. Verwandte Fälle habe ich hier gesammelt.
  • Ich halte SQLITE_BUSY für eine Art Architekturgeruch. Im WAL-Modus entwerfe ich mit getrennten Pools für reine Leseverbindungen und eine einzelne Schreibverbindung. So ist klar nachvollziehbar, wann Locks gehalten werden, und Contention lässt sich von vornherein in das Design einplanen.
  • busy_timeout greift in diesem Fall nicht. Im WAL-Modus werden Seiten an eine einzelne Log-Datei angehängt; wenn man während des Lesens auf Schreiben umschalten will, lässt SQLite zur Wahrung der Serialisierung sofort einen Fehler zurückgeben. Der „immediate“-Modus verhindert das.
  • Wer SQLite verwendet, wird sich an diesem Problem letztlich wohl irgendwann einmal die Finger verbrennen und dann Zeit mit der Ursachenforschung verbringen.
  • Im Blogbeitrag wurde SQLITE_BUSY gar nicht erwähnt; vermutlich fehlte diese Konfiguration einfach.

• Ein Teil der Erklärungen im Artikel scheint falsch zu sein.
  SQLite verwaltet Locks selbst, daher muss die Anwendung den Dateizugriff nicht eigenständig kontrollieren.
  Außerdem erlaubt WAL keine parallelen Schreibvorgänge. Es ermöglicht nur gleichzeitiges Lesen und genau einen Schreibvorgang.

  • Ich mag SQLite wirklich sehr, aber dieser Artikel liegt schon bei grundlegenden Nebenläufigkeitskonzepten daneben, deshalb kann ich ihn nicht empfehlen.

• SQLite ist eine großartige Datenbank, aber die Standardwerte sind mir zu konservativ.
  Für den produktiven Einsatz muss man diverse PRAGMA-Einstellungen anpassen.

  • Ich würde gern wissen, welche PRAGMAs man standardmäßig aktivieren sollte.
  • In so einem Fall wäre es vielleicht besser, einfach einen Fork mit neuen Standardwerten zu machen.

• Wenn das neue hctree-Feature von SQLite stabil ist, werde ich wohl nur noch SQLite verwenden.
  Das hc im Namen steht vermutlich für High Concurrency.
  Link zur offiziellen Dokumentation

• Bei solchen Artikeln habe ich oft das Gefühl, dass sie eher bei Workarounds stehen bleiben, statt die eigentliche Ursache zu analysieren.
  Wirklich wertvoll wäre es, mit tieferem Debugging und mehr Untersuchung die genaue Ursache offenzulegen.

  • Vermutlich hat der Autor nur einen Teil untersucht und dann eine unvollständige Lösung geteilt. Vielleicht auch in der Absicht, auf HN bessere Antworten zu provozieren – nach dem Motto: Wenn man eine falsche Antwort postet, bekommt man schneller die richtige.

• Offenbar wurde auch nicht verstanden, dass der WAL-Modus letztlich ebenfalls eine Struktur aus einem Schreiber und vielen Lesern ist.
  Paralleles Schreiben ist nicht möglich; es sorgt nur dafür, dass Lese-Transaktionen nicht durch Schreibvorgänge blockiert werden.
  Ein vollständiges MVCC wäre schön, aber auch die aktuelle Architektur funktioniert gut, wenn man ihr Prinzip versteht.

• Ich hatte bei Jellyfin ein ähnliches Problem.
  Normalerweise läuft es gut, aber in bestimmten Situationen bleibt die DB in gesperrtem Zustand hängen.
  Im Log steht dann nur „database is locked“, und am Ende hilft nur ein Neustart des Docker-Containers.
  Das passiert vor allem, wenn man in der TV-Oberfläche schnell mehrere Buttons nacheinander betätigt.

• Etwas anderes Thema, aber wenn man eine SQLite-In-Memory-DB für große Mengen an Insert/Delete-Operationen verwendet, steigt der Speicherverbrauch immer weiter an.
  Wenn man zum Beispiel alle 5 Minuten 100.000 Zeilen einfügt und wieder löscht und das über mehrere Tage wiederholt, steigt der Speicher unter macOS bis auf 1 GB.
  Ich frage mich, ob es dafür Einstellungen gibt, die man anpassen sollte.

  • Ich würde prüfen, ob du VACUUM regelmäßig ausführst und ob auto_vacuum aktiviert ist.
    VACUUM-Dokumentation
  • Das könnte auch normales Verhalten sein, bei dem Puffer dynamisch an das Nutzungsmuster angepasst werden.
  • Wenn ohnehin alle Zeilen gelöscht werden, ist es effizienter, die Tabelle zu droppen und neu anzulegen.

• SQLite ist großartig, aber bei solchen Problemen fragt man sich manchmal, ob man nicht lieber Postgres verwenden sollte.
  Wenn es nicht gerade um die Portabilität einer einzelnen Datei oder um eingebettete Nutzung geht, löst Postgres Nebenläufigkeitsprobleme oft einfacher.

  • Aber Jellyfin ist ein selbstgehosteter Medienserver, und wenn man dort Postgres voraussetzt, werden Installation und Wartung komplizierter. SQLite passt besser.
  • Jellyfin läuft meistens in einer Heimumgebung mit nur einem Nutzer, daher reicht SQLite völlig aus. Nur die aktuelle Konfiguration scheint nicht optimal zu sein.
  • Die Vorteile von SQLite zu ignorieren und stattdessen Postgres zu fordern, ist so, als würde man jemandem, der zelten will, empfehlen, stattdessen eine Hütte zu bauen.
  • Mit Postgres muss man sich nicht nur um die Installation kümmern, sondern auch um Migrationen bei Versions-Upgrades. Diese Last hat man mit SQLite nicht.
  • Jellyfin hat den DB-Code kürzlich mit Entity Framework neu geschrieben und bereitet sich damit darauf vor, die Wahl der Datenbank künftig flexibler zu machen.

[ndrgrd]

Hm? An einer Stelle kam mir etwas seltsam vor, also habe ich sofort zuerst die Kommentare geprüft — wie erwartet ...