Une lecture cohérente n'est pas toujours pratique, dans certaines circonstances. Supposons que vous voulez ajouter une ligne dans votre table CHILD, et vous assurer que l'enfant a déjà un parent dans la table PARENT.

Supposez que vous utilisiez une lecture cohérente, pour lire la table PARENT, et que vous découvrez le parent de l'enfant dans cette table. Pouvez vous ajouter tranquillement la ligne fille dans la table CHILD? Non, car il peut arriver que durant ce temps, un autre utilisateur a effacé la ligne parente dans la table PARENT, et vous n'en êtes pas conscient.

La solution est d'exécuter la commande SELECT en mode verrouillage, avec LOCK IN SHARE MODE.

SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Effectuer une lecture en mode partagé signifie que vous allons lire les dernières données disponibles, et que nous allons poser un verrou sur les lignes que nous lisons. Si les dernières données appartiennent à une transaction non validée d'un autre utilisateur, nous allons attendre que ce soit fait. Un verrou partagé évite que les autres utilisateurs ne modifient ou n'effacent la ligne que nous lisons. Après que nous ayons obtenu le nom du parent, nous pouvons tranquillement ajouter le nom du fils dans la table CHILD, et valider notre transaction. Cet exemple montre comment implenter l'intégrité référentielle dans votre application.

Ajoutons un autre exemple : nous avons un champs compteur dans la table CHILD_CODES que nous utilisons pour assigner un identifiant unique à chaque enfant que nous ajoutons dans la table CHILD. Evidemment, en utilisant une lecture cohérente ou une lecture partagée pour lire la valeur courante du compteur n'est pas une bonne idée, car deux utilisateurs de la base peuvent simultanément lire la même valeur de compteur, et nous allons obtenir une erreur de clé doublon lorsque nous ajouterons le second des deux fils.

Ici, LOCK IN SHARE MODE n'est pas une bonne solutionm, car si deux utilisateurs lisent le compteur en même temps, au moins l'un des deux sera bloqué lorsqu'il tentera de modifier le compteur.

Dans ce cas, il y a deux bonnes méthodes pour implémenter la lecture et l'incrémentation du compteur : (1) modifiez le compteur d'une unité, et lisez le après cela ou (2) lisez le compteur d'abord, avec un verrou en mode FOR UPDATE, puis incrémentez le :

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

Une commande SELECT ... FOR UPDATE va lire les dernières données disponibles pour chaque ligne, et pose un verrou dessus en même tant qu'il lit. De cette fa¸on, il pose le même verrou que la commande UPDATE.

Notez que la commande ci-dessus est simplement un exemple de fonctionnement de SELECT ... FOR UPDATE. En MySQL, la tâche spécifique de création d'un identifiant unique peut être réalisée avec un seul accès à la table :

UPDATE child_codes
       SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

La comande SELECT ne fait que lire l'identifiant spécifique à la connexion. Il ne fait aucun accès à la table.