En SQL, un verrou de base de données est un mécanisme de sécurité. Il permet d’interdire ou de limiter l’accès à un élément d’une base de données. Ça peut être une ligne d’une table ou une table entière. L’utilité des verrous est simple : conserver l’ACIDité de la base.
Verrous SQL : 2 exemples concrets
Imaginons le cas d’un site de vente en ligne de billets d’avions avec un internaute qui souhaite réserver un vol à bord d’un avion. La réservation de la place se fera au dernier moment : c’est à dire lorsque la personne a validé sa commande et est en train de saisir sa CB et d’attendre que la banque veuille bien donner son feu vert. À ce moment, pour protéger l’utilisateur et lui permettre de finaliser sa transaction, il faut que le site web empêche les autres utilisateurs de réserver le même siège en même temps.
Pour cela, un verrou en écriture peut être posé sur le siège sélectionné pendant 3 minutes. Si au bout de 3 minutes, l’internaute n’a pas réalisé l’achat, le verrou peut être libéré et la vente peut être faite avec un autre internaute.
Autre exemple avec des statistiques. Imaginons qu’une base de données soit utilisée en temps réel par des utilisateurs tout autour du globe et qu’en même temps, il faille réaliser des calculs un peu lourd (15 minutes) de statistiques. Si pendant le calcul des utilisateurs viennent ajouter / modifier / supprimer des données, le programme de statistiques se retrouvera avec des données différentes entre le début de son calcul et sa fin.
Dans ce cas, on peut imaginer verrouiller les données d’une table en écriture et ne conserver que l’accès en lecture.
Verrous en base de données
Derrière cette notion de verrou se cache une autre notion, importante pour les bases de données : la concurrence. Autrement dit, qui et combien de personnes peuvent-elles accéder en même temps aux mêmes données sans compromettre les données de la base.
Les verrous SQL sont utilisés de façons très différentes d’un SGBD à l’autre et même entre différentes version du même SGBD. Lorsqu’il y a des transactions SQL (une suite logique d’opérations qui doivent être réalisées d’une seule traite), les verrous sont nécessairement utilisés. On utilise alors les instructions COMMIT et ROLLBACK pour libérer les verrous après avoir validé/invalidé la suite d’opérations SQL.
- On utilise l’instruction LOCK TABLES en SQL pour verrouiller une ou plusieurs table ;
- On utilise UNLOCK TABLES pour déverrouiller une ou plusieurs tables ;
- On peut réaliser des verrous exclusifs (EXCLUSIVE), partagés (SHARE) que ce soit sur une table ou sur une ligne.
Risques avec les verrous
Les verrous peuvent entraîner des erreurs en cas de mauvaise manipulation : blocage fatal (deadlock), verrous non libérés, collisions…