MySQL vs SQLite : concurrence — pourquoi les écritures deviennent une falaise de trafic

La falaise de trafic : ce que vous observez réellement

« Falaise de trafic » décrit la sensation lorsqu’un système ne dégrade pas sa capacité de manière progressive. Vous augmentez la charge d’écritures de 10 % et soudainement vous obtenez une latence tail 10x plus élevée,
des timeouts, des retries, et une foule de clients qui retentent poliment la même action.

Dans SQLite, la falaise est généralement causée par la contention sur les verrous et une conception à écrivain unique. Il existe des nuances (le mode WAL change la donne, et le système de fichiers de l’OS compte),
mais l’idée principale reste : les écrivains concurrents s’accumulent derrière un verrou. À mesure que la file s’allonge, la latence explose. Ce n’est pas subtil.

Dans MySQL (en particulier InnoDB), la falaise existe toujours — mais vous disposez de plus de leviers. Le verrouillage au niveau des lignes, le MVCC, le flushing en arrière-plan, et un moteur conçu pour des connexions concurrentes
signifient que les écritures ont tendance à se dégrader plus progressivement, et vous obtenez plus de réglages et plus de façons de vous tirer une balle dans le pied.

Un modèle mental utile : SQLite est un pont très rapide et fiable à une seule voie avec une très bonne signalisation. MySQL est un échangeur autoroutier avec plusieurs bretelles, limitations de vitesse,
et un nombre surprenant de façons de provoquer un embouteillage si vous bloquez la voie de gauche avec un canapé.

Faits et histoire intéressants qui comptent en exploitation

• SQLite a commencé en 2000 comme base de données embarquée pour usage interne, conçue pour être petite, fiable et ne nécessiter aucune administration — c’est toujours sa superpuissance.
• SQLite est dans le domaine public, ce qui explique en grande partie sa diffusion : moins de soucis de licence, adoption plus facile « on publie et on voit ».
• La culture de tests de SQLite est extrême : elle est réputée pour des tests automatisés poussés, y compris l’injection de fautes et la simulation d’erreurs d’E/S — bonne nouvelle quand le disque ment.
• Le mode WAL est arrivé plus tard (milieu/fin des années 2000) pour améliorer la concurrence en lecture en séparant les lectures des écritures via un journal WAL.
• InnoDB est devenu le moteur par défaut de MySQL (après des années où MyISAM était courant), orientant le comportement « normal » vers les transactions et le verrouillage au niveau des lignes.
• InnoDB utilise MVCC (contrôle de concurrence multi-version) : les lecteurs ne bloquent pas les écrivains de la même manière, d’où la résilience des charges en lecture sous écritures.
• Le « écrivain unique » de SQLite est un choix de conception, pas une fonctionnalité manquante. Il simplifie la correction sur différentes plateformes et systèmes de fichiers.
• La sémantique du système de fichiers compte plus pour SQLite que ce que beaucoup imaginent. Les systèmes de fichiers réseau peuvent casser les hypothèses de verrouillage ou les rendre douloureusement lents.
• La durabilité de MySQL est configurable via les politiques de vidage du journal, ce qui signifie que vous pouvez choisir entre « sûr » et « rapide » et ensuite oublier ce que vous avez choisi.

Modèles de concurrence : SQLite vs MySQL en termes pratiques

SQLite : un fichier de base de données avec un protocole de verrouillage

SQLite est une bibliothèque qui lit et écrit un fichier de base de données unique (plus des fichiers annexes comme WAL et des index en mémoire partagée en mode WAL). Votre processus applicatif
la lie (directement ou via un wrapper), et les requêtes opèrent via des E/S de fichiers normales avec un mécanisme de coordination pour maintenir la cohérence du fichier.

L’histoire de la concurrence repose sur les verrous. En mode rollback-journal (l’ancien style par défaut), SQLite prend des verrous sur le fichier de base de données au fur et à mesure des états :
shared, reserved, pending, exclusive. Les détails sont précis, mais opérationnellement cela signifie ceci : à un moment, un écrivain a besoin d’un verrou exclusif pour valider, et pendant
que ce verrou est détenu, les autres écrivains sont bloqués — et, selon le mode et le timing, les lecteurs peuvent être bloqués aussi.

En mode WAL, c’est mieux pour les lectures : plusieurs lecteurs peuvent lire la base principale pendant qu’un écrivain ajoute des entrées dans le WAL. Mais les écritures restent sérialisées : un seul écrivain
peut valider dans le WAL à la fois. WAL vous donne de la concurrence en lecture et souvent de meilleures performances en charges mixtes, mais il ne transforme pas SQLite en moteur multi-écrivains.

MySQL InnoDB : un serveur avec un mécanisme transactionnel concurrent

MySQL est une base de données client/serveur. Vous vous connectez via un socket, et le serveur gère la concurrence à l’intérieur d’un moteur de stockage (InnoDB, typiquement).
InnoDB utilise des verrous au niveau des lignes, des transactions, des journaux undo, des journaux redo, un flush en arrière-plan, et des snapshots MVCC. Il est construit pour garder de nombreux clients en mouvement.

