Quels sont les verrous partagés, les verrous exclusifs et les blocages d'intention de MySQL ?

1. Verrous au niveau de la table InnoDB

Les verrous au niveau des lignes doivent généralement être utilisés pour garantir l'intégrité des transactions, et c'est également l'une des raisons courantes de choisir le moteur InnoDB. Cependant, dans des cas individuels, des verrous au niveau de la table peuvent également être requis.

La transaction doit mettre à jour la plupart ou la totalité des données, et la table est relativement volumineuse. Si le verrouillage de ligne par défaut est utilisé, non seulement l'efficacité de l'exécution de la transaction sera faible, mais cela peut également entraîner l'attente d'autres transactions. longtemps et conflits de verrouillage. La transaction implique plusieurs tables, ce qui est plus compliqué, cela est susceptible de provoquer un blocage et de provoquer un grand nombre d'annulations de transactions

Lorsque nous voulons obtenir un verrouillage de table, exécutez la commande suivante :

Lors de l'utilisation des verrous de table, les enjeux d'efficacité impliqués :
Pour obtenir un verrou de table Pour utiliser un verrou partagé S ou un verrou exclusif X sur une table, vous devez au préalable vous assurer que cette table n'a pas été acquise par d'autres transactions.

Supposons que nous ayons 10 millions de données dans ce tableau, comment déterminer quelles lignes de ces 10 millions de données ont X verrous ?
Si vous souhaitez obtenir le verrou S de la table, vous devez déterminer quelles lignes du tableau ont des verrous X. Si certaines lignes ont des verrous X, vous ne pouvez pas obtenir le verrou S ou le verrou X de cette table. Il n'y a pas de meilleur moyen que de vérifier une par une, ce qui conduit à une inefficacité. L'inefficacité est causée par la nécessité d'ajouter des verrous de table et de parcourir les données une par une pour déterminer si certaines données ont des verrous de ligne. Le verrouillage partagé d'intention et le verrouillage exclusif d'intention que nous apprenons ici peuvent être résolus

Lorsque vous souhaitez obtenir le verrou X de la table, vous n'avez pas besoin de vérifier par quels verrous de ligne de la table sont occupés par (X ou S). . Il vous suffit de vérifier rapidement IX et IS. Verrouillez-le simplement

. 2. Verrouillage partagé d'intention et verrouillage exclusif d'intention

• Verrouillage partagé d'intention (verrouillage IS) :

  Le plan de transaction ajoute un verrou partagé de ligne à l'enregistrement. La transaction doit d'abord obtenir la table avant d'ajouter un verrou partagé à l'enregistrement. une rangée d'enregistrements. Verrouillage IS

• Verrou exclusif d'intention (verrouillage IX) :

  Le plan de transaction ajoute un verrou exclusif de ligne à l'enregistrement. Avant que la transaction n'ajoute un verrou exclusif à une ligne d'enregistrements, la transaction doit d'abord être effectuée. obtenir le verrou IX de la table

• Avant d'ajouter le verrou de ligne, le moteur de stockage InnoDB ajoute le verrou IS ou IX de la table

Les verrous d'intention sont tous
• compatibles

  et ne provoquera pas de conflits. Il s'agit principalement d'aider les autres dans le processus. Accélérer l'efficacité lors de l'acquisition des verrous de table

Le but des verrous d'intention est d'acquérir les verrous de table plus efficacement (X et S dans le tableau font référence à
• ). verrous de table

  , pas de verrous de ligne !)

Verrous d'intention Il s'agit d'un
• verrouillage au niveau de la table

  , coordonne la coexistence des verrous de table et des verrous de ligne. L'objectif principal est de montrer qu'une transaction verrouille une ligne ou tente de verrouiller une ligne.

Analysez la transaction 1 pour acquérir le verrou de la ligne X et la transaction 2 pour acquérir le verrou de la table S :

Lorsque la transaction 1 doit ajouter un verrou Got the IX. Lorsque la transaction 2 veut acquérir le verrou S de la table entière, elle voit qu'une autre transaction a acquis le verrou IX sur cette table, ce qui signifie qu'il doit y avoir des données dans cette table qui ont été ajoutées au verrou X, ce qui entraîne dans la transaction 2, vous ne pouvez pas ajouter le verrou S à la table entière. Pour le moment, la transaction 2 ne peut qu'attendre et ne peut pas obtenir le verrouillage de la table S

3 Deadlock

1. Le verrouillage de la table MyISAM est sans blocage

MyISAM ne prend pas en charge les transactions, uniquement. les transactions. Les verrous de table obtiennent toujours tous les verrous nécessaires en même temps, soit les satisfaire tous, soit attendre, il n'y aura donc pas d'impasse

