MySQL apprend à parler de verrous et de classification

Cet article vous aidera à comprendre les verrous dans MySQL et à présenter la classification de granularité des verrous et la classification de compatibilité des verrous. J'espère qu'il vous sera utile.

1. Scénario de concurrence de base de données

Dans un scénario de concurrence élevée, sans prendre en compte les autres middlewares, la base de données aura les scénarios suivants :

• Lire : Il n'y a aucun problème et aucun contrôle de concurrence n'est requis.
• Lecture et écriture : il existe des problèmes de sécurité des threads, qui peuvent entraîner des problèmes d'isolation des transactions et rencontrer des lectures incorrectes, des lectures fantômes et des lectures non répétables.
• Écrit : Il existe des problèmes de sécurité des threads et il peut y avoir des problèmes de perte de mise à jour, tels que la perte du premier type de mise à jour et la perte du deuxième type de mise à jour.

Compte tenu des problèmes ci-dessus, la norme SQL stipule que les problèmes qui peuvent survenir sous différents niveaux d'isolement sont différents :

Quatre niveaux d'isolement majeurs MySQL :

Niveau d'isolement	Lecture sale	Non- lecture répétable	Lecture fantôme
LECTURE UNCOMMITTED : lecture non validée	peut arriver	peut arriver	peut arriver
LECTURE COMMITTED : lecturecommise	résolu	peut arriver	peut arriver
LECTURE RÉPÉTABLE : Lecture répétable	Résolu	Résolu	Peut arriver
SÉRIALISABLE : Sérialisable	Résolu	Résolu	Résolu

On peut voir que MySQL résout en fait le problème de non-répétabilité au niveau d'isolement REPEATABLE READ, fondamentalementrésout le problème de lecture fantôme, mais dans les cas extrêmes, les lectures fantômes existent toujours.

Alors quelle est la solution ? De manière générale, il existe deux solutions :

1️⃣ MVCC pour les opérations de lecture, verrouillage pour les opérations d'écriture

Pour les lectures, sous MVCC de niveau RR, lorsqu'une transaction est démarrée, un ReadView sera généré, puis trouvé via ReadView Une version historique qui remplit les conditions, et cette version est construite à partir de journaux d'annulation, lors de la génération de ReadView, un instantané est en fait généré, donc la requête SELECT à ce moment est snapshot read (ou lecture cohérente), nous savons que sous RR. , un ReadView sera généré uniquement lorsqu'une opération SELECT est effectuée pour la première fois lors de l'exécution d'une transaction. Les opérations SELECT ultérieures réutiliseront ce ReadView, évitant ainsi les lectures non répétables et dans une large mesure cela évite le problème de lecture fantôme. . Pour l'écriture, puisqu'aucune opération de verrouillage n'est effectuée sur aucun enregistrement de la table lors de la lecture d'un instantané ou d'une lecture cohérente et que la transaction de ReadView est une version historique, mais la dernière version de l'opération d'écriture n'est pas la même. Il peut y avoir des conflits, de sorte que d'autres transactions peuvent librement apporter des modifications aux enregistrements de la table.

2️⃣ Les opérations de lecture et d'écriture sont verrouillées

Si certains de nos scénarios commerciaux ne permettent pas de lire l'ancienne version de l'enregistrement, mais doivent lire la dernière version de l'enregistrement à chaque fois, comme dans le cas d'une transaction de dépôt bancaire, Il faut

d'abord lire le solde du compte, puis l'ajouter au montant de ce dépôt

, et enfin

l'écrire dans la base de données. Après avoir lu le solde du compte, vous ne souhaitez pas que d'autres transactions accèdent au solde. Ce n'est que jusqu'à ce que la transaction de dépôt soit terminée que d'autres transactions puissent accéder au solde du compte. De cette façon, l'enregistrement doit être verrouillé lors de sa lecture, ce qui signifie que les opérations de lecture et d'écriture sont également mises en file d'attente et exécutées comme les opérations d'écriture-écriture. Pour une lecture sale, c'est parce que la transaction en cours lit un enregistrement

écrit par une autre transaction non validée, mais si une autre transaction

