Parlons des différents modes et types de verrous dans MYSQL

Cet article vous apporte des connaissances pertinentes sur les différents types et modes de verrous dans MySQL. J'espère qu'il vous sera utile.

Dans le travail de développement quotidien, nous devons gérer la base de données presque tous les jours. En tant que SQL BOY qui ne connaît que CRUD, à l'exception de l'utilisation de mybatis-generator pour générer automatiquement du code de couche DAO tous les jours, nous ne le faisons presque pas. Nous devons nous soucier de la base de données. Comment gérer les requêtes simultanées, mais tout à coup, un jour, la base de données MYSQL a été alertée et un blocage s'est produit. Nous avons paniqué et nous n'avons pas pu nous empêcher de nous demander : n'est-ce pas juste une requête normale ? Pourquoi est-ce toujours le cas ? fermé?

Afin d'éviter que l'expression de panique ne soit captée par le superviseur, nous devons savoir à l'avance quels verrous se trouvent dans la base de données.

Dans MySQL, les verrous sont en fait divisés en deux catégories : le type de verrouillage (lock_type) et le mode de verrouillage (lock_mode).

Le type de verrou décrit la granularité du verrou, c'est-à-dire l'endroit où le verrou est spécifiquement ajouté ; et le mode de verrouillage décrit le type de verrou ajouté, qu'il s'agisse d'un verrou en lecture ou d'un verrou en écriture. Les modes de verrouillage sont souvent utilisés conjointement avec les types de verrouillage.

Divisé selon le mode de verrouillage

Verrouillage en lecture

Verrouillage en lecture, également appelé verrouillage partagé/verrouillage S/verrouillage partagé.

Le verrou de lecture est un verrou créé par une transaction (telle que la transaction A) lors de l'exécution d'une opération de lecture (telle que la lecture d'une table ou la lecture d'une certaine ligne). D'autres transactions peuvent lire les données simultanément (ajoutées verrouillées), mais ces données sont verrouillées. ne peut pas être modifié (sauf si l'utilisateur détenant le verrou a libéré le verrou).

Une fois que la transaction A ajoute un verrou en lecture aux données, d'autres transactions peuvent toujours y ajouter des verrous en lecture (partagés), mais elles ne peuvent pas ajouter de verrous en écriture.

Ajout de verrous de lecture sur les enregistrements

InnoDB prend en charge les verrous de table et les verrous de ligne. Le verrouillage d'une ligne (c'est-à-dire un enregistrement) ne verrouille pas l'enregistrement, mais ajoute un verrou à l'index correspondant au verrouillage de l'enregistrement. . S'il n'y a pas d'index dans la condition Where, tous les enregistrements seront verrouillés.

L'instruction de verrouillage explicite est :

Remarque : La lecture mentionnée ici fait référence à la lecture actuelle et la lecture de l'instantané ne nécessite pas de verrouillage. Les lectures de sélection ordinaires sont généralement des lectures d'instantanés, à l'exception des instructions de verrouillage explicites telles que select...lock in share mode, qui deviendront des lectures actuelles sous le niveau sérialisable du moteur InnoDB, les lectures de sélection ordinaires deviendront également des lectures d'instantanés.

De plus, il est à noter que pour l'analyse du processus de verrouillage des lignes, il est nécessaire de combiner l'analyse en fonction du niveau d'isolement des transactions, s'il faut utiliser un index (quel type d'index), si l'enregistrement existe et d'autres facteurs pour déterminer où le verrou est ajouté.

Plusieurs situations d'ajout de verrous de lecture dans le moteur innodb

Les requêtes normales ajouteront des verrous S aux enregistrements lorsque le niveau d'isolement est sérialisable. Mais cela dépend aussi du scénario : la lecture non transactionnelle (auto-commit) ne nécessite pas de verrouillage sous le niveau d'isolement Serialisable

Niveau d'isolement sérialisable : Si la condition de requête est un index unique et une requête unique de valeur égale : c'est le cas ; dans cette clause Ajoutez un verrou S à l'enregistrement ; requête de condition non unique (lorsque la requête analyse plusieurs enregistrements) : l'enregistrement lui-même + l'écart entre les enregistrements (la plage de l'écart doit être spécifiquement analysée), ajoutez un S lock;

select... en mode partage donnera L'enregistrement est verrouillé en S, mais le comportement de verrouillage est différent selon le niveau d'isolement :