Les écritures se contendent toujours — sur des lignes chaudes, des pages d’index, des verrous auto-incrément (moins qu’avant), des verrous de métadonnées et le flush du journal redo.
Mais vous pouvez souvent répartir la douleur. Avec un bon schéma et un bon design de requêtes, les écrivains concurrents peuvent progresser tant qu’ils ne frappent pas les mêmes enregistrements.

SQLite demande : « Comment garder le fichier cohérent et portable ? » InnoDB demande : « Comment garder 500 clients satisfaits pendant que le disque est en feu ? »

Pourquoi les écritures en SQLite s’effondrent : explication par files d’attente

Parlons de pourquoi cela échoue soudainement plutôt que graduellement.

Quand vous avez une section critique unique (le verrou d’écrivain), vous avez construit une file. Tant que votre taux d’arrivée de transactions d’écriture est inférieur à votre débit de service
(la vitesse à laquelle l’écrivain unique peut terminer chaque transaction), tout semble normal. Au moment où vous approchez la saturation, la longueur de la file commence à croître rapidement.
Avec des arrivées aléatoires et des temps de transaction variables, la queue tail devient très vilaine rapidement.

Les écritures SQLite ont tendance à être « en rafales » à cause des frontières de transaction et du comportement de fsync. Beaucoup d’applications font de minuscules transactions :
insérer une ligne, commit ; mettre à jour une ligne, commit. Chaque commit est un événement de durabilité qui peut nécessiter une synchronisation. Cela ajoute une grande composante de temps de service en pics.
Soudain votre pont à une voie a un péage qui arrête parfois la circulation pour compter les pièces.

La falaise s’amplifie avec le comportement de retry des clients. Quand SQLite renvoie SQLITE_BUSY ou database is locked, les bibliothèques et applications retentent souvent.
Les retries vont bien quand la contention est brève. Sous une charge soutenue, cela devient de l’automutilation : vous augmentez le taux d’arrivée exactement quand le système est saturé.

Voici votre première blague : une base de données à écrivain unique en pointe de charge ressemble à une réunion à une personne où tout le monde coupe la parole — d’une façon ou d’une autre personne ne progresse pas, mais tout le monde repart fatigué.

Comment le WAL modifie la falaise (mais ne la supprime pas)

Le mode WAL permet aux lecteurs d’éviter d’être bloqués par un écrivain. C’est énorme pour les charges axées lecture : la falaise peut se déplacer vers la droite.
Mais l’écrivain reste unique. Pire, WAL introduit le checkpointing, qui est une forme de travail différé : vous ajoutez au WAL jusqu’à décider de checkpoint et de fusionner les changements
dans la base principale. Le checkpointing coûte des E/S et peut bloquer ou ralentir selon les paramètres et la pression.

Si vous avez de la malchance, vous avez échangé « chaque écriture bloque parfois les lectures » contre « les lectures sont fluides jusqu’à ce que le monstre du checkpoint se réveille ».
Cela peut ressembler à des pics périodiques de latence, ou à une falaise qui apparaît selon un calendrier.

Système de fichiers et pile de stockage : la dépendance cachée de SQLite

Les verrous de SQLite reposent sur la sémantique de verrouillage de fichiers de l’OS. Sur des disques locaux avec des systèmes de fichiers raisonnables, c’est prévisible. Sur certains systèmes de fichiers réseau, c’est soit cassé,
émulé de manière médiocre, soit extrêmement lent. Même lorsque c’est « supporté », la variation de latence peut transformer une contention mineure en une boucle d’attente constante.

MySQL se soucie aussi de la latence d’E/S, évidemment, mais son contrôle de concurrence n’est pas construit sur « chaque processus client se coordonne via des verrous de fichiers sur le même fichier ».
Il est construit autour d’un processus serveur dédié qui contrôle l’accès aux structures de stockage.

Pourquoi MySQL ne s’effondre généralement pas de la même façon

Le moteur InnoDB de MySQL a un profil de défaillance différent. Il peut absolument tomber en panne, mais le chemin typique n’est pas « un verrou d’écrivain bloque tout le monde ».
À la place, c’est un mélange de :

• Contention sur une ligne/page d’index chaude : de nombreux écrivains mettent à jour les mêmes lignes ou la même page de feuille d’index.
• Pression sur le journal des transactions : les flushs du redo log deviennent le goulot d’étranglement, surtout avec une durabilité stricte.
• Pool de pages et flushing : les pages dirty s’accumulent ; le flushing devient urgent ; le débit s’effondre si les E/S ne suivent pas.
• Attentes de verrous et deadlocks : pas un verrou d’écriture global, mais suffisamment d’attentes pour bloquer l’application.
• Tempêtes de connexions : trop de threads clients, changements de contexte, explosion mémoire.

La différence clé : si 50 clients écrivent sur 50 lignes différentes, InnoDB peut souvent le faire en parallèle. Si 50 clients écrivent sur la même ligne « compteur », ça se sérialise.
Mais vous pouvez repenser cela (sharder les compteurs, utiliser des tables append-only, faire des mises à jour en batch). Avec SQLite, repenser signifie souvent « réduire les écrivains ou mieux batcher ».