verrouille cet enregistrement lors de l'écriture de l'enregistrement, alors la transaction en cours Il n'est plus possible de lire l'enregistrement, il n'y aura donc pas de problème de lecture sale. Pour les lectures non répétables, c'est parce que la transaction en cours lit d'abord un enregistrement, et après qu'une autre transaction apporte des modifications à l'enregistrement et le valide, la transaction en cours obtiendra des valeurs différentes lors de sa relecture. dans la transaction en cours Lorsque l'enregistrement est lu, l'enregistrement est verrouillé. Ensuite, une autre transaction ne peut pas modifier l'enregistrement et, naturellement, une lecture non répétable n'aura pas lieu.

Pour la lecture fantôme, c'est parce que la transaction en cours lit un enregistrement dans une plage, puis une autre transaction insère un nouvel enregistrement dans la plage

Lorsque la transaction en cours lit à nouveau l'enregistrement dans la plage, un. un nouvel enregistrement est trouvé. Nous appelons les enregistrements nouvellement insérés des enregistrements fantômes.

Comment appréhender cette gamme ? Comme suit : Supposons qu'il n'y ait qu'un seul élément de données avec id=1 dans la table user.

Lorsque la transaction A exécute une opération de requête de id = 1, les données peuvent être interrogées s'il s'agit d'une requête de plage, telle que id dans (1,2. ) code>, une seule donnée sera interrogée.

• A ce moment, la transaction B effectue une nouvelle opération avec id = 2 et la soumet.

id=1的数据。

• 当事务 A 执行一个id = 1的查询操作，能查询出来数据，如果是一个范围查询，如 id in(1,2)，必然只会查询出来一条数据。

• 此时事务 B 执行一个id = 2的新增操作，并且提交。

• 此时事务 A 再次执行id in(1,2)的查询，就会读取出 2 条记录，因此产生了幻读。

注：由于 RR 可重复读的原因，其实是查不出 id = 2的记录的，所以如果执行一次 update ... where id = 2

À ce moment, la transaction A exécute à nouveau la requête de id in(1,2), et 2 enregistrements seront lus, donc une lecture fantôme se produit.

Remarque

 : En raison de la lecture répétable de RR, l'enregistrement avec id = 2 ne peut pas réellement être trouvé, donc si vous exécutez une mise à jour . .. où id = 2, vous pouvez le découvrir en recherchant la plage.

Il n'est pas facile de résoudre le problème de lecture fantôme en verrouillant, car les enregistrements fantômes n'existent pas lorsque la transaction en cours lit les enregistrements pour la première fois, il est donc un peu gênant de verrouiller lors de la lecture, car elle ne sait pas qui verrouiller.

Alors, comment InnoDB le résout-il ? Jetons d'abord un coup d'œil aux verrous dont dispose le moteur de stockage InnoDB.

2. Verrous et classifications dans MySQL

Dans la

documentation officielle

de MySQL, le moteur de stockage InnoDB introduit les types de verrous suivants :

🎜De même, cela semble encore confus, mais nous pouvons suivre les étapes pour apprendre Classification JDK par verrouillage : 🎜🎜🎜🎜

3. Classification de la granularité du verrouillage

Qu'est-ce que la granularité du verrouillage ? La granularité du verrouillage fait référence à la portée de ce que vous souhaitez verrouiller.

Par exemple, si vous allez aux toilettes à la maison, il vous suffit de verrouiller la salle de bain. Vous n'avez pas besoin de verrouiller toute la maison pour empêcher les membres de la famille d'entrer. La salle de bain est votre granularité de verrouillage.

Qu'est-ce qu'une granularité de verrouillage raisonnable ?

En effet, la salle de bain ne sert pas seulement à aller aux toilettes, mais aussi à prendre une douche et à se laver les mains. Cela pose la question de l’optimisation de la granularité du verrouillage.

