Quelles sont les règles de verrouillage du gap lock mysql ?

Règles MySQL 11 pour le verrouillage du verrouillage des espaces

Le verrouillage des espaces ne prendra effet que sous le niveau d'isolement de lecture répétable : le verrouillage de la clé suivante est en fait implémenté par le verrouillage des espaces et le verrouillage des lignes si vous passez au niveau d'isolement de validation en lecture (lecture-commis). , c'est facile à comprendre. La partie de verrouillage d'espace est supprimée au cours du processus, c'est-à-dire qu'il ne reste que la partie de verrouillage de rangée. Sous le niveau d'isolement lecture-validation, il n'y a pas de verrouillage d'espacement. Afin de résoudre l'éventuelle incohérence entre les données et les journaux, le format binlog doit être défini sur ligne. En d'autres termes, la configuration de nombreuses entreprises est la suivante : niveau d'isolement de validation de lecture plus binlog_format=row. L'entreprise n'a pas besoin de la garantie d'une lecture répétable, donc étant donné que la plage de verrouillage des données d'opération lors de la soumission en lecture est plus petite (pas de verrouillage d'espace), ce choix est raisonnable
.

Règles de verrouillage à clé suivante

　 Les règles de verrouillage résumées comprennent deux "" principes "", deux "" optimisations "" et un "bug".
　Principe 1 : L'unité de base du verrouillage est la serrure à clé suivante. Le verrouillage de la clé suivante est un intervalle ouvert et fermé.
　Principe 2 : Seuls les objets accessibles pendant le processus de recherche seront verrouillés. Tout verrou sur un index secondaire, ou un verrou sur une colonne non indexée, sera finalement retracé jusqu'à la clé primaire, et un verrou sera également ajouté à la clé primaire.
　Optimisation 1 : Pour les requêtes équivalentes sur l'index, lors du verrouillage de l'index unique, le verrou de la clé suivante dégénère en verrou de ligne. En d'autres termes, si InnoDB scanne une clé primaire ou un index unique, InnoDB n'utilisera que des verrous de ligne pour verrouiller. Optimisation 2 : Pour les requêtes équivalentes sur l'index (pas nécessairement l'index unique), déplacez-vous vers la droite lorsque la dernière valeur le fait. ne satisfait pas à la condition d'égalité, le prochain verrouillage dégénère en un verrouillage d'espacement.
　Un bug : une requête range sur un index unique accédera à la première valeur qui ne remplit pas la condition.

Analyse de cas

Nous prenons le test de table comme exemple. L'instruction de création de table et l'instruction d'initialisation sont les suivantes : id est l'index de clé primaire

CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

Cas 1 : Verrouillage de l'écart de requête équivalent à un index unique

Depuis là il n'y a pas d'identifiant dans le test du tableau =7 enregistrement

Selon le principe 1, l'unité de verrouillage est la serrure à clé suivante et la plage de verrouillage de la session A est (5,10] ;

En même temps, selon l'optimisation 2 , il s'agit d'une requête équivalente (id=7), et id=10 ne remplit pas les conditions de la requête, et le verrou de la clé suivante dégénère en un verrou d'espacement, donc la plage de verrouillage finale est (5,10)

Cas 2 : Verrou de requête équivalent à un index non unique

Voici la session A Ajouter un verrou en lecture à la ligne avec col1=5 sur l'index col1

　 Selon le principe 1, l'unité de verrouillage est la serrure à clé suivante, laissée ouverte. et à droite fermé, 5 est fermé, donc ce sera (0,5] Ajouter le verrouillage de la touche suivante

Il convient de noter que c est un index normal, donc seul l'accès à l'enregistrement c=5 ne peut pas s'arrêter immédiatement (il peut y avoir autres enregistrements avec col1=5),

doit traverser vers la droite pour trouver Abandonner uniquement lorsque c=10. Selon le deuxième principe, tous les accès doivent être verrouillés, donc le verrouillage de la clé suivante doit être ajouté au. intervalle (5,10].

　　Mais en même temps, cela est conforme à l'optimisation 2 : jugement d'égalité, vers le parcours à droite, la dernière valeur ne satisfait pas la condition d'équivalence de col1=5, donc elle dégénère en gap lock (5 ,10). Selon le principe 2, seul l'objet accédé sera verrouillé. Cette requête utilise un index de couverture, et il n'est pas nécessaire d'accéder à l'index de clé primaire,

Par conséquent, aucun verrou n'est ajouté sur l'index de clé primaire, ce qui explique pourquoi l'instruction de mise à jour de la session B peut être exécutée avec succès

　mais la session C veut insérer un enregistrement de (7,7,7). Elle sera verrouillée par le verrou d'espacement (5,10) de la session A. Ceci. L'exemple montre que le verrou est ajouté à l'index. Lors de l'exécution de la mise à jour, le système pensera que vous souhaitez ensuite mettre à jour les données, il indexera donc la clé primaire en passant. Ajoutez des verrous de ligne aux lignes qui remplissent les conditions