Niveau d'isolement RC : le verrouillage S est ajouté à l'enregistrement. Niveau d'isolement RR/sérialisable : si la condition de requête est un index unique et une requête unique à valeur égale : le verrou S est ajouté à la requête de condition non unique (lorsque la requête analyse plusieurs enregistrements) : enregistrement lui-même + espace d'enregistrement (l'espacement de l'enregistrement ; la plage de l'écart doit être spécifiquement analysée), ajoutez le verrouillage S

Habituellement, l'opération d'insertion n'est pas verrouillée, mais si une clé en double est détectée (ou s'il y a une clé en double marquée pour suppression) lors de l'insertion ou de la mise à jour d'un enregistrement, Pour une insertion/mise à jour ordinaire, le verrou S sera ajouté, tandis que pour les instructions SQL telles que replace into ou insert...on duplicate, le verrou X sera ajouté.

insérer ... sélectionner Lors de l'insertion de données, le verrou S sera ajouté aux données analysées sur la table de sélection ;

Vérification de clé étrangère : lorsque nous supprimons un enregistrement sur la table parent, nous devons vérifier s'il existe des contraintes de référence. . À ce moment, les enregistrements correspondants sur la sous-table seront analysés et des verrous S seront ajoutés.

Ajouter un verrou en lecture sur la table

Le verrouillage de table est implémenté par le serveur MySQL, quel que soit le moteur de stockage, le verrouillage de table peut être utilisé. Généralement, lors de l'exécution d'une instruction DDL, telle que ALTER TABLE, la table entière sera verrouillée. Vous pouvez également verrouiller explicitement une table lors de l'exécution d'une instruction SQL.

L'instruction de verrouillage explicite pour la table est :

Lorsque nous utilisons le moteur MYISAM, nous n'avons généralement pas besoin de verrouiller manuellement, car le moteur MYISAM verrouillera automatiquement nos instructions SQL et l'ensemble du processus ne nécessite pas l'utilisation de l'utilisateur. intervention. :

• Instruction de requête (select) : ajoutera automatiquement des verrous de lecture aux tables impliquées ;

• Instructions de mise à jour (update, delete, insert) : ajoutera automatiquement des verrous d'écriture aux tables impliquées.

Verrouillage en écriture

Verrouillage en écriture, verrouillage exclusif/verrouillage X/verrouillage exclusif. La nature bloquante des verrous en écriture est beaucoup plus stricte que celle des verrous en lecture. Une fois qu'une transaction a ajouté un verrou en écriture aux données, les autres transactions ne peuvent ni lire ni modifier les données.

Identique à la portée du verrouillage en lecture et du verrouillage en écriture, le verrouillage en écriture peut être ajouté aux enregistrements et aux tables.

Ajoutez un verrou en écriture sur l'enregistrement

Ajoutez un verrou en écriture sur l'enregistrement, le moteur doit utiliser InnoDB.

