Verrouillage MariaDB vs SQLite : comment éviter les erreurs « Busy »

Procédure de diagnostic rapide

Quand vous êtes de garde, vous ne voulez pas un cours de philosophie. Vous voulez « que vérifier en premier pour arrêter l’hémorragie ? » Voici la séquence de triage que j’utilise pour les erreurs « busy » SQLite et la contention des verrous MariaDB.

Première étape : confirmer si vous êtes bloqué par des verrous ou coincé sur l’I/O

• SQLite : « busy » est souvent de la contention de verrous, mais peut aussi être « l’écrivain ne peut pas checkpoint », qui ressemble à de la contention et se ressent comme de la latence.
• MariaDB : les attentes de verrou vs les blocages disque peuvent se comporter de façon identique au niveau applicatif (les requêtes expirent). Il faut séparer « threads en attente de verrous » de « threads en attente du stockage ».

Deuxième étape : trouver la transaction qui tient la porte fermée

• SQLite : localisez les transactions de longue durée, les connexions ouvertes, ou les jobs en arrière-plan qui écrivent pendant que l’appli lit.
• MariaDB : identifiez le thread/transaction bloquant et le graphe d’attente (InnoDB status et processlist).

Troisième étape : corriger la portée et la concurrence avant d’ajuster les timeouts

Les timeouts sont un pansement. Parfois le bon pansement. Mais ce reste un pansement.

• Raccourcissez les transactions.
• Réduisez la fréquence des écritures ou batchifiez intelligemment.
• Ajoutez l’index adéquat pour éviter l’amplification des verrous.
• Ce n’est qu’ensuite : ajustez busy_timeout (SQLite) ou innodb_lock_wait_timeout (MariaDB), et ajoutez une logique de retry avec jitter.

Une règle fiable : si votre « correctif » est « augmenter le timeout », vous choisissez juste combien de temps cela mettra avant d’échouer.

Modèles de verrouillage : ce qui se verrouille réellement, et quand

SQLite : un écrivain à la fois, et c’est sérieux

SQLite est une base embarquée. C’est une bibliothèque liée à votre processus, écrivant dans un fichier. Ce fichier est la ressource partagée. Quand vous voyez « busy », SQLite vous dit qu’il ne peut pas acquérir le verrou dont il a besoin sur ce fichier (ou sur les fichiers de coordination WAL/shared-memory associés au mode WAL).

La réalité opérationnelle fondamentale :

• Beaucoup de lecteurs peuvent coexister.
• Un seul écrivain peut valider à la fois. Même si plusieurs connexions font la queue, une seule devient « l’écrivain ».
• Les transactions définissent la durée du verrou. Une « petite mise à jour » dans une transaction qui reste ouverte pendant des secondes devient « un verrou tenu pendant des secondes ».

SQLite a plusieurs modes de journalisation. Deux importent dans les discussions de production :

• Rollback journal (l’ancien défaut dans de nombreux modèles mentaux) : les écrivains peuvent bloquer les lecteurs plus facilement selon l’état des verrous, et le checkpointing est plus simple mais peut être perturbateur sous concurrence.
• WAL (Write-Ahead Logging) : les lecteurs ne bloquent généralement pas les écrivains et vice versa, mais vous obtenez un nouveau mode d’échec : le fichier WAL grandit jusqu’à ce qu’il puisse être checkpointé, et le checkpointing peut lui-même devenir source de contention.

La concurrence SQLite n’est pas « mauvaise ». Elle est explicite. Elle vous force à être honnête au sujet des schémas d’écriture. C’est une fonctionnalité… jusqu’au moment où vous feignez que c’est un serveur OLTP en réseau.

MariaDB (InnoDB) : verrous de ligne, gap locks et mise en file discrète

MariaDB avec InnoDB est une base client-serveur classique. Elle a un buffer pool, un gestionnaire de verrous, des threads en arrière-plan, et beaucoup de code dédié à permettre à de nombreuses sessions de travailler en concurrence sans se marcher sur les pieds.

La réalité opérationnelle ici :

• Plusieurs écrivains peuvent s’exécuter concurremment tant qu’ils ne touchent pas les mêmes lignes (ou plages, grâce aux next-key locks).
• Les attentes de verrous sont souvent silencieuses. Votre requête s’exécute, puis bloque. Le client voit « c’est lent », pas « c’est busy ».
• Un mauvais index provoque une amplification des verrous. Un index manquant peut transformer une opération destinée à une ligne en un scan de plage qui verrouille tout le quartier.

InnoDB a aussi des deadlocks, ce qui n’est pas l’apocalypse — InnoDB les détecte et choisit un participant à annuler. Le problème vient quand votre application traite les deadlocks comme « ça ne devrait jamais arriver » et répond par « panique, retry immédiatement, répéter indéfiniment ».