. Cependant, dans InnoDB, à l'exception des transactions composées d'un seul SQL, les verrous sont acquis progressivement, c'est-à-dire que la granularité du verrouillage est relativement faible, ce qui détermine qu'un blocage est possible dans InnoDB. Bien entendu, si plusieurs tables sont traitées, un blocage peut toujours se produire.

Les problèmes de blocage sont généralement causés par nous-mêmes. Semblables à la situation de blocage dans la programmation multi-thread, la plupart d'entre eux sont causés par l'ordre différent dans lequel nos multiples threads acquièrent plusieurs ressources de verrouillage. Par conséquent, lorsque nous utilisons différents segments de code pour mettre à jour plusieurs tables de la base de données, ces tables doivent être mises à jour dans le même ordre pour éviter que les conflits de verrouillage ne provoquent des problèmes de blocage. 2. Scénarios de blocage et solutions

Les scénarios dans lesquels un blocage se produit sont les suivants :

La transaction 1 acquiert avec succès le verrou de ligne 1La transaction 2 acquiert avec succès le verrou de ligne 2…

La transaction 1 ne peut pas acquérir le verrou de ligne 2, Lorsqu'il est bloqué, il n'y a aucun moyen d'exécuter une validation/annulation et le verrou de ligne 1 ne peut pas être libéré

La transaction 2 ne peut pas acquérir le verrou de ligne 1. Lorsqu'elle est bloquée, il n'y a aucun moyen d'exécuter une validation/annulation et le verrou de ligne 2 ne peut pas être libéré

Toutes les transactions sont bloquées signifie que tous les threads du processus sont bloqués, provoquant un problème de blocage.

Méthodes pour résoudre les blocages : Lorsque plusieurs transactions/threads acquièrent plusieurs verrous de ressources identiques, elles doivent acquérir des verrous de ressources dans le même ordre.

La transaction est bloquée ou bloquée. Mysqld (démon MySQL Server) est défini avec un délai d'attente pour le blocage de la transaction La transaction ne sera pas bloquée pendant une longue période. Après le délai d'attente, le traitement de la transaction échoue et est actuellement occupé. le verrou est automatiquement libéré.

3.Opération

Définissez les niveaux d'isolement de validation manuelle et de lecture répétable et démarrez la transaction

Interrogez les données de la table Dans le niveau d'isolement de lecture répétable, l'instantané lu fourni par MVCC est utilisé, et il n'y a pas de données. verrouillage

La transaction 1 acquiert le verrou exclusif avec id=7, la transaction 2 acquiert le verrou exclusif avec id=8

La transaction 1 acquiert à nouveau le verrou exclusif avec id=8 et le blocage se produit

La transaction 2 l'acquiert à nouveau. Le verrou exclusif avec l'identifiant = 7 est bloqué

À ce moment, le serveur MySQL détecte qu'un blocage s'est produit, il débloque donc la transaction 1, annule la transaction 1 et libère le verrou de ligne. il tient, donc transaction 2 Vous avez obtenu avec succès le verrou exclusif avec id=7

3. Suggestions d'optimisation du verrouillage

• En partant du principe que l'entreprise peut être complétée correctement, afin d'assurer l'efficacité, essayez d'utiliser un isolement inférieur niveau (les lectures sales doivent être évitées)

• Concevez des index raisonnables et essayez d'utiliser des index pour accéder aux données pour rendre le verrouillage plus précis, réduire les risques de conflits de verrouillage et améliorer les capacités de concurrence

• Choisissez un niveau raisonnable la taille de la transaction et la probabilité de conflits de verrouillage pour les petites transactions est faible (plus la transaction est grande, plus elle contient de SQL et elle peut contenir plus de verrous de ressources de table et de ressources de ligne, augmentant la probabilité de conflits de verrouillage) Lorsque différents programmes accèdent à un groupe de tables, ils doivent essayer de se mettre d'accord pour accéder à chaque table dans le même ordre, pour une table, accéder autant que possible aux lignes du tableau dans un ordre fixe. Cela peut réduire considérablement le risque de blocage

• Essayez d'utiliser des conditions égales pour accéder aux données. Cela peutéviter l'impact des verrous d'espacement sur l'insertion simultanée (en fait, les requêtes équivalentes ajouteront également des verrous d'espacement). pour un niveau de verrouillage qui dépasse le besoin réel.

• Si cela n'est pas nécessaire, vous devez éviter d'utiliser le verrouillage explicite lors de l'interrogation, car sous les niveaux d'isolement de lecture validée et de lecture répétable, MVCC a fourni un mécanisme de lecture sans verrouillage manuel

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!