SQLite : gare aux verrous

La gestion des verrous dans SQLite est d’une étrange bizarreté. Lorsque vous faites une opération DML ou DDL en écriture (UPDATE, DELETE, INSERT mais aussi CREATE, DROP, ALTER), vous posez un verrou sur la database, c’est-à-dire sur le fichier contenant l’ensemble des tables. Il n’y a donc plus aucune possibilité de modifier, de supprimer, d’insérer des données de manière concurrente sur les tables de la base. 

Autrement dit, en cas de modification en masse des lignes d’une table d’une base (d’un fichier sqlite), vous devrez attendre la fin des opérations pour pouvoir procéder à des modifications sur cette table ou sur une autre table de la base au niveau d’un accès ou d’une opération concurrente. Ou bien, vous obtiendrez le message :

Error: database is locked

Remarquez qu’en mode MyISAM ou Aria 1.5, MySQL ou MariaDB posent également un verrou exclusif sur table. Seul, le choix du moteur InnoDB permet les accès concurrents en écriture. 

Seule bonne nouvelle : les modifications n’empêchent pas aux autres utilisateurs de consulter – via des SELECT – le contenu des tables en cours de modification.

SQLite : gare aux verrous

Base de données  / Formateur SQL Formateur Sqlite InnoDB MariaDB MySQL Oracle Database PostgreSQL SQL SQL Server SQLite 

Commentaires

Bonjour, et merci pour ce billet.
Est-ce réellement une surprise lorsque l’on sait que SQLite génère un fichier plat qui est lu et écrit ? En quoi est-ce que cela pourrait s’avérer être un facteur limitant ? Je suppose que le seul souci que cela peut présenter, c’est au niveau d’une application massivement utilisée, comme un forum. J’essaie de comprendre dans quelle mesure cela peut gêner en fait…
Il me semble que d’autres outils et/ou logiciels apposent également un verrou d’écriture lorsqu’un fichier est ouvert en mode édition, justement pour éviter que celui-ci soit écrasé par une source externe.

@Norore

Ca gêne à partir du moment où il y a des process qui tentent des écritures concurrentielles, ce qui est le principe même d’une base de données transactionnelle et relationnelle comme PostgreSQL, SQL Server, Oracle, Firebird ou MariaDB/MySQL en mode InnoDB.

J’ai quelques scripts qui requête des bases SQLite en SELECT uniquement et j’ai aussi ce genre de message Error: database is locked.
Une idée de pourquoi ?

Merci

@Nomis

Je viens d’expliquer pourquoi SQLite ne permet pas les écritures concurrentielles ! Le SELECT est compatible avec UPDATE, DELETE ou INSERT, CREATE, ALTER et DROP.

@Denis Szalkowski
Oui mais justement, je fais uniquement des SELECT alors que vous avez écris : Seule bonne nouvelle : les modifications n’empêchent pas aux autres utilisateurs de consulter – via des SELECT – le contenu des tables en cours de modification.

Hors je ne peux pas consulter via des selects les tables en cours de modification.
Y a t’il un paramètre à spécifier a SQLite ?

@Nomis

Vous vous connectez de quelle manière à SQLite ? Vous êtes en quelle version ? Pour ma part, mes tests ont été réalisés en SQLite 3.23.1 2018-04-10 17:39:29.

@Denis Szalkowski

J’utilise un script shell qui appel la commande sqlite3 :
/usr/bin/sqlite3 /path/to/sqlite/db -cmd « .timeout 1000 » « SELECT idroute, fromchannel_id, tochannel_id FROM routes WHERE idroute LIKE \ »toto\ » »

J’ai rajouté le timeout pour avoir moins de problème de Database is locked justement.

Bonjour Denis !

C’est un choix voulu du concepteur. Il semble que c’est un des éléments qui permet de maintenir une base de code hyper simple.

Ce n’est pas choquant car, d’après le site officiel[1], SQLite ne se positionne pas comme un concurrent à Postgre ou MySQL. Je cite :
> SQLite does not compete with client/server databases. SQLite competes with fopen().

Et en cas de concurrence importante en *écriture*, ils conseillent même de prendre un autre moteur de base de données [2].

Merci pour la note intéressante sur les moteurs MySQL alternatifs. Je ne les utilise jamais et ne les connais donc pas du tout.

Bonne journée.

[1] https://sqlite.org/whentouse.html
[2] https://sqlite.org/faq.html#q5

j’utilise massivement Sql avec une couche serveur C++/ASIO que j’ai monté par dessus dans un service windows pour gérer l’accès concurrentiel en multi-thread de plusieurs clients en réseau. Effectivement il faut bien gérer les erreurs 5 et 6.
Un ajout d’info par rapport a ce qui a été dit: le parcours d’un select sur une table sur un thread empêchera la modification sur un autre thread (lock).
Ma stratégie en multi-thread:
Pour chaque client du service qui ouvre la même base on aura x clients= x handle de base. je recommande ce mode de fonctionnement plutôt que plusieurs clients/thread pour une base ouverte.
Sur les erreurs 5 et 6 je mets une boucle d’attente jusqu’à ce la base/table soit déverrouillée.
Dans cette boucle j’ai un traitement qui va chercher dans les autres thread les selects en cours pour les interrompre ce qui libère la base et permet l’écriture ou autre tache bloquante. j’ai un mécanisme de reprise du select interrompu en utilisant la propriété offset (Limit -1 offset xxx) du select pour redémarrer après la dernière position mémorisée du curseur de parcours. Cdt.

Laisser un commentaire

(requis)

(requis)