Idée paraphrasée (pas une citation mot à mot) : Werner Vogels a maintes fois poussé le principe de concevoir pour l’échec et de construire des systèmes qui supposent que des composants vont mal se comporter. Les verrous sont l’un de ces composants.

Faits intéressants et courte histoire à utiliser en réunion

1. SQLite est plus ancien que beaucoup de stacks web « modernes ». Le développement a commencé en 2000 comme moteur SQL autonome pour usage embarqué.
2. Le design « serverless » de SQLite est littéral. Il n’y a pas de démon. Chaque processus est son propre client de base de données et, à temps partiel, un « serveur » de base de données, se disputant des verrous de fichier.
3. Le mode WAL a été introduit pour améliorer la concurrence en séparant lectures et écritures via un journal append-only, mais il introduit des dynamiques de checkpoint qu’il faut gérer.
4. InnoDB n’était pas à l’origine « le moteur par défaut de MySQL ». Il est devenu la référence car il apportait transactions, récupération après crash et verrous au niveau ligne que MyISAM n’offrait pas.
5. MariaDB est un fork créé après l’acquisition de Sun par Oracle, principalement pour garder MySQL ouvert et piloté par la communauté.
6. « database is locked » dans SQLite est souvent un bug applicatif, comme une transaction laissée ouverte à travers des appels réseau ou une connexion qui fuit dans un pool.
7. Les gap et next-key locks d’InnoDB existent pour prévenir des phantoms sous certains niveaux d’isolation ; ils surprennent les ingénieurs qui supposent que seuls « les rows que j’ai touchées » sont verrouillées.
8. SQLite utilise des primitives de verrouillage POSIX/Win32, ce qui signifie que NFS et d’autres systèmes de fichiers réseau peuvent transformer le verrouillage de déterministe à « excitant ».
9. SQLite est utilisé plus largement que la plupart ne l’imaginent : navigateurs, OS mobiles, et d’innombrables applis desktop — car l’empreinte opérationnelle est minimale et la fiabilité élevée quand utilisé comme prévu.

Blague #1 : SQLite, c’est comme un pont à une voie — sûr, simple, et tout le monde traverse, mais un seul camion à la fois.

Ce que « busy » signifie vraiment (et ce que ça ne signifie pas)

SQLITE_BUSY signifie que SQLite dit : « J’ai essayé d’obtenir le verrou dont j’ai besoin, je n’ai pas pu, et je ne vais pas attendre indéfiniment sauf si vous me l’avez demandé. » Le comportement par défaut dans beaucoup de bindings est effectivement « ne pas attendre ». C’est pourquoi les erreurs « busy » apparaissent dès que la concurrence augmente d’un petit montant.

Les trois coupables habituels

1. Une transaction d’écriture de longue durée. Un job en arrière-plan commence une transaction, écrit beaucoup de lignes, et prend son temps. Tout le monde subit des « busy ».
2. Pression de checkpoint sur le WAL. Le fichier WAL grandit. À terme, le checkpoint nécessite la coopération des lecteurs. Un lecteur long peut empêcher l’avancement du checkpoint.
3. Plusieurs processus sur un système de fichiers qui ment sur les verrous. Les systèmes de fichiers réseau et les drivers de volumes pour conteneurs peuvent transformer un usage correct de SQLite en échecs aléatoires.

Ce que ce n’est pas

• Ce n’est pas principalement « SQLite est lent ». SQLite peut être extrêmement rapide sur un SSD local, notamment pour les charges de lectures.
• Ce n’est pas réparé par « ajoutez juste des retries » si vos transactions sont longues et votre concurrence soutenue. Vous allez juste construire une tempête de retries.
• Ce n’est pas quelque chose que vous devriez étouffer. Traitez « busy » comme un signal que votre modèle de concurrence est inapproprié.

Contention MariaDB/InnoDB : le cousin plus discret et sournois

MariaDB lance rarement un « busy » en plein visage. Il vous laisse faire la queue. Ça semble plus agréable, jusqu’à ce que vos threads applicatifs s’empilent comme des avions en attente. Votre p95 de latence grimpe en flèche. Ensuite votre pool de connexions sature. Puis vos clients commencent à retry. Et maintenant vous avez inventé votre propre déni de service distribué.

Comment la douleur de verrou MariaDB se manifeste généralement

• Requêtes lentes sans utilisation CPU (threads « Sending data » ou « Waiting for row lock »).
• Timeouts d’attente de verrou (finalement) et deadlocks (parfois, mais par rafales).
• Lag de réplication parce qu’un thread SQL sur la réplique est bloqué derrière une grosse transaction ou une attente de verrou.

Causes racines courantes

• Transactions trop grandes (mises à jour en masse, changements de schéma, ou code applicatif qui « par commodité » wrappe tout dans une transaction).
• Indexes manquants qui transforment des updates ciblés en scans de plage et verrouillent largement.
• Surprises du niveau d’isolation qui introduisent des gap locks et bloquent des inserts dans des « espaces vides ».
• Lignes chaudes (compteurs, « last_seen », classements) qui causent de la contention d’écriture quel que soit le bon comportement de la base.