Exigence sur la citation, traitée avec précaution :
L’espoir n’est pas une stratégie — souvent attribué à la culture des opérations (idée paraphrasée).
Cela s’applique parfaitement ici : « nous n’aurons pas d’écritures concurrentes » n’est pas une architecture.

WAL, checkpoints et la « tempête d’écriture surprise »

Le mode WAL dans SQLite est souvent recommandé, et pour de bonnes raisons : il améliore la concurrence lecture/écriture en permettant aux lecteurs de lire la base stable pendant que les écritures vont dans le journal.
Mais WAL introduit un travail opérationnel qu’on ne peut pas ignorer : les checkpoints.

Un checkpoint fusionne le contenu du WAL dans le fichier de base principal. C’est coûteux en E/S. S’il ne suit pas, le WAL grossit. Si le WAL grossit, il peut ralentir les lectures
(parce que les lecteurs peuvent devoir consulter le WAL), augmenter l’usage disque, et allonger le temps de récupération après crash.

Sous une charge d’écriture régulière, vous pouvez finir avec des pics périodiques quand le checkpoint se déclenche. Sous une charge en rafales, vous pouvez obtenir des « tempêtes d’écriture »
où le système essaie de rattraper son retard, et soudain vos performances auparavant régulières deviennent en dents de scie.

Le motif de la falaise ressemble souvent à ceci :

• Charge faible/modérée : tout est rapide, le WAL est petit, les checkpoints sont peu coûteux.
• Approche de la saturation : les écrivains attendent plus, le WAL grossit, le coût du checkpoint augmente.
• Surexploitation : les écrivains s’empilent, le checkpoint concurrence la bande passante disque, les temps d’attente de verrous augmentent, les clients retentent, et vous appelez quelqu’un à 02:00.

Transactions, autocommit et le schéma mortel des petites écritures

La falaise d’écritures de SQLite est fréquemment auto-infligée via la forme des transactions.

Beaucoup d’applications tournent en mode autocommit : chaque INSERT/UPDATE est sa propre transaction. Cela signifie que chaque écriture paie le coût complet du « commit » : acquisition de verrou,
travail de journal/WAL, et comportement de synchronisation pour la durabilité. Si vous faites 1 000 mises à jour une par une, vous ne faites pas 1 000 mises à jour — vous faites 1 000 commits. Votre disque devient un métronome.

Groupez les écritures à l’intérieur de transactions explicites. Ce n’est pas optionnel. Si votre application ne peut pas faire cela, SQLite n’est pas l’outil adapté dès que la concurrence et le débit comptent.

MySQL bénéficie aussi du batching, mais il tolère mieux les petites transactions car il est conçu autour des commits concurrents, du group commit et des E/S en arrière-plan.
Vous souhaitez toujours une bonne hygiène transactionnelle, mais vous êtes moins susceptible de subir une falaise immédiate à partir d’un comportement multi-utilisateur « normal ».

Tâches pratiques : commandes, sorties et décisions (12+)

Voici les vérifications que j’exécute réellement quand quelqu’un dit « les écritures sont lentes » ou « SQLite est bloqué » ou « MySQL timeoute ». Chaque tâche inclut :
commande, ce que la sortie signifie, et la décision suivante.

Tâche 1 : Confirmer le journal mode de SQLite et les pragmas critiques

cr0x@server:~$ sqlite3 /var/lib/app/app.db 'PRAGMA journal_mode; PRAGMA synchronous; PRAGMA busy_timeout;'
wal
2
0

Sens : journal_mode est WAL. synchronous=2 est FULL (durable, plus lent). busy_timeout=0 signifie « échoue rapidement » en cas de contention.
Décision : Définir un busy timeout raisonnable (par ex. 2000–10000 ms) pour réduire les échecs immédiats, et revoir synchronous selon les besoins de durabilité.

Tâche 2 : Vérifier la taille du WAL et la pression de checkpoint

cr0x@server:~$ ls -lh /var/lib/app/app.db*
-rw-r----- 1 app app 1.2G Dec 30 01:12 /var/lib/app/app.db
-rw-r----- 1 app app 768M Dec 30 01:13 /var/lib/app/app.db-wal
-rw-r----- 1 app app  32K Dec 30 01:12 /var/lib/app/app.db-shm

Sens : Un WAL de 768M suggère que les checkpoints ne suivent pas ou ne tournent pas.
Décision : Examiner les paramètres de checkpoint et vérifier si l’application laisse des transactions de lecture longue durée ouvertes (ce qui peut empêcher les checkpoints).

Tâche 3 : Inspecter SQLite pour des transactions longue durée (vérification côté appli)

cr0x@server:~$ lsof /var/lib/app/app.db | head
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF     NODE NAME
app      2140  app   17u   REG  252,0 1291845632 1048577 /var/lib/app/app.db
app      2140  app   18u   REG  252,0  805306368 1048578 /var/lib/app/app.db-wal
app      2199  app   17u   REG  252,0 1291845632 1048577 /var/lib/app/app.db