Lorsque vous prenez une douche dans la salle de bain, d'autres peuvent effectivement entrer et se laver les mains en même temps, à condition qu'ils soient isolés si les toilettes, la baignoire et le lavabo sont tous séparés et relativement indépendants (humides et secs). sont séparés), en fait La salle de bain peut être utilisée par trois personnes en même temps, mais bien sûr les trois personnes ne peuvent pas faire la même chose. Cela affine la granularité du verrouillage. Tant que vous fermez la porte de la salle de bain lorsque vous prenez une douche, d'autres peuvent toujours entrer et se laver les mains. Si les différentes zones fonctionnelles ne sont pas divisées et isolées lors de la conception initiale de la salle de bains, l’utilisation maximale des ressources de la salle de bains ne peut pas être obtenue.

De même, il existe également une granularité de verrouillage dans MySQL. Habituellement divisé en trois types, verrous de ligne, verrous de table et verrous de page.

3.1 Verrouillage de ligne

Dans l'introduction des verrous partagés et des verrous exclusifs, ils sont en fait enregistrés pour une certaine ligne, ils peuvent donc également être appelés verrous de ligne.

Le verrouillage d'un enregistrement n'affecte que cet enregistrement, la granularité du verrouillage des verrous de ligne est donc la meilleure de MySQL. Le verrouillage par défaut du moteur de stockage InnoDB est le verrouillage des lignes.

Il présente les caractéristiques suivantes :

1. La plus faible probabilité de conflit de verrouillage et une concurrence élevée

   Étant donné que la granularité des verrous de ligne est faible, la probabilité de conflit de ressources de verrouillage est également la plus petite, donc la probabilité de verrouillage le conflit est faible et la concurrence est élevée. Plus le sexe est élevé.

2. Surcharge élevée et verrouillage lent

   Les verrous consomment beaucoup de performances. Imaginez, si vous verrouillez plusieurs éléments de données dans la base de données, cela occupera inévitablement beaucoup de ressources et vous devrez attendre le précédent. verrou à déverrouiller.

3. Produira une impasse

   Pour ce qu'est une impasse, vous pouvez lire ci-dessous.

3.2 Verrouillage de table

Le verrouillage au niveau de la table est un verrou au niveau de la table, qui verrouillera la table entière Il peut très bien éviter les blocages et constitue également le plus grand mécanisme de verrouillage granulaire de MySQL.

Le verrou par défaut du moteur de stockage MyISAM est le verrouillage de table.

Il présente les caractéristiques suivantes :

1. Faible surcharge et verrouillage rapide

   Puisqu'il verrouille la table entière, il doit être plus rapide que de verrouiller une seule donnée.

2. Aucun blocage ne se produira

   La table entière est verrouillée. Les autres transactions ne peuvent pas du tout obtenir le verrou et, naturellement, aucun blocage ne se produira.

3. La granularité du verrouillage est grande, la probabilité de conflit de verrouillage est élevée et la concurrence est faible

3.3 Verrouillage de page

Le verrouillage au niveau de la page est un niveau de verrouillage unique dans MySQL, qui n'est pas trouvé dans d'autres logiciels de gestion de bases de données.

La granularité des verrous au niveau de la page se situe entre les verrous au niveau de la ligne et les verrous au niveau de la table, de sorte que la surcharge de ressources requise pour obtenir les verrous et les capacités de traitement simultané qu'ils peuvent fournir se situent également entre les deux ci-dessus. De plus, les verrous au niveau de la page, comme les verrous au niveau des lignes, peuvent provoquer des blocages.

GrandOui

Verrouillage de ligne

Verrouillage de table

Verrouillage de page

Verrouillage de la granularité

Petit

Grand

Entre les deux

Efficacité de verrouillage

Lent

Rapide

Probabilité de conflit entre les deux ance aérien

Petit

Entre le deux

Est-ce une impasse

Non

Oui

4. Classification de compatibilité des verrous

Dans MySQL, la lecture des données est principalement divisée en lecture actuelle et lecture d'instantané :

• Lecture d'instantané

  Lecture d'instantané, ce qui est lu est données d'instantané, sans ajouter de sélections ordinaires de les verrous sont des lectures d'instantanés.

  SELECT * FROM table WHERE ...

