choc! Il y a tellement de verrous dans une seule instruction SQL...

Recommandé (gratuit) : Tutoriel SQL

Gap Lock plus Row Lock, il est facile de juger si An une erreur s'est produite lorsqu'un problème d'attente de verrouillage s'est produit.

Étant donné que le verrouillage des espaces n'est efficace que sous le niveau d'isolement de lecture répétable, cet article utilise par défaut la lecture répétable.

Règles de verrouillage

• Principe 1
  L'unité de base du verrouillage est le verrouillage à clé suivante, qui est un intervalle ouvert et fermé.
• Principe 2
  Seuls les objets accessibles pendant le processus de recherche seront verrouillés.
• Optimisation 1
  Pour les requêtes équivalentes sur l'index, lors du verrouillage de l'index unique, le verrou de la touche suivante dégénère en verrou de ligne.
• Optimisation 2
  Pour les requêtes équivalentes sur l'index, lors d'un déplacement vers la droite et que la dernière valeur ne remplit pas la condition d'égalité, le verrou de la touche suivante dégénère en verrou d'espacement.
• Un bug
  Une requête de plage sur un index unique accédera à la première valeur qui ne remplit pas la condition.

Préparation des données

Nom de la table : t
Nouvelles données : (0,0,0),(5,5,5), ( 10,10,10),(15,15,15),(20,20,20),(25,25,25)
Les exemples suivants sont essentiellement illustrés avec des images, je vous recommande donc de le lire contre le manuscrit. Certains exemples peuvent « détruire les trois points de vue ». Il est également recommandé de le pratiquer vous-même après avoir lu l'article.

Case

Verrouillage d'espacement pour une requête de valeur égale

• Verrouillage d'espacement pour une requête de valeur égale

• Il n'y a pas d'id=7 dans le tableau t, donc selon le principe 1, l'unité de verrouillage est la serrure à clé suivante, donc la plage de verrouillage de la session A est (5, 10]

• En même temps, selon l'optimisation 2, requête équivalente (id=7), mais id=10 n'est pas satisfaite, le verrouillage de la touche suivante dégénère en verrouillage d'espacement, donc la plage de verrouillage finale (5,10)

Donc, si la session B insère l'enregistrement avec id=8 dans cet espace, il sera verrouillé, mais la session C peut modifier la ligne avec id=10.

Verrou équivalent à un index non unique

• Verrouillage ajouté uniquement à un index non unique
  

• la session A veut ajouter un verrou en lecture à la ligne c=5 de l'index c
  Selon le principe 1, l'unité de verrouillage est le verrou à clé suivante, donc ajouter le verrou à clé suivante à (0,5]
  c est index normal , donc seul l'enregistrement c=5 est accessible et ne peut pas être arrêté immédiatement . Il doit traverser jusqu'au à droite et n'abandonnez que lorsque c=10 est trouvé. Selon le principe 2, l'accès doit être verrouillé, donc (5,10] doit être ajouté avec le verrouillage à clé suivante
  et cela également. conforme à l'optimisation 2 : jugement d'équivalence, parcours vers la droite, la dernière valeur ne remplit pas la condition d'équivalence de c=5, elle est donc dégradée Gap lock (5,10)
  Selon le principe 2, seul l'objet accédé sera verrouillé.Cette requête utilise un index de couverture et n'a pas besoin d'accéder à l'index de clé primaire, donc aucun verrou n'est ajouté à l'index de clé primaire, donc la session B L'instruction de mise à jour peut être exécutée Mais si la session C veut insérer (. 7,7,7), il sera verrouillé par le verrouillage d'espacement de la session A (5,10). >Dans cet exemple, le verrouillage en mode partage verrouille uniquement l'index de couverture, mais c'est différent s'il s'agit d'une mise à jour lors de l'exécution. update, le système pensera que vous souhaitez ensuite mettre à jour les données, il ajoutera donc les lignes qui remplissent les conditions sur l'index de clé primaire
  

  Cet exemple montre que le verrou est ajouté. à l'index ; en même temps, cela nous donne des conseils si vous souhaitez utiliser le verrouillage en mode partage pour ajouter un verrou de lecture à la ligne afin d'empêcher la mise à jour des données, vous devez contourner l'optimisation de l'index de couverture et ajouter. champs qui n'existent pas dans l'index. Par exemple, modifiez l'instruction de requête de la session A pour sélectionner d à partir de t où c=5 verrouillez-le en mode partage

