Verrous SQL

Notion de verrou

Information associée à une donnée (ou un groupe de donnée) qui bloque l'accès aux données verrouillées aux autres utilisateurs. Lorsqu'un utilisateur souhaite accéder à une donnée verrouillée :
    Soit il reçoit un message d'erreur lui indiquant que la donnée est verrouillée et qu'il faut donc repasser plus tard.
    Soit il est mis dans une file d'attente, et aura accès aux données une fois que l'utilisateur qui a posé le verrou, l'aura levé.

Ces verrous sont principalement de 3 types :
    Ils peuvent empêcher tout accès aux données aux autres utilisateurs.
    Ils peuvent laisser uniquement un accès en lecture seule aux autres utilisateurs.
    Le dernier utilisateur qui accède aux données en lecture pour modification prend le verrou. Quand il enregistre, il vérifie qu'il a encore le verrou. Ce type de verrouillage, plus compliquée ne sera pas utilisée ici.

verrou courte durée (natif SQL) - LOCK

Ces verrous sont associés à des connexions à la base de données. Ainsi deux scripts PHP utilisant les mêmes identifiants de connexion, sont des connexions différentes.
2 types :
    Les verrous READ permettent aux autres connexions d'accéder aux données verrouillées en lecture seule.
    Les verrous WRITE empêchent tout accès aux autres connexions - ce que nous utiliserons ici.

Levés dès que la connexion à la base MySQL est fermée, ou dès que des verrous sont posés sur une autre table de la base par la même connexion. Lorsqu'une connexion veut accéder à une donnée verrouillée par une autre connexion, MySQL met la requête en attente. Elle sera exécutée dès que le verrou sera levé.

ex:  LOCK TABLE MaTable AS mt WRITE;

Quand une connexion pose un verrou sur une ou plusieurs tables, elle ne peut accéder qu'aux tables verrouillées avec les noms déclarés lors du verrou. Si cette contrainte n'est pas respectée, MySQL génère une erreur.  MaTable sera accédée dans les requêtes avec l'alias mt

Si dans les requêtes SQL, on accède à plusieurs tables ou à une même table avec plusieurs alias, il faut les verrouiller toutes en même temps.
Exemple pour poser un verrou WRITE sur une table MaTable qui sera accédés dans les requêtes avec son nom d'origine et l'alias mt, ainsi que la table AutreTable LOCK TABLE MaTable WRITE, MaTable AS MT WRITE, AutreTable WRITE;

UNLOCK TABLES; supprime tous les verrous éventuels de la connexion courante.

 

verrou "longue durée", verrou programmé

Un verrou programmé permet de verrouiller un enregistrement pour une durée suffisante afin qu'un utilisateur puisse effectuer une saisie de formulaire (temps génération de page en ms). Ne pas bloquer l'accès complet à la table pendant tout ce temps, mais uniquement à la ligne concernées.

Ajoutons deux colonnes à chaque table pouvant comporter des verrous et une constante en PHP:

  • verrou_id contient l'identifiant de la session PHP utilisée par le script posant le verrou.
    Ceci a l'avantage de permettre d'utiliser les verrous sur un site où aucune authentification n'est demandée, ou sur un site ou plusieurs utilisateurs possèdent les mêmes identifiants. De même nous évitons les problèmes posés par un utilisateur qui ouvre plusieurs connexions depuis différents poste avec le même login. Par contre l'utilisation de l'identifiant de session demande d'avoir une durée de vie du verrou plus courte que la durée de la session, et d'avoir un hébergeur supportant cette fonctionnalité. Quand aucun verrou n'est posé sur l'enregistrement, ce champ est à la valeur système NULL. Ce sera un CHAR32 car un identifiant de session est composé de 32 caractères alphanumériques.
  • verrou_heure contient l'instant où le verrou est posé.
    Ceci implique que tous les verrous accédant à la table aient la même durée de vie, puisque pour savoir si un verrou est encore valide, il va être comparé l'heure actuelle avec l'heure de création du verrou à laquelle on ajoute la durée de vie du verrou. Ce champ sera un TIMESTAMP.
  • Constante PHP verrou_duree contient la durée de vie du verrou. Ceci assure que tout les verrous accédant à la table ait la même durée de vie, mais contraint en plus que tous les verrous de l'application aient la même durée de vie. Elle sera de type entier = le nombre de minute de cette durée de vie.