Habituellement, les instructions select ordinaires ne se verrouillent pas (sauf lorsque le niveau d'isolement est sérialisable). Si vous souhaitez ajouter un verrou exclusif pendant la requête, vous devez utiliser l'instruction suivante :

Ajouter un verrou en écriture pendant la requête :

.

et ajouter read Les verrous sont les mêmes et le verrou en écriture est également ajouté à l'index.

Ajouter un verrou en écriture lors de la mise à jour :

Ajouter un verrou en écriture sur la table

L'instruction pour ajouter explicitement un verrou en écriture à la table est :

Lorsque le moteur sélectionne myisam, insérez/ update L'instruction /delete ajoutera automatiquement un verrou exclusif à la table.

Compatibilité du verrouillage en lecture-écriture :

• Le verrou en lecture est partagé, il ne bloquera pas les autres verrous en lecture, mais il bloquera les autres verrous en écriture

• Le verrou en écriture est exclusif, il bloquera les autres verrous en lecture ; Et le verrouillage en écriture ;

• Résumé : La lecture et la lecture ne s'excluent pas mutuellement, la lecture et l'écriture s'excluent mutuellement, l'écriture et l'écriture s'excluent mutuellement

Le verrouillage d'intention

Le verrouillage d'intention est une table qui ne s'exclut pas mutuellement. n'est pas en conflit avec les verrous de niveau ligne. Le verrouillage de niveau indique le type de verrouillage (verrou S ou InnoDB prend en charge plusieurs verrous de granularité, permettant la coexistence de verrous au niveau des lignes et des verrous au niveau des tables.

Les verrous d'intention sont divisés en :

Verrous partagés d'intention (verrous IS) : les verrous IS indiquent que la transaction en cours a l'intention de définir des verrous partagés sur les lignes du tableau

Lorsque l'instruction suivante est exécutée, le verrou IS sera acquis d'abord, car cette opération consiste à acquérir le verrou S : Acquérir le verrou S : sélectionnez ... verrouiller en mode partage

Verrouillage exclusif d'intention (verrouillage IX) : le verrou IX indique que la transaction en cours a l'intention de définir un verrou exclusif sur la ligne dans la table

L'instruction suivante sera acquise en premier lors de l'exécution du verrou IX, car cette opération acquiert le verrou X : Acquérir le verrou X : sélectionnez... pour la mise à jour

Avant que la transaction n'acquière le verrou S et le verrou X sur une certaine table, elle doit d'abord acquérir respectivement le verrou IS et le verrou IX correspondants.

Que fait le verrouillage d'intention ?

Si une autre transaction tente un verrouillage partagé ou exclusif au niveau de la table, elle sera bloquée par le verrouillage d'intention au niveau de la table contrôlé par la première transaction. La deuxième transaction n'a pas besoin de vérifier les verrous de page ou de ligne individuels avant de verrouiller la table, mais uniquement les verrous d'intention sur la table.

Exemple : Table test_user :

Transaction A a acquis un verrou exclusif sur une certaine ligne mais ne l'a pas soumis 

La transaction B souhaite acquérir un verrou partagé sur la table test_user ; les verrous partagés sont différents des verrous exclusifs Les verrous sont mutuellement exclusifs, donc lorsque la transaction B tente d'ajouter un verrou partagé à la table test_user, elle doit s'assurer que :

Aucune autre transaction ne détient actuellement de verrou exclusif sur la table des utilisateurs (table serrure exclusive).
Aucune autre transaction ne détient actuellement un verrou exclusif (verrouillage exclusif de ligne) sur une ligne de la table des utilisateurs.
Afin de vérifier si la deuxième condition est remplie, la transaction B doit vérifier s'il y a un verrou exclusif sur chaque ligne de la table tout en s'assurant qu'il n'y a pas de verrou exclusif sur la table test_user. C'est évidemment une approche très inefficace, mais avec le verrouillage d'intention, la situation est différente :

Parce qu'à ce moment la transaction A a acquis deux verrous : le verrouillage d'intention exclusif sur la table des utilisateurs et les données avec l'identifiant 28 Verrou exclusif sur la ligne.

La transaction B veut obtenir le verrou partagé de la table test_user :

La transaction B a seulement besoin de détecter si la transaction A détient le verrou exclusif d'intention de la table test_user, et on peut alors savoir que la transaction A doit détenir le verrou exclusif de certaines lignes de données dans le verrou de la table, alors la demande de verrouillage de la transaction B pour la table test_users sera exclue (bloquée), il n'est donc pas nécessaire de détecter s'il existe un verrou exclusif pour chaque ligne de données de la table.

La transaction C souhaite également obtenir un verrou exclusif sur une ligne de la table users :

La transaction C détecte que la transaction A détient un verrou exclusif d'intention sur la table test_user
Les verrous d'intention ne s'excluent pas mutuellement, donc les transactions C ont obtenu le verrou exclusif d'intention de la table test_user ;
Comme il n'y a pas de verrou exclusif sur la ligne de données avec l'identifiant 31, la transaction C a finalement réussi à obtenir le verrou exclusif sur la ligne de données.
Les verrous d'intention ne s'excluent pas mutuellement, mais il existe un certain degré de compatibilité et d'exclusion mutuelle entre les verrous d'intention et les autres verrous de table, comme suit :

Compatibilité et exclusivité mutuelle entre les verrous d'intention :

Exclusivité mutuelle entre les verrous d'intention et les verrous exclusifs/partagés ordinaires :

Verrouillage à incrémentation automatique

Lorsque nous concevons la structure de la table, généralement le la clé primaire est définie pour s'incrémenter automatiquement (pensez à pourquoi ?).

Dans le moteur de stockage InnoDB, un compteur auto-croissant est défini pour chaque champ auto-croissant. Nous pouvons exécuter l'instruction suivante pour obtenir la valeur actuelle de ce compteur :

Lorsque nous effectuons une opération d'insertion, l'opération effectuera une opération +1 basée sur la valeur actuelle de ce compteur auto-croissant et l'attribuera à Dans la colonne auto-croissante, cette opération est appelée auto-inc Locking, qui est un verrou à incrémentation automatique. Ce type de verrou utilise en fait un mécanisme spécial de verrouillage de table. Si l'opération d'insertion se produit dans une transaction, le verrou est libéré immédiatement après. l'opération d'insertion est terminée, au lieu d'attendre la validation de la transaction.

Selon le type de verrouillage

Verrouillage global

Le soi-disant verrouillage global verrouille en fait l'intégralité de l'instance de base de données.

Il existe une différence entre une instance de base de données et une base de données :

Une base de données est un entrepôt qui stocke des données. Plus précisément dans MySQL, une base de données est en fait une collection de fichiers de données (c'est ce que nous appelons habituellement une base de données, comme par exemple). création d'une instruction de base de données) créer une base de données...).