• Lecture actuelle

  La lecture actuelle signifie la lecture des dernières données, et non des données historiques Locked SELECT, ou l'ajout, la suppression et la modification de données effectueront tous la lecture actuelle.

  SELECT * FROM table LOCK IN SHARE MODE;
  SELECT FROM table FOR UPDATE;
  INSERT INTO table values ...
  DELETE FROM table WHERE ...
  UPDATE table SET ...

Dans la plupart des cas, nous exploitons la base de données sur la base des lectures actuelles, et dans des scénarios simultanés, nous devons non seulement permettre aux situations lecture-lecture de ne pas être affectées, mais aussi faire en sorte que écrire-écrire, lire -Les opérations d'écriture ou de lecture se bloquent, vous devez utiliser des verrous partagés et des verrous exclusifs dans MySQL.

4.1 Verrous partagés et verrous exclusifs

Les verrous partagés (verrous partagés) peuvent également être appelés verrous en lecture, en abrégé verrous S. Les données peuvent être lues simultanément, mais aucune transaction ne peut modifier les données. Les

Exclusive Locks (Exclusive Locks), peuvent également être appelés verrous exclusifs ou verrous en écriture, appelés X locks. Si quelque chose ajoute un verrou exclusif à une ligne, seule cette transaction peut la lire et l'écrire. Avant la fin de cette transaction, les autres transactions ne peuvent pas y ajouter de verrous. Les autres processus peuvent lire mais ne peuvent pas effectuer d'opérations d'écriture. libérer.

Analysons la situation d'acquisition du verrou : S'il y a la transaction A et la transaction B

• La transaction A acquiert le verrou S d'un enregistrement, et à ce moment la transaction B veut également acquérir le verrou S de l'enregistrement, alors la transaction B peut également Le verrou est acquis, ce qui signifie que la transaction A et la transaction B détiennent le verrou S de l'enregistrement en même temps.

• Si la transaction B souhaite acquérir le verrou X de l'enregistrement, cette opération sera bloquée jusqu'à ce que le verrou S soit libéré après la validation de la transaction A.

• Si la transaction A acquiert d'abord le verrou X, que la transaction B souhaite acquérir le verrou S ou le verrou X de l'enregistrement, elle sera bloquée jusqu'à ce que la transaction A soit validée.

Par conséquent, nous pouvons dire que le verrouillage S et le verrouillage S sont compatibles, le verrouillage S et le verrouillage X sont incompatibles, et le verrouillage X et le verrouillage X sont également incompatibles.

4.2 Verrouillage d'intention

Verrouillage partagé d'intention (Verrouillage partagé d'intention), appelé IS Lock. Lorsqu'une transaction doit ajouter un verrou S à un enregistrement, elle doit d'abord ajouter un verrou IS au niveau de la table.

Intention Exclusive Lock (Intention Exclusive Lock), appelé IX Lock. Lorsqu'une transaction doit ajouter un verrou X à un enregistrement, elle doit d'abord ajouter un verrou IX au niveau de la table.

Les verrous d'intention sont des verrous au niveau de la table Ils sont proposés uniquement pour juger rapidementsi les enregistrements de la table sont verrouillés lors de l'ajout de verrous S au niveau de la table et s'il n'y a aucun enregistrement verrouillé dans la table. C'est-à-dire que le verrouillage IS est compatible avec le verrouillage IS et le verrouillage IX est compatible avec le verrouillage IX.

Pourquoi avez-vous besoin d'un verrouillage d'intention ?

Le verrou d'intention d'InnoDB est principalement utilisé lorsque plusieurs verrous granulaires coexistent. Par exemple, la transaction A souhaite ajouter un verrou S à une table. Si une ligne de la table a été ajoutée à un verrou X par la transaction B, l'application du verrou doit également être bloquée. S'il y a beaucoup de données dans la table, la surcharge liée à la vérification de l'indicateur de verrouillage ligne par ligne sera très importante et les performances du système en seront affectées. Par exemple, s'il y a 100 millions d'enregistrements dans la table et que la transaction A a des verrous de ligne sur plusieurs enregistrements, alors la transaction B doit ajouter des verrous au niveau de la table à la table. S'il n'y a pas de verrouillage d'intention, recherchez. si ces 100 millions d'enregistrements sont verrouillés dans la table. S'il y a un verrou d'intention, alors si la transaction A ajoute un verrou d'intention puis un verrou X avant de mettre à jour un enregistrement, la transaction B vérifie d'abord s'il y a un verrou d'intention sur la table et si le verrou d'intention existant entre en conflit avec le verrou qu'elle prévoit à ajouter En cas de conflit, attendez que la transaction A soit libérée sans vérifier chaque enregistrement. Lorsque la transaction B met à jour la table, elle n'a pas réellement besoin de savoir quelle ligne est verrouillée. Elle a seulement besoin de savoir qu'une ligne est verrouillée de toute façon.