Sens : Plusieurs processus/threads ont la DB ouverte ; c’est normal. Cela n’affiche pas les verrous directement, mais indique qui joue.
Décision : Si vous voyez des processus inattendus, arrêtez-les. Si de nombreux workers partagent une DB sur un stockage réseau, réévaluez l’architecture immédiatement.

Tâche 4 : Reproduction rapide de la contention avec busy timeouts

cr0x@server:~$ sqlite3 /var/lib/app/app.db 'PRAGMA busy_timeout=2000; BEGIN IMMEDIATE; SELECT 1;'
1

Sens : BEGIN IMMEDIATE tente d’obtenir tôt une réservation d’écriture. Si cela bloque ou renvoie BUSY rapidement, vous avez de la contention d’écrivants.
Décision : Si cela lutte systématiquement pendant les incidents, réduisez les écrivains concurrents ou batcher les écritures ; WAL seul ne vous sauvera pas.

Tâche 5 : Vérifier le type de système de fichiers (SQLite y tient plus que vous ne le pensez)

cr0x@server:~$ findmnt -no SOURCE,FSTYPE,OPTIONS /var/lib/app
/dev/nvme0n1p2 ext4 rw,relatime

Sens : ext4 local : bonne base. Si vous voyez nfs/cifs/fuse, considérez cela comme un risque majeur pour le verrouillage et la latence.
Décision : Déplacer SQLite sur du stockage local ou migrer vers une base serveur.

Tâche 6 : Mesurer la latence disque lors des pics d’écriture

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

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.12    0.00    2.10    9.80    0.00   81.98

Device            r/s     w/s   rKB/s   wKB/s  avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1         120.0   980.0   6400  51200      97.5     8.20    8.40    2.10    9.20   0.62  68.0

Sens : iowait est élevé et write await est ~9ms. Ce n’est pas catastrophique, mais si cela monte à des dizaines/centaines de ms, les commits se bloqueront et les files de verrous croîtront.
Décision : Si la latence du stockage coïncide avec les erreurs de verrou, priorisez les I/O : disque plus rapide, moins de sync, transactions plus grandes, moins d’écrivains.

Tâche 7 : Vérifier le moteur MySQL et la configuration de base

cr0x@server:~$ mysql -uroot -p -e "SHOW VARIABLES LIKE 'default_storage_engine'; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';"
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| default_storage_engine | InnoDB |
+------------------------+-------+
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

Sens : InnoDB avec flush-at-commit=1 est durable mais plus sensible à la latence de fsync.
Décision : Si vous pouvez tolérer de perdre jusqu’à ~1 seconde de commits en cas de crash, pensez à la valeur 2 ; sinon investissez dans un stockage basse latence et le group commit.

Tâche 8 : Vérifier MySQL pour attentes de verrous et contention

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2025-12-30 01:15:02 0x7f1c6c0d9700 INNODB MONITOR OUTPUT
=====================================
------------
TRANSACTIONS
------------
Trx id counter 5829101
Purge done for trx's n:o < 5829000 undo n:o < 0 state: running
History list length 2113
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 5829088, ACTIVE 12 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 118, OS thread handle 139759, query id 99102 app 10.0.0.24 updating
UPDATE counters SET value=value+1 WHERE id=1

Sens : Une transaction mettant à jour la même ligne de compteur est une contention classique sur une ligne chaude. Active depuis 12 secondes est un signal.
Décision : Corriger le schéma/chargement : sharder les compteurs, utiliser des incréments tamponnés, ou repenser pour éviter une seule ligne chaude.

Tâche 9 : Vérifier la liste des processus MySQL pour les accumulations

cr0x@server:~$ mysql -uroot -p -e "SHOW FULL PROCESSLIST;" | head
Id	User	Host	db	Command	Time	State	Info
118	app	10.0.0.24:51122	prod	Query	12	updating	UPDATE counters SET value=value+1 WHERE id=1
119	app	10.0.0.25:51140	prod	Query	11	Waiting for row lock	UPDATE counters SET value=value+1 WHERE id=1
120	app	10.0.0.26:51188	prod	Query	11	Waiting for row lock	UPDATE counters SET value=value+1 WHERE id=1

Sens : Beaucoup de sessions en attente pour un verrou de ligne : ce n’est pas un problème « MySQL est lent » ; c’est une contention par l’application.
Décision : Réduire la concurrence sur ce hotspot, changer le pattern de requête, ou passer à un modèle append-only avec agrégation.

Tâche 10 : Vérifier rapidement les deadlocks InnoDB

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | grep -n "LATEST DETECTED DEADLOCK" -A25
247:LATEST DETECTED DEADLOCK
248:------------------------
249:2025-12-30 01:10:44 0x7f1c6c1da700
250:*** (1) TRANSACTION:
251:TRANSACTION 5829051, ACTIVE 1 sec starting index read
252:mysql tables in use 1, locked 1
253:LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

