Transaktionen in SQLite

• In den letzten Jahren hat SQLite als leistungsfähige In-Process-SQL-Datenbank-Engine mit hoher Zuverlässigkeit für den Einsatz als Backend von Serverprozessen stark an Aufmerksamkeit gewonnen
• Die Popularität steigt rasant, obwohl die SQLite-Entwickler einen solchen Einsatz statt der traditionellen Rolle als Client- oder Edge-Anwendung kaum aktiv befürworten

Die wichtigsten Gründe, warum ich mich für SQLite interessiere:

• Konzeptionell einfach: Man stelle sich einen nach Primärschlüsseln partitionierten B-Tree aus Zeilen/Tupeln vor. Dieser wurde umfassend getestet, um dauerhaft zuverlässig auf Datenträgern gespeichert zu werden, und erhielt darüber hinaus eine SQL-Interaktionsschicht
• Mit Litestream lässt sich eine praktikable Backup-Strategie aufbauen. Das WAL wird an einen entfernten Ort gesichert und kontinuierlich repliziert. Backups lassen sich beim Start automatisch mit einem einfachen Befehl wiederherstellen.
• Ich mag nach wie vor eine vollständige Entwicklungsumgebung, die offline lauffähig ist
• Mit file::memory: sind In-Memory-Workloads möglich, sodass Testcode bei Bedarf problemlos Instanzen starten und beenden kann

Beschränkung auf einen einzelnen Writer

• Die „Beschränkungen von SQLite auf dem Server“ sind von den SQLite-Entwicklern gut dokumentiert, ebenso wie die beste serverseitige Konfiguration. Die auffälligste Grenze sind jedoch Websites mit hohem Traffic, also Websites mit vielen Schreibvorgängen
• Im WAL-Modus verwendet SQLite per Design einen einzelnen Writer. Dadurch ist gleichzeitig höchstens 1 Schreibtransaktion und mehrere schreibgeschützte Transaktionen erlaubt
• Dieses Design macht die Verwaltung des Durchsatzes dieses einzelnen Writers zum Engpass bei stark schreiblastigen Websites. Damit sind wir wieder bei einem der Kernelemente moderner Technik angelangt

SQLite

• SQLite bietet standardmäßig strikt isolierte SERIALIZABLE-Transaktionen. Das ist die stärkste Stufe der Isolationsgarantie
• Durch den einzelnen Writer verwendet SQLite eine Form der pessimistischen Nebenläufigkeitskontrolle, mit der sich leicht sicherstellen lässt, dass sich die zugrunde liegenden Daten während einer Schreibtransaktion nicht geändert haben

Postgres

• Postgres weicht tatsächlich vom im SQL-Standard definierten Standard SERIALIZABLE ab und wählt das lockerere READ COMMITTED (trotz deutlich komplexerer Multiversion concurrency control)
  • Diese geringere Strenge birgt das Risiko von non-repeatable reads. Das heißt: Wenn Werte durch andere COMMITTED-Transaktionen im Hintergrund verändert werden, kann dieselbe Leseabfrage selbst innerhalb derselben Transaktion bei mehreren Ausführungen unterschiedliche Ergebnisse liefern
  • Mit dieser Isolationsstufe öffnet Postgres die Möglichkeit, dass Transaktionen mit veralteten Daten arbeiten. Entwickler müssen das im Hinterkopf behalten
• Wird SERIALIZABLE gesetzt, verwendet Postgres ein Schema der optimistischen Nebenläufigkeitskontrolle, verfolgt die während der Transaktion angesprochenen Daten und prüft vor dem Commit, ob sie unverändert geblieben sind
  • Postgres tut dies abhängig von der Transaktion mit Sperren auf Zeilen- oder Seitenebene, um den Speicherverbrauch zu steuern
  • Dieses Muster heißt optimistisch, weil erwartet wird, dass sich die zugrunde liegenden Daten nicht geändert haben, da mit zunehmender Granularität der von der Transaktion überwachten Daten die Wahrscheinlichkeit sinkt, dass sie sich bis zum Commit ändern

FoundationDB

• Transaktionen sind nicht nur auf relationale Datenbanken beschränkt. Zur Erreichung von SERIALIZABLE-Garantien in einem verteilten Key-Value-Store wird optimistische Nebenläufigkeitskontrolle eingesetzt
• Als NoSQL aufkam, waren verteilte NoSQL-Stores mit ACID-Garantien nicht üblich. FoundationDB veröffentlichte ein Transaktions-Manifest, das hervorhob, dass Entwickler stark von ACID-Garantien profitieren können
• FoundationDB gibt Hinweise dazu, wann und wie man Code für optimistische Nebenläufigkeitskontrolle schreibt, sowie dazu, dass sich Daten durch Konflikte zwischen gleichzeitigen Transaktionen manchmal ändern und Transaktionen automatisch erneut versucht werden

Idempotenz