Pour parler franchement, la fonction principale des verrous d'intention est de gérer la contradiction entre les verrous de ligne et les verrous de table. Ils peuvent montrer

qu'une transaction détient un verrou sur une certaine ligne, ou se prépare à détenir un verrou

. . Compatibilité des différents verrous au

niveau table

 :

SXS Non compatible Non compatibleNon compatibleNon CompatibleCompatibleCompatibleNon compatibleNon compatible

IS

IX

Compatible

Compatible

IS

4.3 Verrous pour les opérations de lecture

Pour les opérations de lecture MySQL, il existe deux façons de verrouiller.

1️⃣ SELECT * FROM table LOCK IN SHARE MODE

Si la transaction en cours exécute cette instruction, elle ajoutera des verrous S aux enregistrements qu'elle lit, permettant ainsi à d'autres transactions de continuer à acquérir des verrous S pour ces enregistrements (pour Par exemple, d'autres transactions utilisent également l'instruction SELECT ... LOCK IN SHARE MODE pour lire ces enregistrements), mais ne peuvent pas obtenir les verrous X de ces enregistrements (par exemple, utilisez l'instruction SELECT .. . FOR UPDATE pour lire ces enregistrements, ou modifier ces enregistrements directement). SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），但是不能获取这些记录的 X 锁（比方说使用 SELECT ... FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。

如果别的事务想要获取这些记录的 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉

2️⃣ SELECT FROM table FOR UPDATE

如果当前事务执行了该语句，那么它会为读取到的记录加 X 锁，这样既不允许别的事务获取这些记录的 S 锁（比方说别的事务使用 SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的 X 锁（比如说使用 SELECT ... FOR UPDATESi d'autres transactions souhaitent acquérir les verrous X de ces enregistrements, elles se bloqueront jusqu'à ce que les verrous S sur ces enregistrements soient libérés une fois la transaction en cours validée

2️⃣ SELECT FROM table FOR UPDATE

Si la transaction en cours s'exécute Si cette instruction est saisie, elle ajoutera des verrous X aux enregistrements lus. Cela ne permettra pas à d'autres transactions d'obtenir les verrous S de ces enregistrements (par exemple, d'autres transactions utilisent SELECT... LOCK IN SHARE MODE pour lire ces enregistrements), et il n'est pas non plus permis d'obtenir le verrou X de ces enregistrements (par exemple, utiliser l'instruction <code>SELECT ... FOR UPDATE pour lire ces enregistrements, ou modifier directement ces enregistrements).

Si d'autres transactions souhaitent acquérir le verrou S ou le verrou X de ces enregistrements, elles se bloqueront jusqu'à ce que le verrou X sur ces enregistrements soit libéré après la validation de la transaction en cours. 4.4 Verrous pour les opérations d'écriture

Pour les opérations d'écriture MySQL, DELETE, UPDATE et INSERT sont couramment utilisés. Verrouillage implicite, verrouillage et déverrouillage automatiques.

1️⃣ DELETE

Le processus d'exécution d'une opération DELETE sur un enregistrement consiste d'abord à localiser l'enregistrement dans l'arborescence B+, puis à obtenir le verrou X de l'enregistrement, puis à effectuer l'opération de suppression de marque. On peut également considérer ce processus de localisation de la position de l'enregistrement à supprimer dans l'arborescence B+ comme une lecture verrouillée qui acquiert le verrou X.

2️⃣ INSERT

Normalement, l'opération d'insertion d'un nouvel enregistrement n'est pas verrouillée. InnoDB utilise un type de verrou implicite pour protéger cet enregistrement nouvellement inséré contre l'utilisation par d'autres avant que la transaction ne soit validée.

3️⃣ UPDATE

Il existe trois situations lors de l'exécution d'une opération UPDATE sur un enregistrement :

① Si la valeur clé de l'enregistrement n'a pas été modifiée et que l'espace de stockage occupé par la colonne mise à jour n'a pas changé avant et après la modification, puis localisez d'abord l'emplacement de cet enregistrement dans l'arborescence B+, puis obtenez le verrou X de l'enregistrement, et enfin effectuez les opérations de modification à l'emplacement de l'enregistrement d'origine. En fait, on peut aussi considérer ce processus de localisation de la position de l'enregistrement à modifier dans l'arbre B+ comme une lecture verrouillée pour obtenir le verrou X. ② Si la valeur clé de l'enregistrement n'a pas été modifiée et que l'espace de stockage occupé par au moins une colonne mise à jour a changé avant et après la modification, localisez d'abord la position de l'enregistrement dans l'arborescence B+, puis obtenez le X verrouillez l'enregistrement, supprimez complètement l'enregistrement (c'est-à-dire déplacez complètement l'enregistrement vers la liste des déchets) et enfin insérez un nouvel enregistrement. Ce processus de localisation de la position de l'enregistrement à modifier dans l'arborescence B+ est considéré comme une lecture verrouillée pour obtenir le verrou X. L'enregistrement nouvellement inséré est protégé par le verrou implicite fourni par l'opération INSERT.

③ Si la valeur clé de l'enregistrement est modifiée, cela équivaut à effectuer une opération DELETE sur l'enregistrement d'origine puis à effectuer une opération INSERT. L'opération de verrouillage doit être effectuée selon les règles de DELETE et INSERT.

PS : Pourquoi d'autres transactions peuvent-elles toujours être lues lorsque le verrou en écriture est verrouillé ?

Étant donné qu'InnoDB dispose d'un mécanisme MVCC (Multi-version Concurrency Control), les lectures d'instantanés peuvent être utilisées sans être bloquées.

4. Classification de la granularité du verrouillage

Qu'est-ce que la granularité du verrouillage

 ? La granularité du verrouillage fait référence à la portée de ce que vous souhaitez verrouiller.

Par exemple, si vous allez aux toilettes à la maison, il vous suffit de verrouiller la salle de bain. Vous n'avez pas besoin de verrouiller toute la maison pour empêcher les membres de la famille d'entrer. La salle de bain est votre granularité de verrouillage.

Qu'est-ce qu'une granularité de verrouillage raisonnable ?

En effet, la salle de bain ne sert pas seulement à aller aux toilettes, mais aussi à prendre une douche et à se laver les mains. Cela pose la question de l’optimisation de la granularité du verrouillage.

Lorsque vous prenez une douche dans la salle de bain, d'autres peuvent effectivement entrer et se laver les mains en même temps, à condition qu'ils soient isolés si les toilettes, la baignoire et le lavabo sont tous séparés et relativement indépendants (humides et secs). sont séparés), en fait La salle de bain peut être utilisée par trois personnes en même temps, mais bien sûr les trois personnes ne peuvent pas faire la même chose. Cela affine la granularité du verrouillage. Tant que vous fermez la porte de la salle de bain lorsque vous prenez une douche, d'autres peuvent toujours entrer et se laver les mains. Si les différentes zones fonctionnelles ne sont pas divisées et isolées lors de la conception initiale de la salle de bains, l’utilisation maximale des ressources de la salle de bains ne peut pas être obtenue.