Blague #2 : Les deadlocks InnoDB sont comme des réunions de bureau — quelqu’un doit partir plus tôt pour que ça avance.

Tâches pratiques : commandes, sorties et décisions

Voici les tâches que j’exécute réellement quand je diagnostique de la contention de verrous. Chacune inclut : une commande à exécuter, ce que la sortie signifie, et la décision suivante.

Tâches SQLite (comportement DB fichier local)

Task 1: Confirm journal mode and whether WAL is enabled

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA journal_mode;"
wal

Signification : WAL est activé. En cas général, les lecteurs ne devraient pas bloquer les écrivains, mais le checkpointing devient un élément à gérer.

Décision : Si vous n’êtes pas en WAL et que vous avez de la concurrence, basculez en WAL sauf si vous êtes sur un système de fichiers avec des verrous peu fiables ou si vous avez des contraintes nécessitant le rollback journal.

Task 2: Check busy timeout configured (SQLite side)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA busy_timeout;"
0

Signification : SQLite échouera immédiatement avec SQLITE_BUSY s’il ne peut pas obtenir le verrou.

Décision : Définissez un busy timeout raisonnable (souvent 2000–10000 ms) et corrigez la portée des transactions. Le timeout seul n’est pas une stratégie.

Task 3: Inspect WAL autocheckpoint threshold

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA wal_autocheckpoint;"
1000

Signification : SQLite essayera de checkpointer après ~1000 pages dans le WAL (dépend de la taille de page).

Décision : Si vous voyez la croissance du WAL et des blocages de checkpoint, ajustez ceci et ajoutez une routine de checkpoint contrôlée en période de faible trafic.

Task 4: Check if the WAL file is growing (symptom of checkpoint not keeping up)

cr0x@server:~$ ls -lh /var/lib/myapp/app.db*
-rw-r----- 1 myapp myapp  64M Dec 30 11:40 /var/lib/myapp/app.db
-rw-r----- 1 myapp myapp 512M Dec 30 11:41 /var/lib/myapp/app.db-wal
-rw-r----- 1 myapp myapp  32K Dec 30 11:41 /var/lib/myapp/app.db-shm

Signification : Le WAL est énorme par rapport au fichier DB de base ; le checkpointing n’aboutit probablement pas.

Décision : Cherchez des lecteurs de longue durée et envisagez un checkpoint manuel, raccourcissez les transactions de lecture, ou déplacez les lectures analytiques lourdes ailleurs.

Task 5: Find processes holding the database files open

cr0x@server:~$ sudo lsof /var/lib/myapp/app.db | head
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF   NODE NAME
myapp    2134 myapp   12u   REG  253,0  67108864 91234 /var/lib/myapp/app.db
myapp    2134 myapp   13u   REG  253,0 536870912 91235 /var/lib/myapp/app.db-wal
worker   2201 myapp   10u   REG  253,0  67108864 91234 /var/lib/myapp/app.db

Signification : Deux processus ont le DB ouvert ; c’est normal, mais vous savez maintenant à qui demander des comptes quand il y a des verrous.

Décision : Si vous voyez des processus inattendus (scripts de backup, cron, « juste un rapport rapide »), arrêtez-les ou redirigez-les vers une réplique/export.

Task 6: Confirm the filesystem type (SQLite on NFS is a recurring tragedy)

cr0x@server:~$ findmnt -no FSTYPE,TARGET /var/lib/myapp
nfs4 /var/lib/myapp

Signification : Votre DB SQLite vit sur NFS. Les sémantiques de verrouillage peuvent être peu fiables ou lentes ; les erreurs busy peuvent être aléatoires.

Décision : Déplacez la DB sur un stockage local, ou migrez la charge vers MariaDB/Postgres. SQLite sur NFS est un « marche tant que ça marche ».

Task 7: Measure whether “busy” correlates with slow disk flushes