Sens : Les deadlocks ne sont pas « mauvais », ce sont un signal de concurrence. InnoDB choisira une victime et la rollbackera.
Décision : Raccourcir les transactions et uniformiser l’ordre des verrous ; ajouter des index appropriés ; gérer les retries de deadlock côté application.

Tâche 11 : Observer le débit MySQL et la pression de flushing

cr0x@server:~$ mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty'; SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';"
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_dirty| 8421  |
+-------------------------------+-------+
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 187   |
+------------------+-------+

Sens : Les pages dirty indiquent des flushs en attente ; les log waits indiquent des transactions en attente d’espace dans le redo log.
Décision : Si les log waits augmentent, vous êtes limité par les E/S du redo log ou la taille ; pensez à ajuster la taille des fichiers de log et améliorer la latence du stockage.

Tâche 12 : Valider le nombre de connexions et le comportement des threads MySQL

cr0x@server:~$ mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 812   |
+-------------------+-------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

Sens : 812 connexions, c’est beaucoup sauf si vous l’aviez planifié. Les tempêtes de connexions peuvent ressembler à une « base lente » alors que c’est la planification des threads.
Décision : Utiliser du pooling de connexions, limiter la concurrence, et vérifier que l’application ne crée pas une connexion par requête.

Tâche 13 : Vérifier la version de compilation/exécution de SQLite (le comportement évolue)

cr0x@server:~$ sqlite3 --version
3.45.2 2024-03-12 11:06:23 df5c253c0b3dd24916e4ec7cf77d3db5294cc9fd3f24a94d9c0b6ec8d1c4a2c7

Sens : La version vous dit quels correctifs WAL et de verrouillage existent. Les vieilles versions peuvent avoir des comportements par défaut et des caractéristiques de performance différentes.
Décision : Si vous êtes sur un paquet ancien inclus dans une image OS, mettez à jour la bibliothèque/le runtime de l’application si possible.

Tâche 14 : Déterminer si vous êtes accidentellement sur un système de fichiers réseau

cr0x@server:~$ mount | grep -E ' /var/lib/app |nfs|cifs|fuse' | head
/dev/nvme0n1p2 on /var/lib/app type ext4 (rw,relatime)

Sens : Montage local. Bien. Si vous voyiez NFS/CIFS/FUSE ici, vous auriez une action immédiate à mener.
Décision : Si c’est distant, arrêtez-vous et réarchitectez : disque local pour SQLite ou migrer vers MySQL/Postgres.

Tâche 15 : Repérer le « meurtre par autocommit » dans les logs applicatifs (vérification de pattern)

cr0x@server:~$ grep -E "SQLITE_BUSY|database is locked|BEGIN|COMMIT" /var/log/app/app.log | tail -n 8
2025-12-30T01:12:10Z db=sqlite msg="BEGIN"
2025-12-30T01:12:10Z db=sqlite msg="COMMIT"
2025-12-30T01:12:10Z db=sqlite err="database is locked"
2025-12-30T01:12:10Z db=sqlite msg="BEGIN"
2025-12-30T01:12:10Z db=sqlite msg="COMMIT"

Sens : Paires BEGIN/COMMIT par opération et erreurs de verrou immédiates : vous faites de minuscules transactions et vous vous heurtez.
Décision : Grouper les écritures dans des transactions explicites ; appliquer du backpressure ; réduire la concurrence des workers.

Feuille de diagnostic rapide

Quand les performances d’écriture s’effondrent, vous n’avez pas le temps de philosopher sur l’ACID. Vous avez besoin d’un chemin de décision rapide.
Voici l’ordre qui trouve habituellement le goulet rapidement.

Premièrement : identifier le type de verrou et où la contention a lieu

• SQLite : Voyez-vous SQLITE_BUSY / database is locked ? Vérifiez PRAGMA busy_timeout, la taille du WAL, et si vous avez plusieurs écrivains.
• MySQL : Les requêtes sont-elles « Waiting for row lock » ou « Waiting for table metadata lock » ? Utilisez SHOW FULL PROCESSLIST et SHOW ENGINE INNODB STATUS.

Deuxièmement : vérifier la latence E/S et les réglages de durabilité

• Exécutez iostat -xz. Si write await monte, les commits vont se bloquer.
• SQLite : vérifier PRAGMA synchronous. MySQL : vérifier innodb_flush_log_at_trx_commit.

Troisièmement : confirmer la forme des transactions et les limites de concurrence

• Cherchez les boucles autocommit : beaucoup de petites transactions.
• Vérifiez les tailles des pools de workers, la concurrence des requêtes et le comportement de retry.
• Dans MySQL, vérifiez le nombre de connexions et l’existence d’un pool.

Quatrièmement : décider « tuner » vs « repenser »

• Si c’est SQLite et que vous avez besoin de multiples écrivains concurrents : assumez refonte/migration.
• Si c’est MySQL et que vous avez une ligne/index chaud : repensez les patterns de requêtes et le modèle de données ; le tuning seul ne battra pas la physique.
• Si c’est I/O : achetez de la latence (meilleur stockage), réduisez la fréquence de sync en sécurité, ou réduisez le volume d’écriture.