De même, il existe également une granularité de verrouillage dans MySQL. Habituellement divisé en trois types,

verrous de ligne, verrous de table et verrous de page

. 🎜🎜4.1 Verrouillage de ligne🎜🎜Dans l'introduction des verrous partagés et des verrous exclusifs, ils sont en fait enregistrés pour une certaine ligne, ils peuvent donc également être appelés verrous de ligne. 🎜

Le verrouillage d'un enregistrement n'affecte que cet enregistrement, la granularité de verrouillage des verrous de ligne est donc la meilleure de MySQL. Le verrouillage par défaut du moteur de stockage InnoDB est le verrouillage des lignes.

Il présente les caractéristiques suivantes :

• La plus faible probabilité de conflit de verrouillage et une concurrence élevée

  Étant donné que la granularité des verrous de ligne est faible, la probabilité de conflit de ressources de verrouillage est également la plus petite, donc la probabilité de verrouillage le conflit est faible et la concurrence est élevée. Plus le sexe est élevé.

• Surcharge élevée et verrouillage lent

  Les verrous consomment beaucoup de performances. Imaginez, si vous verrouillez plusieurs éléments de données dans la base de données, cela occupera inévitablement beaucoup de ressources et vous devrez attendre le précédent. verrou à déverrouiller.

• Produira une impasse

  Pour ce qu'est une impasse, vous pouvez lire ci-dessous.