cr0x@server:~$ iostat -x 1 3
Linux 6.8.0 (server)  12/30/2025  _x86_64_  (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.10    0.00    1.20   22.50    0.00   73.20

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1          5.0   420.0   200.0  5200.0   48.2   1.1   92.0

Signification : Fort %iowait, await élevé, %util élevé. Les écrivains peuvent tenir les verrous plus longtemps parce que fsync est lent.

Décision : Réparez d’abord le stockage (saturation disque, voisin bruyant, throttling) avant de réécrire la logique des transactions. Les verrous ne se libèrent pas tant que les écritures ne sont pas complètes.

Task 8: Check SQLite compile options (sometimes relevant in embedded distros)

cr0x@server:~$ sqlite3 -cmd ".compile_options" ":memory:" | head
COMPILER=gcc-13.2.0
ENABLE_FTS5
ENABLE_RTREE
THREADSAFE=1
USE_URI

Signification : Thread safety activée ; bien. Dans de rares cas, des builds atypiques peuvent changer le comportement de verrouillage.

Décision : Si vous êtes sur une build embarquée étrange, standardisez le paquet SQLite entre environnements pour réduire les mystères « ça arrive seulement en prod ».

MariaDB tasks (InnoDB locking and waiting)

Task 9: See who is running and who is waiting

cr0x@server:~$ mysql -uroot -p -e "SHOW FULL PROCESSLIST\G" | sed -n '1,60p'
*************************** 1. row ***************************
     Id: 12431
   User: app
   Host: 10.0.2.15:53320
     db: mydb
Command: Query
   Time: 28
  State: Waiting for row lock
   Info: UPDATE accounts SET balance=balance-10 WHERE id=42
*************************** 2. row ***************************
     Id: 12405
   User: app
   Host: 10.0.2.14:53112
     db: mydb
Command: Query
   Time: 61
  State: Updating
   Info: UPDATE accounts SET balance=balance+10 WHERE id=42

Signification : Une session est bloquée en attente d’un verrou de ligne tandis qu’une autre met à jour la même ligne.

Décision : Trouvez le bloqueur et raccourcissez cette transaction ; envisagez aussi des changements de conception pour les lignes chaudes (sharding des compteurs, batching, ou utilisation de primitives atomiques).

Task 10: Check InnoDB engine status for lock waits and deadlocks

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 987654, ACTIVE 3 sec starting index read
...
*** (2) TRANSACTION:
TRANSACTION 987655, ACTIVE 3 sec updating or deleting
...
WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 987700
History list length 1240

Signification : Un deadlock s’est produit ; InnoDB a choisi une victime. Notez aussi la history list length (purge lag) qui peut indiquer des transactions longues.

Décision : Assurez-vous que l’appli retry les transactions deadlockées avec backoff ; réduisez aussi la taille des transactions et assurez un ordre de verrouillage cohérent dans le code.

Task 11: Verify lock wait timeout and whether it matches reality

cr0x@server:~$ mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

Signification : Les sessions peuvent attendre jusqu’à 50 secondes avant d’échouer une attente de verrou.

Décision : Si vos timeouts en amont sont 5–10s, un wait de 50s bloque juste des threads. Diminuez-le pour l’aligner sur le budget de latence de bout en bout, mais seulement après avoir identifié le pattern bloquant.

Task 12: Check transaction isolation level (gap locks surprise people)

cr0x@server:~$ mysql -uroot -p -e "SHOW VARIABLES LIKE 'transaction_isolation';"
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

Signification : L’isolation par défaut est REPEATABLE-READ, qui peut utiliser des next-key locks et bloquer des inserts dans des plages.

Décision : Si vous voyez de la contention d’inserts sur des plages et que vous pouvez le tolérer, envisagez READ-COMMITTED pour les workloads OLTP — après tests pour la correction.

Task 13: Identify missing indexes that cause broad locking

cr0x@server:~$ mysql -uroot -p -e "EXPLAIN UPDATE orders SET status='paid' WHERE customer_id=123 AND status='pending'\G"
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 842113
     filtered: 10.00
        Extra: Using where

Signification : Scan de table complet (type: ALL) sur un update. Cela peut verrouiller bien plus que prévu et prendre une éternité.

Décision : Ajoutez un index composite (par ex. (customer_id, status)) et revérifiez le plan. C’est l’un des correctifs de verrouillage à ROI le plus élevé.

Task 14: Monitor InnoDB metrics for lock waits (quick signal)

cr0x@server:~$ mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';"
+-------------------------------+----------+
| Variable_name                 | Value    |
+-------------------------------+----------+
| Innodb_row_lock_current_waits | 12       |
| Innodb_row_lock_time          | 184223   |
| Innodb_row_lock_time_avg      | 15351    |
| Innodb_row_lock_time_max      | 60000    |
| Innodb_row_lock_waits         | 48       |
+-------------------------------+----------+

Signification : Des attentes de verrous ont lieu maintenant (current_waits) et ont été coûteuses en moyenne.

Décision : Passez à une analyse des requêtes/transactions. Si current_waits reste élevé pendant les incidents, vous avez une vraie contention, pas un incident isolé.

Task 15: Check whether the server is thread-saturated due to waits

cr0x@server:~$ mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running'; SHOW GLOBAL STATUS LIKE 'Max_used_connections';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 187   |
+-----------------+-------+
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+

Signification : Beaucoup de threads sont en exécution (souvent « running » inclut des threads en attente). Les connexions ont été fortement sollicitées.

Décision : Si l’appli retry agressivement, limitez le débit des retries et ajoutez du jitter immédiatement. Puis identifiez et corrigez la requête bloquante.

Task 16: OS-level confirmation: are we CPU-bound or waiting?

cr0x@server:~$ top -b -n 1 | sed -n '1,12p'
top - 11:44:12 up 17 days,  3:28,  1 user,  load average: 22.15, 20.97, 18.44
Tasks: 312 total,   5 running, 307 sleeping,   0 stopped,   0 zombie
%Cpu(s):  4.3 us,  1.1 sy,  0.0 ni, 72.8 id, 21.7 wa,  0.0 hi,  0.1 si,  0.0 st
MiB Mem :  32112.0 total,   1120.3 free,  10234.8 used,  20756.9 buff/cache
MiB Swap:   2048.0 total,   1980.0 free,     68.0 used.  18940.2 avail Mem

Signification : Fort IO wait (wa). Cela allonge souvent la durée de détention des verrous parce que les commits attendent fsync.

Décision : Investiguer la latence du stockage. Le tuning des verrous ne résoudra pas les écritures durables lentes.

Trois mini-histoires du monde de l’entreprise (anonymisées, douloureusement plausibles)

Incident causé par une mauvaise hypothèse : « SQLite ira bien ; on n’écrit que peu »

Dans une entreprise de taille moyenne, une équipe a livré un service interne « carnet ops ». Il stockait des notes d’incident, des runbooks et un petit audit log. Quelqu’un a choisi SQLite parce que c’était un binaire, pas d’infra supplémentaire, et les écritures étaient « minuscules ». Il tournait sur une VM avec un montage de système de fichiers partagé pour que deux instances applicatives accèdent au même fichier DB.

La mauvaise hypothèse n’était pas « SQLite ne peut pas gérer des écritures. » SQLite peut gérer beaucoup d’écritures. La mauvaise hypothèse était de penser que le système de fichiers et le modèle de processus n’avaient pas d’importance. En faible charge, le service semblait parfait. En charge d’incident — quand plusieurs ingénieurs modifiaient des notes et que l’audit log s’emballait — des requêtes ont commencé à échouer avec database is locked. Naturellement, ça s’est produit pendant des incidents, quand l’outil était le plus nécessaire.

Le premier correctif a été le classique : ajouter des retries. Cela a transformé « certaines requêtes échouent rapidement » en « requêtes qui bloquent, puis échouent ». Le pool de connexions s’est rempli. La latence est devenue non linéaire. Les ingénieurs ont commencé à copier-coller des notes dans le chat parce que le carnet expirait. La productivité a chuté. Le postmortem a inclus la phrase « l’outillage est devenu un point unique de défaillance », ce qui est une manière polie de dire « nous avons construit un piège ».

La vraie correction a été ennuyeuse mais correcte : déplacer le fichier DB sur le disque local et exécuter une seule instance écrivant (ou basculer vers MariaDB). Ils ont aussi modifié l’audit log pour batcher les écritures et raccourcir les transactions dans le code applicatif. Les erreurs de verrou ont chuté à près de zéro et, plus important, les rares erreurs restantes étaient des signaux exploitables plutôt que du bruit de fond.

Optimisation qui a mal tourné : WAL partout, pour toujours, avec un lecteur sans fin

Une autre entreprise avait une DB SQLite locale embarquée dans un agent edge. Ils ont activé WAL partout pour améliorer la concurrence : l’agent lisait la config tout en écrivant des métriques. Les benchmarks initiaux semblaient meilleurs. Tout le monde s’est félicité. Puis des mois plus tard, des devices sur le terrain ont commencé à manquer d’espace disque de façon étrange.

Ce n’était pas le fichier DB de base. C’était le fichier WAL. Il continuait de croître, car le checkpoint ne pouvait pas se compléter de façon fiable. L’agent avait un thread « watcher » qui gardait une transaction de lecture ouverte tout en streamant des changements vers un autre composant. Cette transaction de lecture empêchait le recyclage des anciennes frames WAL. Le WAL est devenu une fuite de disque au ralenti.

Les opérations ont remarqué des devices ralentis. Les alarmes d’usage disque se sont déclenchées. Certains devices ont atteint 100% de disque et ont commencé à tomber dans des sous-systèmes non liés. Le débogage a été pénible car l’agent lui-même était « ok » jusqu’à ce qu’il ne le soit plus. Quand il a franchi le seuil, tout a planté en même temps.

Le correctif : repenser le watcher pour éviter de maintenir une transaction ouverte, et checkpoint explicite pendant les périodes d’inactivité. Ils ont aussi défini des limites raisonnables pour la croissance du WAL via une politique (alerter sur la taille du WAL par rapport à la DB). WAL n’était pas l’ennemi. WAL non borné plus un lecteur infini l’était.

Pratique ennuyeuse mais correcte qui a sauvé la mise : bornes transactionnelles cohérentes et backoff

Une équipe proche des paiements utilisait MariaDB. Ils avaient des pics de verrous périodiques, mais les incidents étaient rares. La raison n’était pas la chance. Ils avaient une norme interne : chaque chemin d’écriture avait une portée transactionnelle maximale, pas d’appels réseau dans les transactions, et toutes les erreurs DB retryables utilisaient un backoff exponentiel avec jitter.

Un jour, un job de réconciliation a été déployé avec une update par cross-join par erreur. Il a commencé à verrouiller plus de lignes que prévu. Dans beaucoup d’organisations, cela devient un thundering herd : les retries applicatifs martèlent la DB, la DB devient non réactive, et l’incident prend de l’ampleur.

Ici, le job a quand même causé des douleurs, mais l’impact a été contenu. L’appli a reculé au lieu de s’empiler. D’autres services ont dégradé progressivement. Les ingénieurs ont eu le temps d’identifier la requête, la tuer et déployer un correctif. Les actions postmortem étaient simples : ajouter un garde-fou, améliorer la revue des requêtes pour les jobs batch.

C’est la vérité peu sexy de la fiabilité : les pratiques qui semblent lentes en développement sont celles qui empêchent la production de se transformer en performance artistique.

Comment prévenir les erreurs busy (SQLite) et les accumulations de verrous (MariaDB)

Choisir le moteur adapté à la topologie d’écriture

Si vous avez un seul processus, disque local et concurrence modérée : SQLite peut être excellent. Si vous avez plusieurs instances applicatives, plusieurs hôtes et des « écritures depuis partout » : SQLite devient un problème de coordination que vous n’aviez pas l’intention d’adopter.

• SQLite est un excellent choix pour les charges embarquées, single-node, sur disque local ; systèmes à lecture-majoritaire ; caches ; files durables avec écrivains contrôlés.
• MariaDB est un excellent choix quand vous avez besoin de vrais écrivains concurrents, d’accès distant, d’outillage opérationnel et d’un comportement prévisible sous multi-clients.

SQLite : tactiques concrètes qui fonctionnent vraiment

• Activer WAL pour lecture/écriture concurrente (la plupart du temps). Puis surveiller la croissance du WAL et le comportement de checkpoint.
• Définir busy_timeout et/ou un busy handler pour que la contention courte ne devienne pas une erreur. Alignez-le sur votre budget de requête (ne mettez pas 60s et prétendez que c’est bien).
• Garder les transactions courtes. Ne gardez pas une transaction ouverte pendant des appels réseau, du parsing JSON, ou l’attente d’un utilisateur.
• Batcher les écritures, sans créer des « méga-transactions ». Batcher en petits commits (par ex. centaines ou milliers) plutôt qu’une transaction infinie.
• Utiliser un pattern un seul écrivain si possible. Un thread/process dédié qui sérialise les écritures peut éliminer la contention et simplifier les retries.
• Éviter d’exécuter des fichiers DB SQLite sur des systèmes de fichiers réseau. Si vous devez, testez le verrouillage sous charge. La plupart des équipes découvrent la vérité trop tard.
• Être explicite sur le checkpointing pour les services longue durée avec lecteurs persistants. Envisagez PRAGMA wal_checkpoint(TRUNCATE); périodique en période de faible trafic, mais testez soigneusement.

MariaDB/InnoDB : tactiques concrètes qui fonctionnent vraiment

• Corriger les index d’abord. La plupart des « problèmes de verrou » sont en fait « on scanne trop et donc on verrouille trop ».
• Réduire la portée des transactions. Committez plus tôt. Divisez les grosses mises à jour. Évitez les patterns « read-modify-write » sur les lignes chaudes.
• Concevoir pour éviter les lignes chaudes. Les compteurs et champs « last seen » peuvent être écrits par de nombreux threads. Utilisez compteurs sharded, tables append-only, ou agrégation périodique.
• Rendre le comportement de retry civilisé. Les deadlocks arrivent. Votre appli doit retry avec jitter et un nombre borné de tentatives.
• Aligner les timeouts de lock avec les timeouts en amont. Si votre API timeoute à 8s, laisser des sessions DB attendre 50s, c’est juste du hoarding de ressources.
• Être prudent avec les changements d’isolation. Passer à READ-COMMITTED peut réduire la contention, mais validez la correction (phantoms, lectures répétées) pour votre workload.

Erreurs courantes : symptômes → cause racine → correction

1) Pics « database is locked » SQLite après ajout d’un job en arrière-plan