L'instance de base de données fait référence à l'application qui accède à la base de données. Dans Mysql, il s'agit du processus mysqld.

Pour comprendre simplement, une instance de base de données contient différentes bases de données que vous créez.

Si vous ajoutez un verrou global à l'instance de base de données, la bibliothèque entière sera en lecture seule (c'est très dangereux).

De manière générale, le scénario d'utilisation typique des verrous globaux concerne la sauvegarde complète de la base de données, c'est-à-dire la sélection de toutes les tables de la base de données. Mais attention, laisser toute la bibliothèque en lecture seule entraînera de sérieux problèmes :

• Ajouter un verrou global à la bibliothèque principale Pendant la période de verrouillage, aucune opération de mise à jour ne pourra être effectuée, et de nombreuses fonctions de la bibliothèque principale. Les entreprises sont fondamentalement indisponibles ;

• Ajoutez un verrouillage global sur la bibliothèque esclave pendant la période de verrouillage, la synchronisation maître-esclave ne peut pas être effectuée, ce qui entraînera un retard de synchronisation maître-esclave.

L'instruction de verrouillage du verrou global est :

La méthode pour déverrouiller le verrou global est :

• Déconnectez simplement la session qui exécute le verrou global

• Exécutez l'instruction SQL de déverrouillage ; : déverrouiller les tables ;

Si vous avez besoin d'une sauvegarde de base de données, vous pouvez utiliser l'outil de sauvegarde logique officiel mysqldump.

Puisque nous avons déjà l'outil de vidage, pourquoi avons-nous besoin de FTWRL ? Une lecture cohérente est bonne, mais seulement si le moteur prend en charge ce niveau d'isolation. Par exemple, les moteurs comme MyISAM ne prennent pas en charge les transactions. À ce stade, nous devons utiliser la commande FTWRL.

S'il y a une lecture ou une écriture avant FTWRL, FTWRL attendra la fin de la lecture et de l'écriture avant de s'exécuter.

Lorsque FTWRL est exécuté, les données des pages sales doivent être vidées sur le disque. Étant donné que la cohérence des données doit être maintenue, FTWRL est exécuté lorsque toutes les transactions sont soumises.

La mise en œuvre du verrouillage global repose toujours sur le verrouillage des métadonnées.

Metadata Lock

MetaData Lock, également appelé verrouillage MDL, est utilisé pour protéger les informations de métadonnées qui ne peuvent pas être contrôlées activement. Dans MySQL version 5.5, les verrous MDL ont été introduits, principalement pour maintenir la cohérence des métadonnées lors d'opérations simultanées de DDL et DML dans un environnement concurrent. Par exemple, la situation suivante :

Niveau d'isolement : RR

S'il n'y a pas de protection contre le verrouillage des métadonnées, alors la transaction 2 peut effectuer directement des opérations DDL, provoquant une erreur de la transaction 1. Le verrou MDL a été ajouté dans la version MYSQL5.5 pour empêcher cette situation de se produire. Puisque la transaction 1 ouvre la requête, elle obtient le verrou de métadonnées. Le mode de verrouillage est le verrouillage de lecture MDL. Si la transaction 2 veut exécuter DDL, elle doit obtenir le verrou d'écriture MDL. Puisque les verrous de lecture et d'écriture s'excluent mutuellement, la transaction 2. doit attendre que la transaction 1 soit libérée. Elle ne peut être exécutée que si le verrou de lecture est libéré.

• Lors de l'ajout, de la suppression, de la modification et de l'interrogation d'enregistrements dans la table (opérations DML), les verrous de lecture MDL sont automatiquement ajoutés ;

• Lors de la modification de la structure de la table (opérations DDL), les verrous d'écriture MDL sont automatiquement ajoutés ; ajouté.