.

　　Si vous souhaitez utiliser le verrouillage en mode partage pour ajouter des verrous de lecture aux lignes afin d'empêcher la mise à jour des données, vous devez contourner l'optimisation de l'index de couverture, car l'index de couverture n'accédera pas à l'index de clé primaire. ne sera pas verrouillé

Cas 3 : Verrouillage de requête de plage d'index de clé primaire

Lors du démarrage de l'exécution, nous devons trouver la première ligne avec l'identifiant = 10, il devrait donc s'agir du verrouillage de la clé suivante (5, 10]. Selon l'optimisation 1, la condition équivalente sur la clé primaire

id est dégénérée en un verrou de ligne, et seul le verrou de ligne de id=10 est ajouté

　Il s'agit d'une requête de plage, et la recherche de plage continue de trouver id=15. . Cette ligne s'arrête et la condition n'est pas remplie, donc next-key lock(10,15] doit être ajouté.

　La portée du verrou pour 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). 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, le jugement de requête par plage est utilisé
.

Cas 4 : Verrouillage de requête de plage d'index non unique

　 Lorsque col1=10 est utilisé pour localiser l'enregistrement pour la première fois, après que le verrouillage de clé suivant de (5,10] soit ajouté à l'index c, car l'index col1 n'est pas unique
Un index n'a pas de règles d'optimisation, ce qui signifie qu'il ne se transformera pas en verrou de ligne. Par conséquent, les verrous finaux ajoutés par la session A sont les deux verrous suivants (5,10] et
(10,. 15] sur l'index c.
　Il est raisonnable d'arrêter l'analyse lorsque col1=15 est analysé, car InnoDB doit analyser col1=15 avant de savoir qu'il n'a pas besoin de continuer la recherche

Cas 5 : bug de verrouillage de requête de plage d'index unique.

　La session A est une requête de plage Selon le principe 1, seul le verrou de clé suivante de (10,15] doit être ajouté à l'identifiant d'index, et comme l'identifiant est la seule clé, la boucle est jugée comme étant. la ligne id=15 Elle devrait s'arrêter. Mais lors de l'implémentation, InnoDB analysera la première ligne qui ne remplit pas la condition, qui est id=20. Et comme il s'agit d'une analyse de plage, l'identifiant d'index est (15,20. ). ] Ce verrou de clé suivante sera également verrouillé Logiquement, le comportement de verrouillage de la ligne id=20 ici est en fait inutile, car après avoir scanné id=15, vous pouvez être sûr que vous n'avez pas besoin de rechercher. plus tard.

Cas 6 : Exemple de " " Equivalent " " sur un index non unique

Ici, j'insère un nouvel enregistrement dans la table t : insérer dans t valeurs (30,10,30) ; maintenant la table Il y a deux lignes avec c=10, mais leurs valeurs de clé primaire id sont différentes (10 et 30 respectivement), il y a donc un écart entre les deux enregistrements avec c=10

Cette fois. nous utilisons l'instruction delete pour vérifier. Notez que la logique de verrouillage de l'instruction delete est en fait similaire à select... for update, qui sont les deux "principes" et les deux "optimisations" que j'ai résumés au début de l'article. un "bug". A ce moment, la session A accède d'abord au premier enregistrement col1=10. De même, selon le principe 1, ce qui est ajouté ici est
(col1=5,id=5). 10) Ce verrou à clé suivante

Puisque c est un index normal, la recherche continue vers la droite jusqu'à ce que la ligne de (col1=15,id=15) soit rencontrée. Selon l'optimisation 2, c'est

une requête équivalente. Les lignes qui ne remplissent pas les conditions se trouvent à droite, elles dégénéreront donc en un espace lock de (col1=10,id=10) à (col1=15,id=15).

　La plage de verrouillage de cette instruction de suppression sur l'index c est la partie couverte par la zone bleue dans l'image ci-dessus. Les côtés gauche et droit de cette zone bleue sont des lignes pointillées, indiquant les intervalles ouverts, c'est-à-dire (col1=5,id=5) et (col1=15,id=15). Il n'y a pas de verrous sur ces deux lignes

. Cas 7 : ajout d'une instruction de limite Lock

　L'instruction de suppression de la session A ajoute la limite 2. Vous savez qu'il n'y a en réalité que deux enregistrements avec c=10 dans le tableau t, donc l'effet de l'ajout ou de la suppression de la limite 2 est le même. Mais l'effet de verrouillage est différent

C'est parce que l'instruction delete dans le cas 7 ajoute clairement une limite de limite 2, donc après avoir parcouru la ligne (col1=10, id=30), il y a déjà deux instructions qui satisfont à la condition bar. , le cycle est terminé. Par conséquent, la plage de verrouillage sur l'index col1 devient la plage d'ouverture avant et de fermeture arrière de (col1=5,id=5)
à (col1=10,id=30), comme le montre la figure suivante :