Symptômes : Erreurs busy corrélées à l’exécution d’un cron/worker ; logs applicatifs montrent des échecs même à faible trafic.

Cause racine : Le job ouvre une transaction et effectue beaucoup d’écritures (ou la garde ouverte pendant d’autres travaux), bloquant les autres écrivains.

Correction : Committez en plus petits morceaux ; déplacer le job vers une file single-writer ; ajouter busy_timeout ; s’assurer que le job ne s’exécute pas concurrentiellement.

2) Le fichier WAL croît sans limite

Symptômes : app.db-wal continue de grossir ; l’usage disque augmente ; stalls occasionnels lors des tentatives de checkpoint.

Cause racine : Une transaction de lecture de longue durée empêche le checkpoint de recycler les frames WAL.

Correction : Éviter les longues transactions de lecture ; changer le lecteur pour utiliser des snapshots plus petits ; planifier des checkpoints ; vérifier que le code ne garde pas un curseur ouvert pendant de longues opérations.

3) « busy » SQLite se produit surtout sur certains hôtes

Symptômes : Même workload, comportement de verrou différent selon l’hôte ou le conteneur.

Cause racine : Fichier DB sur un système de fichiers différent (quirks d’overlayfs, NFS, dispositif bloc réseau), ou latence de stockage provoquant des durées de verrou plus longues.

