Les transactions de SQLite

• Ces dernières années, SQLite a gagné en popularité comme moteur de base de données SQL embarqué, hautement fiable, utilisé en backend dans des processus serveur
• Cette popularité a explosé alors même que les développeurs de SQLite ont presque activement déconseillé son usage dans ce rôle, plutôt que dans son rôle traditionnel d’application cliente ou edge

Les principales raisons qui m’ont amené à m’intéresser à SQLite :

• Une simplicité conceptuelle : imaginez un B-tree de lignes/tuples partitionnés par clé primaire. Le tout a été largement testé pour persister de manière fiable sur disque, avec une couche d’interaction SQL ajoutée par-dessus
• Il est possible de mettre en place une stratégie de sauvegarde pratique via Litestream. Le WAL peut être sauvegardé vers un emplacement distant et répliqué en continu. Les sauvegardes peuvent être restaurées automatiquement au démarrage avec une simple commande
• J’apprécie toujours un environnement de développement complet pouvant fonctionner hors ligne
• Avec file::memory:, il est possible de travailler en mémoire, ce qui permet au code de test de démarrer et d’arrêter facilement des instances selon les besoins

Limitation du single-writer

• Les « limitations de SQLite sur un serveur » sont bien documentées par les développeurs de SQLite, qui ont aussi analysé les meilleurs réglages côté serveur. Mais la limite la plus visible concerne les sites web à fort trafic, c’est-à-dire les sites avec beaucoup d’écritures
• En mode WAL, SQLite utilise par conception un seul writer. Cela autorise au maximum une transaction d’écriture simultanée, ainsi que plusieurs transactions en lecture seule
• Cette conception fait du débit de ce writer unique le goulot d’étranglement des sites web à fort trafic orientés écriture. On revient ainsi à l’un des composants fondamentaux des systèmes modernes

SQLite

• SQLite fournit par défaut des transactions strictement isolées de niveau SERIALIZABLE. C’est le niveau de garantie d’isolation le plus fort
• En utilisant un seul writer, SQLite emploie une forme de contrôle de concurrence pessimiste qui permet de garantir facilement que les données sous-jacentes n’ont pas été modifiées pendant l’exécution d’une transaction d’écriture

Postgres

• En pratique, Postgres s’écarte du défaut SERIALIZABLE défini par la norme SQL et choisit à la place le niveau plus permissif READ COMMITTED (malgré un contrôle de concurrence multiversion bien plus complexe)
  • Cette réduction de la rigueur introduit un risque de non-repeatable reads : autrement dit, au sein d’une même transaction, la même requête de lecture peut produire des résultats différents lorsqu’elle est exécutée plusieurs fois si une autre transaction COMMITTED a modifié les valeurs en arrière-plan
  • En choisissant ce niveau d’isolation, Postgres ouvre la possibilité qu’une transaction travaille sur des données périmées. Les développeurs doivent garder cela à l’esprit
• Lorsqu’il est configuré en SERIALIZABLE, Postgres utilise un schéma de contrôle de concurrence optimiste pour suivre les données consultées pendant la transaction et vérifier avant le commit qu’elles n’ont pas changé
  • Postgres fait cela en s’appuyant sur des verrous au niveau ligne ou page selon la transaction, afin de maîtriser l’usage mémoire
  • Ce modèle est qualifié d’optimistic parce qu’il part du principe que les données sous-jacentes ne devraient pas avoir changé : plus les données surveillées par la transaction sont fines au moment du commit, moins elles ont de chances d’avoir été modifiées

FoundationDB

• Les transactions ne se limitent pas aux bases de données relationnelles. Pour obtenir des garanties SERIALIZABLE dans un magasin clé-valeur distribué, on utilise aussi un contrôle de concurrence optimiste
• À l’époque de l’essor du NoSQL, les magasins NoSQL distribués avec garanties ACID n’étaient pas courants. FoundationDB a publié un manifeste sur les transactions pour souligner à quel point les développeurs pouvaient bénéficier de garanties ACID
• FoundationDB fournit des conseils sur la manière d’écrire du code pour le contrôle de concurrence optimiste, ainsi que sur le fait que des conflits entre transactions concurrentes peuvent parfois modifier les données et entraîner une relance automatique de la transaction

Idempotence