Erreurs courantes : symptômes → cause racine → correctif

1) « database is locked » apparaît seulement en pointe de trafic

Symptômes : Erreurs de verrou éparses, retries, timeouts, et saut soudain de la latence tail.

Cause racine : Contention sur le verrou d’écrivain et mise en file ; trop d’écrivains concurrents ; busy_timeout trop bas ou nul.

Correctif : Grouper les écritures dans des transactions explicites ; définir un busy_timeout sensé ; réduire la concurrence des écrivains ; passer à MySQL si le multi-écrivain est requis.

2) Le fichier WAL grossit indéfiniment

Symptômes : Le .db-wal devient gros ; l’utilisation disque augmente ; pics périodiques de latence.

Cause racine : Les checkpoints ne tournent pas ou sont bloqués par des lecteurs longue durée ; paramètres de checkpoint inadaptés à la charge.

Correctif : S’assurer que les lecteurs ne gardent pas de transactions ouvertes ; planifier/déclencher les checkpoints ; envisager des transactions de lecture plus courtes ou l’usage de snapshots.

3) « SQLite est rapide sur mon laptop » mais lent en conteneurs

Symptômes : Très bonnes perf en dev ; production montre des timeouts de verrous et des stalls d’E/S.

Cause racine : Stockage différent : systèmes de fichiers overlay, volumes réseau, IOPS throttlés, ou latence fsync plus élevée.

Correctif : Mettre SQLite sur un stockage persistant local et basse latence ; mesurer le coût de fsync ; ou migrer vers MySQL où le serveur peut absorber mieux la variabilité.

4) Pics de latence écriture MySQL sans verrous évidents

Symptômes : Les requêtes « s’exécutent » mais prennent beaucoup plus de temps ; CPU modéré ; disque occupé.

Cause racine : Pression sur le flush du redo log ou flush agressif des pages dirty ; latence du stockage.

Correctif : Améliorer la latence disque ; tuner la taille du redo log ; vérifier les politiques de flush ; réduire la fréquence des commits via le batching.

5) MySQL est « down » mais le vrai problème ce sont les connexions

Symptômes : Erreurs de trop de connexions ; dégradation des temps de réponse partout.

Cause racine : Comportement connexion-par-requête, mauvaise configuration des pools, ou tempêtes de retry.

Correctif : Faire appliquer le pooling, plafonner la concurrence, et appliquer du backpressure ; surveiller Threads_connected et la profondeur de la file côté appli.

6) « On a ajouté un index et les écritures ont empiré »

Symptômes : Les lectures s’améliorent, mais le débit d’insert/update chute ; le temps de verrou augmente.

Cause racine : La maintenance d’index supplémentaires augmente l’amplification d’écriture et la contention (surtout sur des clés monotones).

Correctif : Garder les index au minimum ; envisager les index composés avec parcimonie ; éviter les index redondants ; utiliser des clés favorables à l’append si possible.

Trois mini-récits d’entreprise tirés du terrain

Mini-récit 1 : L’incident causé par une mauvaise hypothèse

Une équipe produit de taille moyenne a construit un job runner qui traitait des événements d’une file de messages. Chaque worker écrivait une ligne d’état dans SQLite : temps d’enqueue, temps de début, temps de fin,
et quelques compteurs. Ça fonctionnait très bien en staging. Ça a bien tourné le premier mois en production.

L’hypothèse était silencieuse et mortelle : « Les workers sont indépendants, donc les écritures DB sont indépendantes. » Elles ne l’étaient pas. Tous les workers écrivaient dans le même fichier, sur le même
volume, et chaque job produisait plusieurs petites transactions parce que l’ORM était en mode autocommit.

Le trafic de pointe est arrivé (prévisible, en même temps qu’une campagne marketing). Les workers sont passés de quelques-uns à des dizaines. Soudain la longueur de la file a cru plus vite que le traitement.
Les workers ont retenté sur erreurs de verrou, ce qui a augmenté les tentatives d’écriture. Le CPU restait bas. Le service semblait « OK » si vous ne regardiez que le CPU.

L’indice était le log : un mur de database is locked et des transactions minuscules. La solution n’a pas demandé d’actions héroïques : réduire la concurrence des écrivains, grouper les écritures par job
dans une transaction, et déplacer le stockage d’état vers MySQL pour une durabilité multi-écrivains. Le reste de la semaine a été passé à expliquer à la direction pourquoi « une DB embarquée rapide » ne signifie pas
« une base de données partagée pour écritures ».

Mini-récit 2 : L’optimisation qui s’est retournée contre eux

Une autre équipe voulait « moins de sauts réseau » et a déplacé une fonctionnalité write-heavy de MySQL vers SQLite embarqué dans un service API. Leur logique était séduisante :
pas de réseau, pas d’overhead TCP, pas de serveur, pas de pools de connexions. Juste un fichier. Les tests de performance semblaient excellents — sur une instance.