Correction : Standardiser l’emplacement et le système de fichiers ; déplacer sur SSD local ; mesurer la latence fsync ; si vous avez besoin d’accès multi-hôte, migrez vers MariaDB.

4) Timeouts d’attente de verrou MariaDB lors de mises à jour « petites »

Symptômes : Lock wait timeout exceeded lors de la mise à jour d’une seule ligne ; récurrent mais intermittent.

Cause racine : Contention sur une ligne chaude (la même ligne mise à jour par de nombreuses sessions) ou une transaction qui touche la ligne puis garde le verrou pendant d’autres opérations.

Correction : Reconcevoir les écritures sur lignes chaudes (sharder, append-only, cache puis agrégation) ; réduire la durée des transactions ; éviter les appels externes dans les transactions.

5) MariaDB soudainement « lente partout » après un changement d’index

Symptômes : Attentes de verrou accrues ; lag de réplication ; CPU pas saturé, mais latence élevée.

Cause racine : Régression du plan de requête provoquant des scans larges et plus de verrous, ou DDL en ligne causant de la contention metadata sur un schéma occupé.

Correction : Valider les plans avec EXPLAIN avant et après ; staged index changes ; s’assurer que les requêtes utilisent les indexes prévus ; throttler les changements de schéma.

6) Les retries aggravent tout