　L'importance directrice de cet exemple pour notre pratique est d'essayer d'ajouter une limite lors de la suppression de données.

　Cela contrôle non seulement le nombre de données supprimées, rendant l'opération plus sûre, mais réduit également la portée du verrouillage.

Cas 8 : Un exemple de blocage

　La session A exécute l'instruction de requête après avoir démarré la transaction et ajoute un verrou en mode partage, et ajoute next-keylock(5,10] et gap lock(10, 15) (Le parcours d'index vers la droite dégénère en gap lock);

L'instruction de mise à jour de la session B doit également ajouter le next-key lock(5,10] à l'index c et entrer dans l'attente de verrouillage ; elle est en fait divisée en deux étapes,

Ajoutez d'abord le verrou d'espacement de (5,10) avec succès, puis ajoutez le verrou de ligne de col1=10, car la session A a déjà ajouté un verrou de lecture à cette ligne et l'application de blocage sera bloquée à ce moment-là. Ensuite, la session A doit ensuite insérer la ligne (8,8,8), qui est verrouillée par le verrou d'espacement de la session B. En raison d'un blocage, InnoDB annule la session B.

Cas 9 : Gap lock 1 de l'ordre par tri d'index

Telle que l'instruction suivante
La figure ci-dessous est un diagramme schématique de l'identifiant d'index de cette table.
begin;
select * from testwhere id>9 et id

Tout d'abord, la sémantique de cette instruction de requête est ordonnée par id desc Pour obtenir toutes les lignes qui. remplir les conditions, optimiser Le processeur doit d'abord trouver "la valeur du
ème id　Ce processus est obtenu grâce au processus de recherche de l'arborescence d'index. Dans le moteur, nous voulons en fait trouver la valeur de id=12, mais au final nous ne l'avons pas trouvée, mais avons trouvé l'écart (10,15). (id=15 ne remplit pas la condition, donc le verrouillage de la touche suivante dégénère en verrouillage d'espacement (10,
15).)
Ensuite, traversez vers la gauche Pendant le processus de parcours, ce n'est plus une requête équivalente, et id=. 5 sera scanné. Une ligne, et comme l'intervalle est ouvert à gauche et fermé à droite, un verrou à clé suivante (0,5] sera ajouté. En d'autres termes, pendant le processus d'exécution, lors de la localisation de l'enregistrement.
via la recherche arborescente, utilisez la méthode "Requête égale".

Cas 10 : Gap lock 2 de l'ordre par tri par index

　Puisqu'il s'agit d'un ordre par col1 desc, la première chose à localiser est la col1 "la plus à droite". = sur l'index col1 20 lignes. Il s'agit d'une requête équivalente avec un index non unique :

Ajoutez d'abord le verrou à clé suivante à l'intervalle ouvert de gauche pour former l'intervalle (15,20). Traversez vers la droite, col1= 25 ne remplit pas la condition et dégénère en Gap Lock ajoutera donc Gap Lock (20,25) et Next-Key Lock (15,20).

Arrêtez de scanner l'index vers la gauche lors du passage vers col1=10. Le le prochain verrouillage par clé (next-keylock) sera appliqué dans l'intervalle d'ouverture à droite et de fermeture à gauche (5,10)

C'est la raison pour laquelle l'instruction d'insertion de la session B est bloquée pendant le processus de numérisation, col1=20. , col1=15, col1=10 ont tous des valeurs, car ils sont select *, donc trois verrous de ligne seront ajoutés à la clé primaire

id Par conséquent, la plage du verrou de l'instruction select de la session A est :

　　On index. col1 (5, 25);

　　Deux identifiants = 15 et 20 sur l'index de clé primaire

Cas 11 : Exemple de mise à jour modifiant les données - insérer d'abord puis supprimer

Remarque : Le premier enregistrement trouvé. selon col1>5 est col1=10, donc (0,5 ne sera pas ajouté ] Ce verrou à clé suivante.

La plage de verrouillage de la session A est (5,10], (10,15], (15,20 ], (20,25] et (25,supremum] sur l'index col1.

Après cela, la première instruction de mise à jour de la session B doit changer col1=5 en col1=1. Vous pouvez le comprendre en deux étapes :
　　Insérez le record (col1=1, id=5);
　　Delete (col1=5, id =5) Cet enregistrement
　Grâce à cette opération, la plage de verrouillage de la session A devient comme indiqué sur la figure 7 :

　D'accord, la prochaine session B exécutera la mise à jour t set col1 = 5 où col1 = 1 Cette instruction peut être divisée en deux étapes :

　　Insérer l'enregistrement (col1=5, id=5) ;

　　Supprimer l'enregistrement (col1=1, id=5) ; ). La première étape consiste à essayer d'insérer des données dans (1,10) qui a ajouté un verrouillage d'espacement, afin qu'elles soient bloquées
　　.

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!