• Une transaction idempotente est une transaction qui a le même effet si elle est validée une fois ou deux
• FoundationDB propose des patterns pour rendre les transactions idempotentes afin d’éviter les problèmes lorsqu’une transaction doit être relancée plusieurs fois à cause de conflits

Alors, en gardant tout cela à l’esprit, quelles options SQLite propose-t-il ?

BEGIN …

SQLite propose plusieurs façons d’indiquer au moteur comment une transaction doit se comporter, sous la forme des mots-clés IMMEDIATE, EXCLUSIVE et DEFERRED, qui en mode WAL se ramènent essentiellement à DEFERRED vs IMMEDIATE

DEFERRED

• La transaction démarre en mode READ, ce qui lui permet de s’exécuter en même temps que d’autres transactions de lecture ou d’écriture
• Elle n’est promue en transaction READ-WRITE bloquante que lorsqu’une requête qui modifie l’état de la base (INSERT, UPDATE, DELETE) est exécutée
• Si, au moment de cette promotion, la base est verrouillée par une autre transaction, une erreur SQLITE_BUSY est renvoyée. Le client doit la gérer

IMMEDIATE

• La transaction démarre immédiatement en mode READ-WRITE
• Si une transaction d’écriture est déjà en cours, SQLITE_BUSY est renvoyé immédiatement
• C’est au client de décider comment traiter ce cas

CONCURRENT

• SQLite dispose d’une branche expérimentale qui fait évoluer les transactions d’un modèle pessimiste vers un modèle optimiste limité
• Cette limitation vient du fait que le verrouillage optimiste fonctionne au niveau des pages de la base (4096 bytes par défaut), et non au niveau des lignes/tuples
• En mode CONCURRENT, SQLite peut autoriser plusieurs transactions d’écriture actives en parallèle, mais avant le commit il vérifie que les pages consultées pendant la transaction n’ont pas été modifiées depuis son démarrage
• S’il n’y a pas de conflit, les changements sont validés séquentiellement et des garanties strictes SERIALIZABLE sont obtenues. En cas de conflit, SQLITE_BUSY est renvoyé

HC-Tree

• Une autre branche expérimentale de SQLite, [HC-Tree], est un travail en cours visant à fournir un verrouillage optimiste au niveau ligne/tuple. L’un des résultats intéressants est qu’elle fournit un excellent ensemble de benchmarks montrant les gains de performance d’une telle conception par rapport à la branche BEGIN CONCURRENT

Et si l’on reprenait leur approche de benchmarking pour la lancer sur les options standard ?

Benchmarking

nUpdate=1, nScan=0

• Cette transaction en écriture seule montre clairement l’intérêt d’IMMEDIATE par rapport à DEFERRED. Le verrouillage intervient immédiatement et la transaction ne subit pas le coût d’une promotion
• CONCURRENT montre une hausse du débit quand le nombre de threads augmente et que les conflits se multiplient

nUpdate=10, nScan=0

• Comme on pouvait s’y attendre, le batch d’écritures aide beaucoup sur le nombre de lignes mises à jour avec 16 threads. CONCURRENT passe d’environ ~12k/sec à ~19k/sec
• IMMEDIATE vs DEFERRED devient moins important. Le coût des mises à jour elles-mêmes devient plus significatif que celui de la promotion de transaction

nUpdate=1, nScan=10

• Cette transaction devrait exposer la faiblesse du verrouillage CONCURRENT au niveau page à cause des lectures aléatoires
• Elle montre immédiatement pourquoi utiliser IMMEDIATE pour les transactions qui vont faire des mises à jour est plus important que le coût de promotion de DEFERRED
• Dans le cas de CONCURRENT, ces résultats sont très robustes parce que les conflits sous-jacents n’ont en réalité pas beaucoup augmenté

nUpdate=0, nScan=10

• Cette transaction batch en lecture seule montre l’impact du contrôle de concurrence pessimiste
• Elle montre pourquoi il ne faut pas définir IMMEDIATE par défaut pour toutes les transactions
• CONCURRENT vs IMMEDIATE indique un léger inconvénient à l’usage du mode CONCURRENT. « Les performances sont un peu moins bonnes dans tous les cas »
  • Mais CONCURRENT serait malgré tout une bonne option par défaut