<.>3 Verrouillage de la plage d'index de clé primaire

requête de plage.

Pour notre table t, les deux instructions de requête suivantes ont-elles la même plage de verrouillage ? mysql> sélectionnez * à partir de t où id=10 pour la mise à jour ;

mysql> sélectionnez * à partir de t où id>=10 et id<11 pour la mise à jour ;

Vous pensez peut-être, définition de l'identifiant Étant de type int, ces deux instructions sont équivalentes, non ? En fait, ils ne sont pas complètement équivalents.

Logiquement, ces deux instructions de requête sont définitivement équivalentes, mais leurs règles de verrouillage sont différentes. Maintenant, laissons la session A exécuter la deuxième instruction de requête pour voir l'effet de verrouillage.

Figure 3 Verrous pour les requêtes de plage sur l'index de clé primaire
Nous allons maintenant utiliser les règles de verrouillage mentionnées ci-dessus pour analyser quels verrous seront ajoutés à la session A ?

Au démarrage de l'exécution, nous devons trouver la première ligne avec id=10, elle devrait donc être next-key lock(5,10). Selon l'optimisation 1, la condition d'équivalence sur l'identifiant de clé primaire dégénère dans un verrou de ligne, seul le verrou de ligne pour la ligne avec l'identifiant = 10 est ajouté

La recherche de plage continue et la ligne avec l'identifiant = 15 s'arrête, donc le verrouillage de la touche suivante (10,15). ] doit être ajouté

Ainsi, la portée du verrouillage de la session A à ce moment est l'index de clé primaire, l'identifiant de verrouillage de ligne = 10 et le verrouillage de clé suivante (10,15). peut comprendre les résultats de la session B et de la session C. .

Vous devez faire attention à une chose ici lorsque la session A localise la ligne avec id=10 pour la première fois, elle est jugée comme une requête équivalente. , et lors de la numérisation vers la droite jusqu'à id=15, la plage est utilisée pour la requête et le jugement.

Regardez à nouveau le verrouillage des requêtes de plage. Vous pouvez le comparer avec le cas 3

Le verrouillage de plage d'index non unique

session_1	session_2	session_3
begin; select * from t where c>=10 and c<11 for update;
	insert into t values(8,8,8);(blocked)
		update t set d=d+1 where c=15;(blocked)

• la session1 est en cours. en premier lieu Lorsque c=10 est utilisé pour localiser des enregistrements pour la première fois, l'index c est ajouté avec (5,10] next-key lock
• c qui est un index non unique . Il n'y a pas d'optimisation. règles, c'est-à-dire qu'il ne dégénérera pas en un verrou de ligne
• Par conséquent, le verrou final de la session1 est le (5,10] de c et (10,15] le verrou à clé suivante.

Donc, à en juger par les résultats, sesson2 est bloqué lorsqu'il veut insérer l'instruction d'insertion de (8,8,8).

L'index non unique doit être scanné jusqu'à c=15 pour savoir qu'il n'est pas nécessaire de continuer à parcourir en arrière.

Bug de verrouillage de plage d'index unique

Les quatre premiers cas utilisent deux principes et deux optimisations, puis examinez le cas de bogue de règle de verrouillage.

session1 est une requête de plage

• Selon le principe 1, ajoutez uniquement (10,15] next-key lock à l'identifiant de l'index, car l'identifiant est la seule clé, donc la boucle détermine le ligne id=15 Il est temps d'arrêter la traversée.