• Eine idempotente Transaktion ist eine Transaktion, die beim einmaligen und beim zweimaligen Commit denselben Effekt hat
• FoundationDB bietet Muster, um Transaktionen idempotent zu machen und Probleme zu vermeiden, wenn sie aufgrund von Konflikten mehrfach erneut versucht werden müssen

Welche Optionen bietet SQLite also vor diesem Hintergrund?

BEGIN …

SQLite bietet mehrere Möglichkeiten, der Engine mitzuteilen, wie sich Transaktionen verhalten sollen. Für Entwickler äußert sich das in den Schlüsselwörtern IMMEDIATE, EXCLUSIVE und DEFERRED, die sich im WAL-Modus im Wesentlichen auf DEFERRED vs. IMMEDIATE reduzieren lassen

DEFERRED

• Die Transaktion startet im READ-Modus, in dem sie gleichzeitig mit anderen Lese- oder Schreibtransaktionen laufen kann
• Erst wenn eine Abfrage ausgeführt wird, die den DB-Zustand verändert (INSERT, UPDATE, DELETE), wird sie zu einer blockierenden READ-WRITE-Transaktion hochgestuft
• Ist die DB beim Upgrade durch eine andere Transaktion gesperrt, wird der Fehler SQLITE_BUSY zurückgegeben. Der Client muss dies behandeln

IMMEDIATE

• Die Transaktion startet sofort im READ-WRITE-Modus
• Wenn bereits eine Schreibtransaktion läuft, wird sofort SQLITE_BUSY zurückgegeben
• Der Client muss entscheiden, wie damit umzugehen ist

CONCURRENT

• SQLite besitzt einen experimentellen Zweig, der Transaktionen von pessimistisch zu eingeschränkt optimistisch verschiebt
• Eingeschränkt deshalb, weil optimistisches Locking nicht auf Zeilen-/Tupel-Ebene, sondern auf DB-Seitenebene (standardmäßig 4096 Bytes) arbeitet
• Im CONCURRENT-Modus kann SQLite mehrere Schreibtransaktionen gleichzeitig aktivieren, prüft aber vor dem Commit, ob die während der Transaktion angesprochenen Seiten seit Beginn der Transaktion unverändert geblieben sind
• Wenn kein Konflikt auftritt, werden die Änderungen der Reihe nach committet und strikte SERIALIZABLE-Garantien erreicht. Bei Konflikten wird SQLITE_BUSY zurückgegeben

HC-Tree

• Ein weiterer experimenteller Zweig von SQLite ist [HC-Tree], eine laufende Arbeit mit dem Ziel, optimistisches Locking auf Zeilen-/Tupel-Ebene bereitzustellen. Eines der interessanten Ergebnisse ist ein ausgezeichnetes Benchmark-Set, das die Leistungsvorteile eines solchen Designs im Vergleich zum Zweig BEGIN CONCURRENT zeigt

Wie wäre es, diesen Benchmarking-Ansatz zu übernehmen und ihn für die Standardoptionen auszuführen?

Benchmarking

nUpdate=1, nScan=0

• Diese reine Schreibtransaktion zeigt deutlich die Vorteile von IMMEDIATE gegenüber DEFERRED. Das Locking erfolgt sofort, und die Transaktion leidet nicht unter den Kosten des Upgrades
• CONCURRENT zeigt mit steigender Thread-Zahl und zunehmenden Konflikten einen höheren Durchsatz

nUpdate=10, nScan=0

• Wie zu erwarten hilft die Bündelung von Schreibvorgängen bei 16 Threads stark bei der Zahl aktualisierter Zeilen. CONCURRENT steigt von etwa ~12k/s auf ~19k/s
• IMMEDIATE vs. DEFERRED wird weniger wichtig, weil die Kosten der Aktualisierung selbst größer werden als die Kosten des Transaktions-Upgrades

nUpdate=1, nScan=10

• Diese Transaktion sollte durch zufällige Lesezugriffe die Schwäche des seitenbasierten CONCURRENT-Lockings offenlegen
• Sie zeigt sofort, warum IMMEDIATE für zu aktualisierende Transaktionen wichtiger ist als die Upgrade-Kosten von DEFERRED
• Im Fall von CONCURRENT sind diese Ergebnisse sehr robust, weil die zugrunde liegenden Konflikte tatsächlich nicht stark zugenommen haben

nUpdate=0, nScan=10

• Diese schreibgeschützte Batch-Transaktion zeigt die Auswirkungen pessimistischer Nebenläufigkeitskontrolle
• Sie zeigt, warum man IMMEDIATE nicht als Standard für alle Transaktionen setzen sollte
• CONCURRENT vs. IMMEDIATE deutet darauf hin, dass der Einsatz des CONCURRENT-Modus einen kleinen Nachteil hat. „In allen Fällen ist die Leistung etwas schlechter“
  • Trotzdem wäre CONCURRENT eine gute Standardoption