La granularité des verrous MDL

Les verrous MDL sont implémentés au niveau du serveur Mysql, pas dans le plug-in du moteur de stockage. Selon l'étendue du verrouillage, les verrous MDL peuvent être divisés dans les catégories suivantes :

Mode de verrouillage MDL

Verrouillage au niveau de la page

Un verrou dans MySQL avec une granularité de verrouillage entre les verrous au niveau de la ligne et les verrous au niveau de la table. Les verrous au niveau des tables sont rapides mais comportent de nombreux conflits. Les verrous au niveau des lignes présentent peu de conflits mais sont lents. Nous avons donc pris un niveau de page compromis et verrouillé un groupe d'enregistrements adjacents à la fois. Différents moteurs de stockage prennent en charge différents mécanismes de verrouillage. Selon les différents moteurs de stockage, les caractéristiques des verrous dans MySQL peuvent être résumées comme suit :

Le verrouillage au niveau de la page est un niveau de verrouillage unique dans MySQL. Il est appliqué au moteur BDB. Les caractéristiques du verrouillage au niveau de la page sont les suivantes : La granularité du verrouillage se situe entre le verrouillage au niveau de la ligne et le verrouillage au niveau de la table, de sorte que la surcharge de ressources requise pour obtenir le verrou et la capacité de traitement simultané qu'il peut fournir se situent également entre les deux ci-dessus. De plus, le verrouillage au niveau de la page et au niveau de la ligne entraînera un blocage.

Comparaison de la granularité du verrouillage : verrous au niveau de la table > verrous au niveau de la page > verrous au niveau de la ligne

verrous au niveau de la table

Les verrous de table ont été introduits ci-dessus, par rapport à l'ajout fin de Verrouillage des lignes Verrouillage, le verrouillage de la table consiste à verrouiller la table entière. Étant donné que la table entière est verrouillée, ce n'est pas aussi compliqué que le verrouillage de ligne, donc le verrouillage est plus rapide que le verrouillage de ligne, et il n'y aura pas de blocage (car la transaction acquiert le verrou de table souhaité en une seule fois), mais les verrous de table en ont également Problèmes : la plage de verrouillage est trop grande et lorsque la concurrence est relativement élevée, la probabilité de conflits de verrouillage augmentera, ce qui réduira considérablement les performances de concurrence.

Méthode de verrouillage du verrouillage de table

Lorsque le moteur sélectionne MYISAM

Le moteur MYISAM prend uniquement en charge les verrous de table et ne prend pas en charge les verrous de rangée.

L'instruction pour ajouter manuellement des verrous au niveau de la table est la suivante :

Lorsque nous utilisons le moteur MYISAM, nous n'avons généralement pas besoin de verrouiller manuellement, car le moteur MYISAM verrouillera automatiquement nos instructions SQL et l'ensemble du processus. ne nécessite pas l'intervention de l'utilisateur :

• Instruction de requête (select) : ajoutera automatiquement des verrous en lecture aux tables impliquées ;

• Instructions de mise à jour (update, delete, insert) : ajoutera automatiquement des verrous en écriture aux tables impliquées

Lorsque InnoDB est sélectionné comme moteur, le moteur InnoDB prend en charge à la fois les verrous au niveau de la ligne et les verrous au niveau de la table, et la valeur par défaut est les verrous au niveau de la ligne. Verrouillez manuellement la table dans le moteur InnoDB et utilisez également l'instruction de lecture/écriture lock table {tableName} pour ajouter des verrous en lecture/écriture.

De plus, innodb prend également en charge un verrouillage au niveau de la table : le verrouillage d'intention (déjà introduit ci-dessus).

En général, les verrous au niveau de la table du moteur InnoDB incluent cinq modes de verrouillage :

LOCK_IS : Verrouillage intentionnel en lecture

• LOCK_IX : Verrouillage intentionnel en écriture

• LOCK_S : Verrouillage intentionnel en lecture

• LOCK_X : Verrouillage en écriture de table

• LOCK_AUTO_INC : Verrouillage à incrémentation automatique

Verrouillage au niveau de la ligne

Dans le processus d'écriture du code métier, la chose la plus courante avec laquelle nous entrons en contact est le niveau de la ligne (verrous au niveau de la table) En raison de problèmes de performances, son utilisation n'est généralement pas recommandée). Par rapport aux verrous au niveau de la table, les verrous au niveau des lignes présentent des avantages évidents en termes de performances :