Mais lors de la mise en œuvre, InnoDB continuera à analyser jusqu'à la première ligne qui ne remplit pas la condition, c'est-à-dire id=20, et comme il s'agit d'une analyse de plage, le (15,20] next-key lock sur le l'identifiant sera également verrouillé.

Donc session2 sera bloquée s'il veut mettre à jour la ligne id=20.
La Session3 sera également bloquée si l'id=16 est inséré.

Il va de soi qu'il n'est pas nécessaire de verrouiller la ligne id=20, car l'index unique scanne jusqu'à id=15 pour déterminer qu'il n'est pas nécessaire de continuer la traversée. Mais cela se fait toujours dans l’implémentation, ce qui peut être un bug.

Exemples d'"équivalence" sur des index non uniques

Pour mieux illustrer la notion de "gap".
Insérer l'enregistrement 7

La ligne c=10 nouvellement insérée, c'est-à-dire qu'il y a maintenant deux c=10 dans le tableau. Alors, quel est l’état actuel de l’écart sur l’indice c ?
Puisque l'index non unique contient la valeur de la clé primaire, il est impossible d'avoir deux lignes "identiques".

Mais maintenant, bien qu'il y ait deux c=10, leurs identifiants de valeur de clé primaire sont différents, il y a donc un écart entre les deux enregistrements c=10.

Regardez le cas suivant.

6

la logique de verrouillage de suppression est similaire à select ... for update, qui est également conforme aux règles initiales.

session_1	session_2	session_3
begin; select * from t where id>10 and id<=15 for update;
	update t set d=d+1 where id=20;(阻塞)
		insert into t values(16,16,16);(阻塞)

session_1	session_2	session_3
begin session_1 session_2 session_3 begin; delete * from t where c=10 insert into t values(13,13,13);(阻塞) update t set d=d+1 where c=15; supprimer * de t où c=10
	insérer dans t valeurs(13,13,13);(blocage)
td>		mettre à jour t définir d=d+1 où c=15 ;

Lorsque vous parcourez la session 1, accédez d'abord au premier c=10 :

Selon le principe 1, ajoutez (c=5, id=5) à (c=10, id=10) suivant -key lock

session_1	session_2
begin; delete * from t where c=10 limit 2
	insert into t values(13,13,13);(阻塞)

Ensuite, session1 recherche vers la droite jusqu'à ce qu'elle rencontre la ligne (c=15,id=15), et la boucle se termine. Selon l'optimisation 2, requête équivalente, les lignes qui ne remplissent pas les conditions se trouvent à droite, donc cela dégénère en un gap lock (intervalle ouvert, (c= Les deux lignes 5, id=5) et (c=15, id=15) sont sans verrouillage). Verrouillage d'instruction à 7 limites session_2

session_1
begin supprimer * de t où c= 10 limite 2
	insérer dans t values(13,13,13); (blocage)

L'instruction delete de session1 ajoute la limite 2. Vous savez qu'il n'y a en réalité que deux enregistrements avec c=10 dans la table t, donc que la limite 2 soit ajoutée ou non, l'effet de la suppression est le même, mais l'effet du verrouillage est différent. On peut voir que l'instruction d'insertion de la session B est exécutée avec succès, ce qui est différent du résultat du cas 6.

C'est parce que l'instruction delete dans le cas 7 ajoute explicitement une limite de limite 2, donc après avoir parcouru la ligne (c=10, id=30), il y a déjà deux instructions qui remplissent la condition, boucle C'est sur.

Par conséquent, la plage de verrouillage sur l'index c devient la plage d'ouverture avant et d'arrière-fermeture de (c=5, id=5) à (c=10, id=30), comme le montre la figure ci-dessous :

Effet de verrouillage avec limite 2

