Comment utiliser les verrous de table MySQL, les verrous de ligne, les verrous exclusifs et les verrous partagés

1. Sélection du mécanisme d'isolation des transactions

• Si on s'en fiche tous, utilisez le mécanisme d'isolation des transactions # 🎜🎜#Lecture non validée, si ces threads sont autorisés à faire fonctionner la base de données simultanément, des lectures sales (les données non validées sont lues), des lectures non répétables (deux valeurs de requête sont différentes) , Des problèmes tels que la lecture fantôme (différentes quantités de données interrogées deux fois), les données ont la sécurité la plus faible, l'avantage est que l'efficacité de la concurrence est très élevée, généralement non utilisée #🎜🎜 #

Si nous
• sérialisons

  (implémenté par verrouillage), toutes les transactions sont triées via des verrous. Bien que la sécurité des données soit améliorée, l'efficacité de la concurrence sera réduite. trop faible, et nous n'utilisons généralement pas

, nous utilisons donc généralement
• Lecture soumise, lecture répétable

  Ces deux les niveaux d'isolement équilibrent la sécurité des données, la cohérence et l'efficacité de la concurrence et sont implémentés par le contrôle de concurrence multi-version MVCC (MVCC est le principe de lecture validée et de lecture répétable, et le verrouillage est le principe de sérialisation) #🎜 🎜#

  2. Verrouillage au niveau de la table et verrouillage au niveau de la ligne