Puis la fonctionnalité est devenue populaire. Ils ont scalé l’API horizontalement, parce que c’est ce qu’on fait avec un service stateless. Chaque instance pointait vers le même
volume partagé (un système de fichiers réseau managé) pour « partager la base ». C’est là que l’optimisme a rencontré la réalité du système de fichiers.

Le système n’a pas juste ralenti. Il a développé une personnalité : pauses occasionnelles, timeouts de verrous, et comportements étranges où un déploiement « le réparait » pendant une heure.
Les fichiers WAL ont grossi, le checkpointing est devenu erratique, et la sémantique de verrouillage du FS réseau a introduit de la variance de latence. Le débogage était misérable car chaque instance était techniquement « saine », mais bloquée.

Ils ont annulé le changement. Pas parce que SQLite est mauvais, mais parce qu’ils ont essayé de l’utiliser comme une base partagée multi-nœuds. L’« optimisation » a supprimé un saut réseau et l’a remplacé
par un verrou distribué et une latence de stockage imprévisible. On ne peut pas ruser avec la physique ; il faut choisir quelle part de la physique on paie.

Mini-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Une équipe liée aux paiements utilisait MySQL pour les charges transactionnelles et SQLite sur des appareils edge pour le cache local. Ils avaient une règle : aucun fichier SQLite ne devait avoir plus d’un écrivain actif
par groupe de processus, et chaque chemin d’écriture avait une transaction explicite et une file bornée.

C’était ennuyeux. Cela demandait des revues de code posant des questions agaçantes comme « Quelle est votre durée maximale de transaction ? » et « Où appliquez-vous du backpressure ? »
Ils avaient aussi des tests de charge synthétiques qui modélisaient les pics d’écriture et forçaient la contention en pré-prod.

Un jour, une nouvelle fonctionnalité a accidentellement introduit un pattern write-on-read : un endpoint de lecture mettait à jour un champ « last_seen » à chaque requête. Dans MySQL, cela aurait été bruyant mais survivable ; sur les caches SQLite en edge, ça a transformé des lectures en écritures et menaçait de tout sérialiser.

Les garde-fous ont fait leur travail. La file bornée s’est remplie, le service s’est dégradé de façon prévisible (certaines mises à jour ont été abandonnées, les lectures sont restées rapides), et des alertes ont déclenché avant que les appareils ne deviennent inutilisables. Ils ont reverté le changement et remplacé « write last_seen » par une mise à jour batch périodique. Personne n’a été réveillé à 02:00. Voilà ce que « ennuyeux » vous achète.

Listes de contrôle / plan pas à pas

Si vous insistez pour faire tourner SQLite sous charge concurrente

1. Activez WAL sauf raison spécifique de ne pas le faire. Vérifiez avec PRAGMA journal_mode;.
2. Batcher les écritures : encapsulez plusieurs instructions dans une transaction explicite. Mesurez le taux de commit, pas le taux de requêtes.
3. Définir busy_timeout pour obtenir une attente contrôlée plutôt que des échecs immédiats ; puis limiter les retries côté application.
4. Faire respecter un écrivain unique via une queue (en processus ou via un service écrivain dédié). Plusieurs écrivains n’est pas « parallèle », c’est « contention ».
5. Garder les transactions courtes ; ne pas garder des lectures ouvertes pendant une logique applicative lente.
6. Placer la base sur un stockage local. Éviter les systèmes de fichiers réseau pour des écritures partagées.
7. Surveiller WAL et le comportement des checkpoints : alerter sur la croissance du WAL et les stalls de checkpoint.
8. Tester la charge avec une concurrence réaliste, incluant retries et timeouts, pas seulement des benchmarks mono-thread.

Si vous choisissez MySQL pour la concurrence

1. Utiliser InnoDB (oui, ça vaut encore la peine de le dire) et confirmer que les réglages de durabilité correspondent aux besoins métier.
2. Éviter les points chauds : éviter les compteurs sur une seule ligne ; éviter les verrous globaux déguisés en « tables de statut ».
3. Indexer avec responsabilité : chaque index est un coût en écriture ; ne payez que les taxes nécessaires.
4. Pooler les connexions et plafonner la concurrence ; ne laissez pas chaque requête ouvrir une nouvelle session.
5. Surveiller les attentes de verrous et les deadlocks et les traiter comme un retour d’information sur le design transactionnel.
6. Tester sous rafales d’écriture, pas seulement en régime stable. Beaucoup de pannes surviennent quand les buffers se remplissent et que le flushing devient urgent.

Checklist de déclenchement de migration : quand SQLite n’est pas le bon choix

• Vous avez besoin de plusieurs écrivains concurrents à travers threads/processus et ne pouvez pas les sérialiser sans nuire à la latence.
• Vous avez besoin d’un scale horizontal avec état partagé entre nœuds.
• Vous dépendez d’un stockage en réseau pour le fichier de base.
• Vous ne pouvez pas batcher les écritures parce que le produit exige des commits immédiats à haut débit.
• Vous avez besoin de changements de schéma en ligne et d’outils opérationnels qui attendent une base serveur.