Peu de conflits : il n'y a que quelques conflits de verrouillage lors de l'accès à différents enregistrements dans plusieurs threads

• La granularité du verrou est faible : il peut être verrouillé pendant longtemps. Une seule ligne n'a aucun impact sur les autres lignes, donc le degré de concurrence est le plus élevé

• Mais lorsque vous utilisez des verrous de ligne, si vous ne faites pas attention, il est très facile de se bloquer ( les verrous de table n'existent pas), donc lorsque vous utilisez des verrous de ligne, vous devez faire attention à l'ordre de verrouillage et à la plage de verrouillage.

Les verrous de ligne d'InnoDB sont implémentés en verrouillant les éléments d'index, ce qui signifie que les verrous de ligne ne seront utilisés que lors de l'interrogation d'enregistrements via l'index. Si les données sont interrogées sans l'index, les verrous de table seront utilisés et les performances seront considérablement réduites.

Vous devez vous rappeler : les verrous de ligne sont également appelés verrous d'enregistrement, et les verrous d'enregistrement sont ajoutés à l'index.

où la condition spécifie l'index de clé primaire : alors le verrou est ajouté à l'index de clé primaire

• où la condition spécifie l'index secondaire : le verrouillage d'enregistrement sera non seulement ajouté à cet index secondaire, mais aussi à celui-ci ; Sur l'index clusterisé correspondant à l'index secondaire ;

• condition Where si l'index ne peut pas être utilisé : MySQL ajoutera des verrous d'enregistrement à toutes les lignes de données de la table entière, et la couche du moteur de stockage renverra tous les enregistrements pour le filtrage par le Serveur MySQL.

Verrouillage d'enregistrement : LOCK_REC_NOT_GAP (verrouiller les enregistrements uniquement)

Le verrouillage d'enregistrement est le verrouillage de ligne le plus simple. Par exemple, au niveau d'isolement RR, lors de l'exécution de l'instruction select * from t_user Where id = 1 pour la mise à jour, l'enregistrement id = 1 (où id est la clé primaire) est réellement verrouillé (le verrou est ajouté à l'index clusterisé) .

Les verrous d'enregistrement sont toujours ajoutés à l'index Même si une table n'a pas d'index, la base de données créera implicitement un index. Si la colonne spécifiée dans la condition WHERE est un index secondaire, le verrou d'enregistrement sera non seulement ajouté à l'index secondaire, mais également à l'index clusterisé correspondant à l'index secondaire.

Notez que si l'instruction SQL ne peut pas utiliser l'index, elle utilisera l'index principal pour implémenter une analyse complète de la table. À ce stade, MySQL ajoutera des verrous d'enregistrement à toutes les lignes de données de la table entière.

Si une condition WHERE ne peut pas être filtrée rapidement par l'index, la couche du moteur de stockage verrouillera tous les enregistrements et les renverra, puis la couche du serveur MySQL les filtrera. Lorsqu'il n'y a pas d'index, cela consommera non seulement beaucoup de ressources de verrouillage et augmentera la surcharge de la base de données, mais réduira également considérablement les performances de concurrence de la base de données. Par conséquent, vous devez vous rappeler d'utiliser l'index pendant l'opération de mise à jour (car). l'opération de mise à jour ajoutera un verrou X).

Plusieurs types de verrous au niveau des lignes :

Verrouillage des espaces : LOCK_GAP (verrouille uniquement les espaces)

Le verrouillage des espaces est un type de verrouillage par intervalles. Le verrou est ajouté à l'espace libre qui n'existe pas, ou entre deux enregistrements d'index, ou le premier enregistrement d'index, ou l'espace après le dernier index. Il est utilisé pour indiquer que seule une plage est verrouillée (généralement de manière isolée lors de l'exécution). requêtes de plage) niveau dans les intervalles RR ou sérialisables).

Les verrous GAP sont généralement utilisés sous le niveau d'isolement RR. L'objectif principal de l'utilisation du verrouillage GAP est d'empêcher la lecture fantôme. Dans l'intervalle verrouillé par le verrouillage GAP, les données ne peuvent pas être insérées ou mises à jour.

Les conditions de génération des gap locks : le niveau d'isolement d'innodb est Repeatable Read ou Serialisable.

Explication de la portée du verrouillage d'écart :

Niveau d'isolement : RR

Prenez la table Student comme exemple de données, id comme clé primaire, stu_code comme numéro d'étudiant et ajoutez un index normal.