Verrouillage au niveau de la table : verrouillez toute la table. La surcharge est faible (car vous n'avez pas besoin de trouver l'enregistrement d'une certaine ligne dans la table pour la verrouiller. Si vous souhaitez modifier cette table, vous demandez directement le verrouillage de cette table), le verrouillage est rapide, et il n'y aura pas de blocage ; la granularité du verrouillage est grande et des conflits de verrouillage se produiront. La probabilité est élevée et la concurrence est faible.

Verrouillage au niveau de la ligne : verrouillez une ligne d'enregistrements. C'est cher (vous devez trouver l'enregistrement correspondant dans la table, et il y a un processus de recherche dans la table et l'index), le verrouillage est lent et un blocage se produira, la granularité du verrouillage est la plus petite, la probabilité de conflit de verrouillage est ; le plus bas et la concurrence est élevée

#🎜🎜 #

Le moteur de stockage InnoDB prend en charge le traitement des transactions, les tables prennent en charge le verrouillage au niveau des lignes et de meilleures capacités de concurrence

Les verrous de ligne InnoDB sont implémentés en verrouillant les entrées d'index sur l'index, plutôt que de verrouiller les enregistrements de ligne dans la table. Cela signifie qu'InnoDB utilise des verrous au niveau de la ligne uniquement lorsque les données. est récupéré via les conditions d'index. , sinon InnoDB utilisera le verrouillage de table

1. Étant donné que l'implémentation du verrouillage de ligne d'InnoDB est un verrou ajouté pour le champ d'index, pas un verrou ajouté pour la ligne. enregistrement, donc bien que l'accès soit à différentes lignes de la table sous le moteur InnoDB, mais si le même champ d'index est utilisé comme condition de filtre, des conflits de verrouillage se produiront toujours uniquement en série, pas simultanément#. 🎜🎜#

Même si des index sont utilisés en SQL, d'après l'optimiseur de MySQL,
2. si un scan complet de table est considéré comme plus efficace que l'utilisation d'un index, l'index sera abandonné à ce moment, donc il ne
   # 🎜🎜#Utiliser des verrous de ligne au lieu de verrous de table
Par exemple, pour certaines très petites tables, MySQL n'utilisera pas d'index

3. . 3. Verrouillage exclusif (Exclusif) et verrouillage partagé (Partagé)
   

   Verrouillage exclusif, également connu sous le nom de verrouillage X, verrouillage en écriture#🎜 🎜#
#🎜 🎜#

Verrou partagé, également connu sous le nom de verrouillage S, verrouillage en lecture

• La lecture (SS) est compatible, mais la lecture et l'écriture (SX) , SX) et l'écriture (XX) s'excluent mutuellement #

  Une transaction ajoute un verrou S à l'objet de données A. Elle peut effectuer des opérations de lecture sur A mais ne peut pas effectuer d'opérations de mise à jour pendant le verrouillage. période, d'autres transactions peuvent ajouter des verrous S à A mais ne peuvent pas ajouter des verrous X

  # 🎜🎜#
• Si une transaction ajoute un #
  Afficher le verrouillage : sélectionnez &hellip ; verrouiller en mode partage pour forcer un verrou partagé, sélectionnez … pour mettre à jour pour obtenir un verrou exclusif

1 La compatibilité de ses verrous et verrous partagés

#. 🎜🎜#Vérifions d'abord le SQL et le contenu de la table

• Voir le niveau d'isolement : #🎜 🎜#

• Ouvrez d'abord une transaction et ajoutez un verrou exclusif aux données avec id=7

Ouverture de la transaction avec un autre client

Nous utilisons le fil de service d'une autre transaction pour ajouter de l'exclusivité aux données avec id=7 Lock, bloqué

# 🎜🎜#

Nous avons essayé d'ajouter un verrou partagé aux données avec l'id=7, mais il était toujours bloqué

Résumé : Pour les verrous de données entre différentes transactions, uniquement SS les verrous peuvent coexister. XX, SX et XS ne peuvent pas coexister 🎜🎜#Le verrou d'exécution est ajouté à l'arborescence d'index

.

Utiliser les champs non indexés du tableau comme conditions de filtrage

#🎜 🎜#Transaction 2 veut maintenant également obtenir le verrouillage exclusif de cet enregistrement, mais a échoué comme prévu ; maintenant la transaction 2 obtient le verrouillage exclusif de l'enregistrement de chenwei, essayez de voir si elle peut réussir

#🎜🎜 #

InnoDB prend en charge les verrous de ligne. Lorsque l'identifiant de clé primaire a été utilisé comme condition de filtre tout à l'heure, la transaction 1 et la transaction 2 peuvent acquérir avec succès des verrous pour différentes lignes. Cependant, nous constatons maintenant que nous ne pouvons pas obtenir le verrou exclusif nommé Chenwei. Pourquoi ? Expliquons :

Le verrouillage des lignes d'InnoDB est implémenté en verrouillant les entrées d'index, plutôt que en verrouillant les enregistrements de lignes de la table

Puisque nous utilisons le nom comme filtre condition sans utiliser d'index, nous n'utiliserons naturellement pas de verrous de ligne, mais des verrous de table. Cela signifie qu'InnoDB utilise des verrous au niveau des lignes uniquement lorsque les données sont récupérées via l'index, sinon InnoDB utilisera des verrous de table !!!

Nous ajoutons un index au champ de nom

#🎜🎜 #

Nous avons constaté qu'après avoir ajouté un index au nom, deux transactions peuvent obtenir des verrous exclusifs sur des lignes différentes (pour mise à jour), une fois prouve encore une fois que le verrou de ligne d'InnoDB est ajouté à l'élément d'index

Parce que maintenant le nom est dans l'index, via zhangsan dans l'arborescence d'index auxiliaire Trouvez l'identifiant de l'enregistrement de ligne où il se trouve est 7, puis allez dans l'arborescence d'index de clé primaire et obtenez le verrou exclusif de l'enregistrement de ligne correspondant

(Je suppose personnellement que les enregistrements correspondants dans l'index auxiliaire l'arbre et l'arbre d'index de clé primaire sont ajoutés au verrou)

4. Test de niveau d'isolement de sérialisation

(toutes les transactions utilisent des verrous exclusifs ou des verrous partagés, pas besoin de verrouiller manuellement les utilisateurs) #🎜🎜 #

Définir le niveau d'isolement de sérialisation

Deux transactions peuvent acquérir des verrous partagés en même temps (coexistence SS)

#🎜🎜 #

Maintenant, laissez la transaction 2 insérer des données

À ce moment, des lignes supplémentaires sont nécessaires en raison de l'insertion. Il verrouille, mais comme la transaction 1 a ajouté un verrou partagé à l'ensemble de la table, la transaction 2 ne peut plus verrouiller la table avec succès (SX ne coexiste pas)

rollback#🎜 🎜## 🎜🎜#

Parce que nous avons ajouté un index au nom, la sélection ci-dessus équivaut à ajouter un verrou partagé de ligne aux données nommées zhangsan

Mise à jour de la transaction 2#🎜 🎜#

La transaction 2 ne peut pas être mise à jour car la table entière a été verrouillée par le verrou partagé de la transaction 1 à ce moment

#🎜 🎜#Transaction 2 est dans l'auxiliaire Recherchez zhangsan dans l'arborescence d'index, recherchez la valeur de clé primaire correspondante, puis accédez à l'arborescence d'index de clé primaire pour trouver l'enregistrement correspondant, mais constatez que cette ligne d'enregistrements a été verrouillée par un partage lock. La transaction 2 peut obtenir le verrou partagé, mais ne peut pas obtenir le verrou exclusif#🎜 🎜#

Essayons d'utiliser l'identifiant d'index de clé primaire pour voir si la mise à jour#🎜 🎜#

est toujours bloqué Wow, bien que le champ derrière notre où utilise désormais l'identifiant au lieu du nom, le nom trouve également la clé primaire correspondante via l'arborescence d'index auxiliaire, et trouve ensuite l'enregistrement correspondant sur l'arbre d'index de clé primaire,

Et sur l'arbre d'index de clé primaire L'enregistrement est verrouillé

(Je suppose personnellement que les données correspondant à l'arbre d'index auxiliaire et à l'index de clé primaire les arbres sont verrouillés)

Nous avons mis à jour les données avec id=8 et avons réussi. Seuls les verrous de ligne sont ajoutés aux données de ligne avec l'ID 7, afin que nous puissions exploiter avec succès les données avec l'ID 8

S'il existe un index, utilisez le verrouillage de ligne sans index, le verrouillage de table est utilisé.

Les verrous au niveau de la table ou les verrous au niveau des lignes font référence à la granularité du verrou. Les verrous partagés et les verrous exclusifs font référence à la nature du verrou. Qu'il s'agisse d'un verrou de table ou d'un verrou de ligne, il existe une distinction entre. serrures partagées et serrures exclusives#🎜🎜 #

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!