Deuxième blague : SQLite est un outil fantastique, mais l’utiliser comme base d’écritures partagée, c’est comme utiliser un tournevis comme burin — ça marche jusqu’à ce que ce soit une anecdote que vous racontez en onboarding.

FAQ

1) SQLite est-il vraiment à écrivain unique ?

Pratiquement, oui : un seul écrivain peut valider à la fois. WAL améliore la concurrence en lecture, pas le débit multi-écrivains.
Vous pouvez avoir plusieurs connexions qui soumettent des écritures, mais elles se sérialisent sur le verrou d’écrivain.

2) Le mode WAL rend-il SQLite sûr pour une forte concurrence ?

Il le rend plus sûr pour la concurrence orientée lecture et les charges mixtes, car les lecteurs ne bloquent pas un écrivain de la même façon.
Il n’enlève pas le goulot d’écrivain unique ; il déplace souvent le point de douleur ou change sa forme (pics de checkpoint).

3) Pourquoi les écritures s’effondrent au lieu de se dégrader lentement ?

Parce que vous satuez une ressource sérialisée. Théorie des files : à mesure que l’utilisation approche 100 %, les temps d’attente explosent.
Ajoutez des retries et des temps de commit variables, et l’explosion devient spectaculaire.

4) Puis-je résoudre la concurrence SQLite en ajoutant plus de CPU ?

Généralement non. Le goulot est la sérialisation des verrous et la latence fsync/E/S, pas le CPU. Un stockage plus rapide et moins de commits aident plus que des cœurs supplémentaires.

5) « database is locked » est-ce toujours mauvais ?

C’est un signal. Quelques réponses busy lors de rafales peuvent être acceptables si vous avez du backpressure et des retries bornés.
Des erreurs de verrou persistantes signifient que votre taux d’arrivée d’écritures dépasse ce que l’écrivain unique peut servir, ou que les transactions sont trop longues.

6) Pourquoi MySQL gère-t-il mieux les écritures concurrentes ?

InnoDB supporte les verrous au niveau des lignes et le MVCC, donc les écritures indépendantes peuvent avancer en parallèle si elles ne touchent pas les mêmes lignes/pages d’index.
Il a toujours des goulots, mais ils sont souvent localisés et re-concevables.

7) Quand MySQL se comporte-t-il encore comme une « falaise de trafic » ?

Lignes chaudes (compteurs uniques), gros index secondaires sous insert intense, pression sur le redo log, ou tempêtes de connexions.
MySQL peut se dégrader progressivement, mais il peut aussi tomber d’une falaise si vous créez un hotspot et que vous ajoutez des retries.

8) Dois-je réduire la durabilité pour améliorer le débit d’écriture ?

Parfois, mais faites-le délibérément. PRAGMA synchronous pour SQLite et innodb_flush_log_at_trx_commit pour MySQL sont des décisions métier.
Si vous ne pouvez pas tolérer la perte de données, ne vous « optimisez » pas hors d’un cadre de conformité.

9) Puis-je exécuter SQLite sur NFS si je fais attention ?

Vous pouvez essayer, et vous pouvez avoir de la chance pendant un moment. Mais la sémantique des verrous et la variance de latence en font un pari à haut risque.
Si vous avez besoin d’écritures partagées entre nœuds, préférez un serveur de base de données.

10) Quel est le schéma le plus simple et sûr pour SQLite en production ?

Un écrivain unique (queue), mode WAL, transactions batchées, disque local, transactions de lecture courtes, et retries bornés avec backpressure.
Si cela ressemble à la construction d’un serveur de base de données, vous apprenez la bonne leçon.

Prochaines étapes réalisables cette semaine

Si vous êtes déjà sur SQLite et que vous sentez la falaise :

• Mesurer : confirmer journal mode, synchronous, busy_timeout ; inspecter la taille du WAL ; corréler les erreurs de verrou avec la latence disque.
• Rectifier la forme : grouper les écritures dans des transactions explicites ; plafonner la concurrence des écrivains ; ajouter du backpressure ; arrêter les retries infinis.
• Stabiliser : assurer un stockage local ; empêcher les transactions de lecture longue durée ; surveiller le comportement des checkpoints.
• Décider : si vous avez besoin de concurrence multi-écrivains entre processus/nœuds, planifier une migration vers MySQL (ou une autre base serveur) au lieu de « tuner plus fort ».

Si vous hésitez entre MySQL et SQLite pour un nouveau système :

• Utilisez SQLite quand vous voulez la simplicité embarquée, principalement des lectures, une concurrence d’écriture bornée et une durabilité locale.
• Utilisez MySQL quand vous avez besoin de nombreux écrivains concurrents, d’un état central partagé entre instances applicatives, d’outils opérationnels et d’un scaling prévisible sous charge.

La falaise n’est pas mystérieuse. C’est un contrat que vous avez signé accidentellement : « un écrivain à la fois ». Si ce contrat correspond à votre charge, SQLite est brillant.
Si ce n’est pas le cas, la falaise n’est pas un bug. C’est la facture.