Symptômes : Après ajout de retries, les incidents durent plus longtemps ; la DB voit plus de QPS pendant les événements de verrou.

Cause racine : Les retries immédiats créent une herd. Tous les clients tentent de nouveau en même temps, étendant la fenêtre de contention.

Correction : Backoff exponentiel + jitter ; limiter les retries ; échouer rapidement pour les opérations non idempotentes sauf si conçues pour ça.

Listes de contrôle / plan étape par étape

Plan A : Vous êtes sur SQLite et vous voulez moins d’erreurs « busy » cette semaine

1. Vérifiez que le stockage est local et stable. Si c’est NFS/remote/overlay étrange, priorisez le déplacement. Les erreurs busy là-bas sont un mode de vie.
2. Activez WAL (si approprié). Confirmez avec PRAGMA journal_mode;.
3. Définissez un busy timeout raisonnable. Commencez avec 2000–5000 ms pour les chemins interactifs ; ajustez selon le budget de requête.
4. Auditez la portée des transactions. Assurez-vous qu’aucune transaction ne s’étende sur des appels réseau, des sleeps ou des « boucles de traitement ».
5. Identifiez vos écrivains. Listez les processus avec lsof ; assurez-vous de ne pas avoir d’écrivains surprises.
6. Surveillez la taille du WAL vs la DB. Alertez si le WAL dépasse un ratio que vous jugez dangereux pour votre budget disque.
7. Introduisez un pattern single-writer pour les chemins à forte écriture. Queuez les écritures en-process ou via IPC ; sérialisez les commits.
8. Ajoutez des retries bornés avec jitter. Seulement pour les opérations retryables ; logguez le nombre et la durée des retries pour ne pas masquer les problèmes.

Plan B : Vous êtes sur MariaDB et les attentes de verrous tuent le p95

1. Capturer la requête bloquante. Processlist + InnoDB status pendant l’incident.
2. Vérifier les indexes manquants sur les chemins d’update chauds avec EXPLAIN.
3. Réduire la durée des transactions. Confirmer qu’aucun chemin code ne garde une transaction ouverte pendant du travail non-DB.
4. Aligner les timeouts. Réglez innodb_lock_wait_timeout sur une valeur qui correspond aux budgets et évite le hoarding de threads.
5. Corriger les retries. Assurez-vous que deadlocks/timeouts de lock sont retriés avec backoff exponentiel et jitter. Capper les retries.
6. Concevoir pour éviter les lignes chaudes. Si une ligne est une ressource contestée, l’échelle ne la résoudra pas. Changez le modèle de données.
7. Valider la latence du stockage. Si fsync est lent, les verrous durent plus longtemps ; vous blamerez le gestionnaire de verrous pour un problème de stockage.