Définition de la zone de verrouillage de l'espace :

Regardez à gauche la valeur A la plus proche comme intervalle de gauche selon les conditions de recherche, et regardez à droite la valeur la plus proche B comme intervalle de droite. Le verrouillage de l'espace est (A, B)

À gauche Si la valeur A la plus proche ne peut pas être trouvée, elle est infinitésimale. Comme intervalle de gauche, cherchez à droite la valeur la plus proche B car l'intervalle de droite est (infinitésimal, B).

Trouvez la valeur A la plus proche à gauche comme intervalle de gauche. , la valeur B la plus proche ne peut pas être trouvée à droite, qui est l'infini. Comme intervalle de droite, le verrouillage de l'écart est (A, infini)

Intervalle (A, B) Exemple :

Transaction 1 :

select * from student where stu_code = 4 for update

Transaction 2 :

insert into student vaues(2, 2, 'A')；
insert into student values(4, 5, 'B');

Selon l'analyse de l'instruction SQL de la transaction 1, la plage du verrou d'espacement est : stu_code = 4 enregistrements existent, donc l'intervalle de gauche est le plus récent la valeur de l'index est stu_code = 3, l'intervalle de droite est la valeur d'index la plus récente est stu_code = 7, donc l'écart La plage est : (3, 7), donc les deux instructions d'insertion de la transaction 2, l'une est en dehors de la plage et la l'autre est dans la plage. Celui en dehors de la plage peut être inséré, tandis que celui à l'intérieur de la plage est bloqué, donc (2, 2, 'A') peut être inséré avec succès (4, 5, 'B') ; bloqué.

Exemple d'intervalle (infinitésimal, B) :

Transaction 1 :

select * from student where stu_code = 1 for update

Transaction 2 :

insert into student vaues(2, 0, 'c')；
insert into student vaues(2, 2, 'r')；
insert into student vaues(5, 2, 'o')；

Selon l'analyse de l'instruction SQL de la transaction 1, la plage du verrou d'écart est : stu_code = 1 existe, et il y a aucun enregistrement récent à gauche. Il est donc infinitésimal à gauche, et la valeur d'index la plus proche à droite est stu_code = 3, donc la plage de verrouillage de l'espace est : (infini, 3). Par conséquent, l'exécution des première et deuxième instructions insert sql de la transaction 2 est bloquée et entre dans le cadre du verrou d'espacement. La troisième instruction insert sql peut être exécutée avec succès et ne se trouve pas dans la plage de verrouillage d'espacement.

Exemple d'intervalle (A, infini) :

Transaction 1 :

select * from student where stu_code = 7 for update

Transaction 2 :

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

Selon l'analyse de l'instruction SQL de la transaction 1, la plage du verrou d'écart est : stu_code = 7 existe, l'index le plus proche la valeur à gauche est stu_code = 4, et il n'y a pas de valeur d'index à droite, donc la plage du verrou d'espacement est : (4, infini). La première instruction encart peut être exécutée avec succès et n'est pas dans la plage d'espacement ; l'exécution de la deuxième instruction insert est bloquée, ce qui se situe dans la plage de verrouillage d'espacement.

Si l'instruction de requête n'est pas enregistrée dans la base de données, comment la verrouiller ?

La requête ci-dessus est enregistrée. Si l'instruction de requête n'est pas enregistrée dans la base de données, comment la verrouiller ? Continuons :

Transaction 1 :

update student set stu_name = '000' where stu_code = 10

Transaction 2 :

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

Selon l'instruction d'exécution ci-dessus, l'enregistrement est introuvable. Prenez l'enregistrement le plus proche à gauche (10, 7, 'Xiao Ming') comme gauche. intervalle, C'est-à-dire que la plage du verrou d'espacement est : (7, infini). La première instruction d'insertion n'est pas dans la plage et peut être exécutée avec succès ; la deuxième instruction d'exécution d'insertion est bloquée dans la plage et ne parvient pas à s'exécuter. Si la condition Where de la transaction 1 est supérieure à 10, la valeur d'enregistrement la plus proche se trouve également à gauche comme intervalle de gauche, donc la plage du verrouillage de l'écart est également : (7, infini)

Résumé : Les conditions de l'écart génération de verrous

RR/Niveau d'isolement sérialisable Suivant : Lorsque vous sélectionnez... Où...Pour la mise à jour :

Utilisez uniquement des requêtes d'index uniques et ne verrouillez qu'un seul enregistrement, InnoDB utilisera des verrous de ligne.