Vous pouvez voir que l'écart après (c=10, id=30) n'est pas dans la plage de verrouillage, donc le L'instruction insert pour insérer c=12 peut être exécutée avec succès.

L'importance directrice de cet exemple pour notre pratique est d'essayer d'augmenter la limite lors de la suppression de données. Cela contrôle non seulement le nombre de données supprimées, ce qui rend l'opération plus sûre, mais réduit également la portée du verrouillage.

Un exemple de blocage

Dans l'exemple précédent, lorsque nous l'avons analysé, nous l'avons analysé selon la logique du verrouillage à clé suivante, car cette analyse et cette comparaison sont pratiques . Enfin, regardons un autre cas pour illustrer : le verrouillage de la touche suivante est en fait le résultat de la somme du verrouillage d'espacement et du verrouillage de ligne.

Vous devez vous demander, ce concept n’a-t-il pas été mentionné au début ? Ne vous inquiétez pas, jetons d'abord un œil à l'exemple suivant :

Séquence d'opérations du cas 8

session A Après avoir démarré la transaction, exécutez l'instruction de requête et ajoutez un verrou en mode partage et ajoutez un verrou en mode partage dans l'index Le verrouillage à clé suivante (5,10] et le verrouillage à espacement (10,15) sont ajoutés à c ; l'instruction de mise à jour de la

session B également doit ajouter le verrou à clé suivante (5,10] à l'index c. Entrez le verrou en attente ;

Ensuite, la session A veut insérer la ligne (8,8,8), qui est verrouillée par le verrou d'espacement de la session B. InnoDB annule la session B en raison d'un blocage. >

Vous vous demandez peut-être si l'application de verrouillage par clé suivante pour la session B n'a pas encore réussi

En fait, c'est le cas ? comme ceci, l'opération « ajouter le verrouillage de la clé suivante (5,10] » de la session B ? , est en fait divisée en deux étapes. Tout d'abord, ajoutez le verrou d'espacement de (5,10), et le verrouillage est réussi ; puis ajoutez la ligne lock de c=10, puis il est verrouillé

En d'autres termes, on peut utiliser le next-key lock pour analyser les règles de verrouillage, mais il faut savoir que l'exécution spécifique est divisée en deux étapes : gap. lock et row lock.

Résumé

Tous les cas sont vérifiés en lecture répétable. La lecture répétable suit le protocole de verrouillage en deux étapes. la transaction est validée ou annulée.

Dans le dernier cas, vous pouvez clairement savoir que le verrouillage de la clé suivante est en fait implémenté par le verrouillage d'espacement et le verrouillage de ligne. Il sera plus facile à comprendre si vous passez en lecture validée. niveau d'isolement. La partie de verrouillage d'espacement est supprimée pendant le processus, c'est-à-dire qu'il ne reste que la partie de verrouillage de ligne

Il existe une autre optimisation sous le niveau d'isolement de validation de lecture, à savoir : le verrouillage de ligne ajouté pendant le processus. l'exécution de l'instruction est terminée après l'exécution de l'instruction. , il est nécessaire de libérer directement le verrou de ligne sur la "ligne qui ne remplit pas les conditions" sans attendre que la transaction soit validée

Sous le niveau d'isolement read-commit, la plage de verrouillage est plus petite et le temps de verrouillage est plus court, de sorte que de nombreuses entreprises ont également utilisé la validation de lecture par défaut. Lorsque l'entreprise a besoin d'utiliser une lecture répétable, elle peut résoudre le problème de lecture fantôme et maximiser la capacité du système à traiter les transactions en parallèle.

Verrouillage des espaces et verrouillage des lignes. Il est facile de faire des erreurs en jugeant si l'attente du verrouillage se produira.

Étant donné que les verrouillages des espaces ne sont efficaces que sous le niveau d'isolement de lecture répétable, cet article utilise par défaut la lecture répétable. . Pour des connaissances connexes, veuillez visiter la colonne gratuite

SQL

~~

.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!