Checklist de décision : quand passer de SQLite à MariaDB

• Vous avez besoin de plusieurs instances applicatives écrivant concurremment et vous ne pouvez ou ne voulez pas funneler les écritures via un seul écrivain.
• Vous avez besoin d’accès distant depuis plusieurs hôtes au même SGBD.
• Vous avez besoin de fonctionnalités opérationnelles : privilèges fins, réplication intégrée, changements de schéma en ligne, monitoring mature des verrous.
• Vous êtes en train de « résoudre » les erreurs busy avec des timeouts plus longs et plus de retries.

FAQ

1) Est-ce que « database is locked » dans SQLite est toujours un bug ?

C’est généralement un mauvais choix de conception ou un bug de portée de transaction. Parfois c’est une contention brève légitime qui nécessite un busy timeout. Si c’est fréquent, traitez-le comme un problème de conception du système.

2) Dois-je activer WAL par défaut ?

Pour la plupart des charges locales disque en lecture/écriture concurrente : oui. Si vous êtes sur un système de fichiers avec des sémantiques de verrou douteuses (courant avec le stockage réseau), testez soigneusement ou évitez WAL.

3) Si je règle busy_timeout, ai-je fini ?

Non. Vous avez juste décidé d’attendre au lieu d’échouer rapidement. Si l’écrivain tient le verrou 5s et que votre timeout est 2s, vous échouez toujours. Si vous le mettez à 60s, votre appli bloquera plus longtemps.

4) Pourquoi WAL rend parfois les choses pires ?

WAL améliore la concurrence lecteur/écrivain, mais il introduit le comportement de checkpoint. Les lecteurs longue durée peuvent bloquer le checkpointing, laissant le WAL grandir et causant parfois des stalls.

5) Est-ce que SQLite peut gérer plusieurs écrivains ?

Plusieurs connexions peuvent tenter d’écrire, mais une seule peut commit à la fois. Sous une écriture concurrente soutenue, vous aurez de la mise en file et des erreurs busy sauf si vous coordonnez les écrivains.

6) Dans MariaDB, pourquoi des inserts sont bloqués alors que personne ne touche les mêmes lignes ?

Les gap/next-key locks sous REPEATABLE-READ peuvent verrouiller des plages, pas seulement des lignes individuelles. Des indexes manquants peuvent aussi élargir les scans et verrouiller des plages inattendues.

7) Dois-je baisser innodb_lock_wait_timeout pour éviter l’accumulation ?

Souvent oui — après avoir confirmé la charge de travail. Un timeout plus bas empêche le hoarding de threads et force un échec plus rapide, ce qui peut protéger le serveur. Mais si vous le baissez sans corriger le bloqueur, vous échangerez latence contre erreurs.

8) Quelle est la bonne stratégie de retry pour les timeouts de verrous et les deadlocks ?

Retryez les deadlocks et timeouts de verrou avec backoff exponentiel et jitter, retries bornés, et en tenant compte de l’idempotence. Ne jamais retryer instantanément en boucle serrée. C’est comme ça qu’on transforme un petit événement de verrou en panne.

9) Puis-je exécuter SQLite sur Kubernetes avec un volume partagé ?

Vous pouvez, mais « pouvez » n’est pas « devriez ». Si ce volume partagé est réseau, le verrouillage et la latence peuvent être imprévisibles. Si vous avez besoin d’écrivains multi-pod, utilisez MariaDB (ou une autre BD client-serveur) ou imposez une architecture single-writer.

10) Comment savoir si je suis limité par des verrous ou par l’I/O ?

Recherchez un fort IO wait (top, iostat) et des commits longs, vs de nombreuses sessions bloquées en états d’attente de verrou (processlist, InnoDB status). Souvent c’est les deux : l’I/O lente allonge la durée de détention des verrous, ce qui augmente la contention.

Conclusion : prochaines étapes qui réduisent réellement le bruit du pager

Si vous ne retenez qu’une chose : les erreurs de verrou vous disent généralement la vérité sur les ressources partagées. SQLite vous le dit rapidement et franchement. MariaDB vous laisse faire semblant plus longtemps.

Faites ceci ensuite :

1. Exécutez la procédure de diagnostic rapide et décidez si vous êtes limité par des verrous ou par l’I/O.
2. Trouvez la transaction la plus longue et raccourcissez-la. Cela résout plus d’incidents que n’importe quel réglage.
3. Stoppez les tempêtes de retries avec backoff + jitter et un plafond strict.
4. Pour SQLite : WAL + busy_timeout raisonnable + écrivains contrôlés + attention au checkpointing.
5. Pour MariaDB : correction des indexes, bornes transactionnelles, redesign des lignes chaudes, et timeouts alignés sur de vrais budgets de latence.

Puis choisissez la base qui correspond à votre réalité de concurrence, pas à votre organigramme. Votre vous futur appréciera la nouveauté de dormir toute la nuit.