Lorsqu'une seule requête d'index unique est utilisée, mais que la condition de recherche est une recherche par plage, ou que le résultat de la recherche est une recherche unique mais n'existe pas (en essayant de verrouiller des données inexistantes), le verrouillage par clé suivante se produit.

Lors de l'utilisation de la récupération d'index ordinaire, quel que soit le type de requête, tant qu'elle est verrouillée, un verrouillage d'espacement sera généré.

Lors de l'utilisation simultanée d'un index unique et d'un index ordinaire, étant donné que les lignes de données sont triées d'abord selon l'index ordinaire, puis selon l'index unique, des verrouillages d'espacement se produiront également.

Next-Key Lock : LOCK_ORDINARY, également connu sous le nom de Next-Key Lock

Next-Key Lock est une combinaison de verrouillage d'enregistrement + de verrouillage d'espace. Comme les verrous d'espacement, il n'y a pas de verrou Next-key sous le niveau d'isolation RC (à moins qu'il ne soit activé de force en modifiant la configuration), seul le niveau d'isolation RR/Serialalisable en dispose.

MySQL InnoDB fonctionne sous le niveau d'isolement de lecture répétable (RR) et verrouille les lignes de données à l'aide de Next-Key Lock, ce qui peut empêcher efficacement l'apparition de lectures fantômes. Next-Key Lock est une combinaison de verrouillage de ligne et de verrouillage d'espacement Lorsque InnoDB analyse l'enregistrement d'index, il ajoutera d'abord un verrou de ligne (Record Lock) à l'enregistrement d'index, puis ajoutera un verrou d'espacement (Gap Lock) aux espaces. des deux côtés de l’enregistrement d’index. Après avoir ajouté le verrou d'espacement, les autres transactions ne peuvent pas modifier ou insérer d'enregistrements dans cet espace.

Lorsque l'index interrogé contient des attributs uniques (index unique, index de clé primaire), le moteur de stockage Innodb optimisera le verrouillage de la clé suivante et le réduira à un verrouillage d'enregistrement, c'est-à-dire que seul l'index lui-même est verrouillé, pas la plage. .

Insérer un verrou d'intention : LOCK_INSERT_INTENSION

Insérer un verrou d'intention, qui est utilisé lors de l'insertion d'enregistrements. Il s'agit d'un verrou d'espacement spécial. Ce verrou représente l'intention d'insertion. Ce verrou n'existera que lorsque l'instruction d'insertion est exécutée.

Supposons qu'il y ait des enregistrements d'index avec les valeurs id = 1 et id = 5 respectivement (aucun enregistrement entre 1 et 5), des transactions distinctes tentent d'insérer id = 2 et id = 3 respectivement, avant d'obtenir le verrou exclusif de l'inséré row , chaque transaction utilise un verrou d'intention d'insertion pour verrouiller l'espace entre 1 et 5, mais ne se bloquera pas. Parce qu'il n'y aura pas de conflit entre les verrous d'intention insérés.

L'insertion de verrous d'intention entrera en conflit avec les verrous d'espacement ou les verrous Next-Key : la fonction des verrous d'espacement est de verrouiller l'intervalle pour empêcher d'autres transactions d'insérer des données et de provoquer des lectures fantômes.

Dans le scénario ci-dessus, en supposant que la transaction A a acquis à l'avance le verrou d'espacement avec l'identifiant dans l'intervalle (1, 5), lorsque la transaction B tentera d'insérer l'identifiant = 2, elle tentera d'abord d'acquérir le verrou d'intention d'insertion , mais en raison du verrou d'intention d'insertion, il entre en conflit avec le verrou d'espacement, provoquant l'échec de l'insertion, évitant ainsi l'apparition de lectures fantômes.

Conclusion

Le mécanisme de verrouillage de MYSQL est très complexe. Dans le travail de développement réel, vous devez être très prudent lors de la définition du niveau d'isolation. Par exemple, le niveau RR aura un verrou d'espacement de plus que le niveau RC. ce qui peut entraîner de graves problèmes de performances. Cet article présente brièvement la classification des verrous MYSQL du point de vue du mode de verrouillage et de la portée du verrouillage. J'espère que dans le processus de développement de la base de données, nous pourrons analyser et étudier soigneusement si nos instructions SQL sont raisonnables (en particulier les blocages et autres). des problèmes surviendront) ) !

Apprentissage recommandé : Tutoriel vidéo mysql

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!