4.2 Verrouillage de table

Le verrouillage au niveau de la table est un verrou au niveau de la table, qui verrouillera la table entière Il peut très bien éviter les blocages et constitue également le plus grand mécanisme de verrouillage granulaire de MySQL.

Le verrou par défaut du moteur de stockage MyISAM est le verrouillage de table.

Il présente les caractéristiques suivantes :

• Faible surcharge et verrouillage rapide

  Puisqu'il verrouille la table entière, il doit être plus rapide que de verrouiller une seule donnée.

• Aucun blocage ne se produira

  La table entière est verrouillée. Les autres transactions ne peuvent pas du tout obtenir le verrou et, naturellement, aucun blocage ne se produira.

• La granularité du verrouillage est grande, la probabilité de conflit de verrouillage est élevée et la concurrence est faible

4.3 Verrouillage de page

Le verrouillage au niveau de la page est un niveau de verrouillage unique dans MySQL, qui n'est pas trouvé dans d'autres logiciels de gestion de bases de données.

La granularité des verrous au niveau de la page se situe entre les verrous au niveau de la ligne et les verrous au niveau de la table, de sorte que la surcharge de ressources requise pour obtenir les verrous et les capacités de traitement simultané qu'ils peuvent fournir se situent également entre les deux ci-dessus. De plus, les verrous au niveau de la page, comme les verrous au niveau des lignes, peuvent provoquer des blocages.

GrandOui

Verrouillage de ligne

Verrouillage de table

Verrouillage de page

Verrouillage de la granularité

Petit

Grand

Entre les deux

Efficacité de verrouillage

Lent

Rapide

Probabilité de conflit entre les deux ance aérien

Petit

Entre le deux

Est-ce une impasse

Non

Oui

5. 算法实现分类

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换句话说即使对同一条记录加行锁，如果类型不同，起到的功效也是不同的。通常有以下几种常用的行锁类型。

5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

CREATE TABLE test (
    id INT (1) NOT NULL AUTO_INCREMENT,
    number INT (1) NOT NULL COMMENT '数字',
    PRIMARY KEY (id),
    KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下数据
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

如果此时在开启一个事务 B 进行插入数据，如下：

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

结果如下：

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

默认情况下，InnoDB 以 REPEATABLE READ 隔离级别运行。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

6. 乐观锁和悲观锁

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

6.1 乐观锁

所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。

实现乐观锁常见的方式

常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。

在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。

6.2 悲观锁

所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。

悲观锁的实现方式有两种

共享锁（读锁）和排它锁（写锁），参考上面。

7. 死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。

产生的条件

• 互斥条件：一个资源每次只能被一个进程使用；
• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
• 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；
• 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

MySQL 中其实也是一样的，如下还是这样一张表：

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `age` varchar(10) DEFAULT NULL COMMENT '年龄',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

按照如下顺序执行：

	A	B
①	BEGIN
②		BEGIN
③	SELECT * FROM user WHERE name='a' FOR UPDATE
④		SELECT * FROM user WHERE name='b' FOR UPDATE
⑤	SELECT * FROM user WHERE name='b' FOR UPDATE
⑥		SELECT * FROM user WHERE name='a' FOR UPDATE

1、开启 A、B 两个事务；

2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；

3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；

4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。

此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。

如何避免

从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。

【相关推荐：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!