SQLite : diagnostiquer « database is locked » sans augmenter le timeout au hasard
L’erreur « database is locked » ne signifie pas que SQLite est cassé. Elle indique généralement qu’une opération ne peut pas obtenir le verrou dont elle a besoin au moment prévu. Augmenter immédiatement le timeout peut rendre l’incident moins visible, mais ne raccourcit aucune transaction et ne corrige ni un curseur oublié, ni deux écrivains qui se disputent la base, ni un fichier placé sur un stockage inadapté.
Réponse courte : quoi vérifier en premier
- Relever le code SQLite exact :
SQLITE_BUSY,SQLITE_LOCKEDou un code étendu. - Identifier l’opération, la connexion et la durée de la transaction qui détient le verrou.
- Vérifier que chaque curseur, statement et transaction est bien finalisé, validé ou annulé.
- Réduire la durée passée entre
BEGINetCOMMIT: aucun appel réseau ni traitement lent dans cette zone. - N’ajouter un
busy_timeoutque pour absorber une contention brève et attendue.
SQLITE_BUSY et SQLITE_LOCKED ne racontent pas la même histoire
Le message affiché par un framework masque parfois le code natif. Or ce code oriente immédiatement l’enquête.
| Code | Conflit probable | Piste prioritaire |
|---|---|---|
SQLITE_BUSY | Une autre connexion, souvent dans un autre thread ou processus, utilise déjà le verrou incompatible. | Transactions longues, plusieurs workers écrivains, checkpoint WAL, reprise après incident. |
SQLITE_LOCKED | La même connexion, ou une connexion utilisant un cache partagé, maintient une opération incompatible. | Statement non finalisé, curseur encore ouvert, modification de schéma pendant une lecture. |
SQLite autorise plusieurs transactions de lecture simultanées, mais une seule transaction d’écriture à la fois. Cette contrainte reste vraie en mode WAL. WAL améliore la coexistence des lecteurs et de l’écrivain ; il ne transforme pas SQLite en moteur multi-écrivains.
1. Instrumenter l’erreur avant de la corriger
Une trace utile contient au minimum l’horodatage, le code primaire et le code étendu, l’opération SQL, l’identifiant de connexion, le début de la transaction et le nombre de tentatives. Sans cela, « database is locked » reste une observation trop vague.
Ajoutez aussi deux durées : le temps total de la transaction et le temps passé à attendre un verrou. Si une transaction reste ouverte pendant plusieurs secondes alors que ses requêtes SQL ne durent que quelques millisecondes, le blocage se situe probablement dans le code applicatif entre les requêtes.
2. Chercher les transactions qui restent ouvertes
Le cas classique est une transaction qui englobe trop de travail : requête HTTP, calcul, lecture d’un gros fichier, attente utilisateur ou traitement d’une file. Pendant ce temps, le verrou d’écriture reste réservé à une connexion qui ne travaille même plus avec la base.
BEGIN IMMEDIATE;
-- lectures strictement nécessaires
-- écritures courtes et déterministes
COMMIT;Préparez les données avant BEGIN, écrivez-les dans une transaction courte, puis effectuez les appels externes après COMMIT. En cas d’exception, le chemin d’erreur doit toujours exécuter un rollback ou fermer la connexion selon le contrat du pilote.
Il faut également fermer ou finaliser les statements. La documentation SQLite précise qu’une transaction implicite se termine lorsque son dernier statement actif est terminé. Un curseur encore ouvert ou un accès BLOB incrémental non fermé peut donc prolonger une transaction bien au-delà de la requête visible dans les logs.
3. Comprendre DEFERRED, IMMEDIATE et EXCLUSIVE
BEGIN utilise par défaut le mode DEFERRED. La transaction ne prend pas immédiatement un verrou d’écriture. Si elle commence par un SELECT, elle démarre comme transaction de lecture, puis tente de passer en écriture au premier INSERT, UPDATE ou DELETE. Cette montée en privilège peut échouer avec SQLITE_BUSY.
BEGIN IMMEDIATE demande le droit d’écrire dès le début. Il peut échouer immédiatement si un autre écrivain est actif, ce qui facilite souvent une stratégie de nouvelle tentative propre : l’échec survient avant que la transaction ait effectué son travail. En revanche, l’utiliser partout sans mesurer la contention peut réserver inutilement le rôle d’écrivain.
BEGIN EXCLUSIVE n’est pas un remède générique. En mode WAL, son comportement est identique à IMMEDIATE. Dans les autres modes de journalisation, il bloque aussi les autres lectures pendant la transaction.
4. Ce que busy_timeout règle — et ce qu’il masque
Le pragma suivant demande à une connexion d’attendre jusqu’à une limite donnée avant de renvoyer SQLITE_BUSY :
PRAGMA busy_timeout = 5000;La valeur est exprimée en millisecondes et s’applique à la connexion. Selon le pilote, une option équivalente peut être exposée dans l’API. Un délai de quelques secondes est pertinent pour une collision courte entre deux écritures normales. Il est insuffisant comme correction si une transaction reste ouverte indéfiniment ou si la charge produit en permanence plus d’écritures que l’unique écrivain ne peut en absorber.
La bonne stratégie de retry doit être bornée, inclure un léger backoff et ne rejouer que des opérations idempotentes ou protégées contre les doublons. Relancer aveuglément une création de commande ou un débit sans clé d’idempotence peut produire un incident métier bien plus grave que le verrou initial.
5. Activer WAL pour la bonne raison
PRAGMA journal_mode = WAL;En mode WAL, les modifications sont ajoutées au fichier -wal. Les lecteurs peuvent continuer à consulter leur instantané pendant qu’un écrivain valide ses changements. C’est souvent un gain important pour une application mêlant beaucoup de lectures et des écritures courtes.
Mais trois limites restent essentielles :
- il ne peut toujours y avoir qu’un seul écrivain simultané ;
- WAL utilise aussi un fichier
-shmet suppose que les processus partagent la même machine ; - des lecteurs très longs peuvent empêcher un checkpoint d’avancer complètement et laisser le WAL grossir.
Ne supprimez jamais à la main les fichiers -wal, -shm ou -journal pour « débloquer » la base. Ils participent au mécanisme de cohérence et de récupération. Arrêtez proprement les processus, sauvegardez l’ensemble cohérent des fichiers et utilisez les interfaces SQLite prévues pour le checkpoint ou la récupération.
6. Vérifier le stockage et le déploiement
Un fichier SQLite doit idéalement rester sur un stockage local dont les verrous sont correctement pris en charge. La documentation déconseille les systèmes de fichiers réseau pour ce scénario, et WAL exige que tous les processus accédant à la base soient sur le même hôte.
Dans un déploiement conteneurisé, vérifiez donc la nature réelle du volume, le nombre de réplicas qui écrivent et la machine qui héberge chacun d’eux. Plusieurs conteneurs sur plusieurs nœuds pointant vers le même fichier ne constituent pas une architecture SQLite sûre. Si plusieurs hôtes doivent écrire simultanément, un serveur de base de données comme PostgreSQL correspond mieux au besoin.
7. Une procédure de diagnostic reproductible
- Reproduire le problème sur une copie ou un environnement de test représentatif.
- Journaliser le code SQLite exact et les durées de transaction.
- Réduire temporairement le nombre de workers écrivains pour voir si l’erreur disparaît.
- Tracer tous les
BEGIN,COMMITetROLLBACKavec l’identifiant de connexion. - Vérifier les curseurs, statements et accès BLOB non fermés.
- Mesurer avant et après avoir raccourci la section transactionnelle.
- Tester ensuite un timeout borné, puis WAL si le profil lectures/écritures le justifie.
- Surveiller le taux de
SQLITE_BUSY, la latence des écritures et la taille du WAL après le changement.
Questions fréquentes
WAL supprime-t-il les erreurs « database is locked » ?
Non. WAL permet aux lecteurs et à l’écrivain de mieux cohabiter, mais SQLite conserve un seul écrivain à la fois. Deux transactions d’écriture concurrentes peuvent toujours produire SQLITE_BUSY.
Faut-il utiliser BEGIN IMMEDIATE partout ?
Non. Il est utile lorsqu’une transaction doit écrire et que l’application préfère échouer ou attendre dès le départ. Une transaction purement en lecture n’en a pas besoin, et une réservation trop précoce peut réduire la concurrence entre écritures.
Quand faut-il quitter SQLite ?
Lorsque plusieurs machines doivent écrire dans la même base, que les écritures longues sont structurelles ou que le débit exige plusieurs écrivains réellement simultanés. Le bon seuil dépend des mesures de l’application, pas d’un nombre universel de requêtes par seconde.
Le timeout est un amortisseur, pas un diagnostic
Une correction durable commence par la durée et la propriété du verrou. Raccourcir les transactions, finaliser systématiquement les statements, limiter les écrivains et choisir le bon mode de journalisation résout la cause. Le timeout vient ensuite pour absorber les collisions normales qui restent.
Si le verrouillage apparaît dans une automatisation ou un petit outil métier, FuretSplick peut aider à diagnostiquer le flux et fiabiliser son exécution, sans remplacer un problème d’architecture par une valeur de timeout arbitraire.