Introduction au verrouillage innoDB dans MySQL-tutoriel mysql-php.cn

Pourquoi devez-vous démarrer la transaction avant de verrouiller InnoDB ?

Le verrou dans innodb est libéré une fois la transaction validée/annulée. Une fois la transaction validée/annulée, le verrou de la transaction sera activé. être automatiquement libéré. Par défaut, innodb autocommit=1 active la soumission automatique

La différence entre les verrous utilisant des index et ceux qui n'utilisent pas d'index pour les conditions de récupération :

Lorsque les conditions de récupération ont des index, des lignes spécifiques seront être verrouillé.

Si la condition de recherche n'est pas utilisée, une analyse complète de la table sera effectuée pour verrouiller toutes les lignes (y compris les enregistrements inexistants)

Verrouillage en lecture :

Le verrouillage en lecture est Partagé ou non bloquant. Plusieurs utilisateurs peuvent lire la même ressource en même temps sans interférer les uns avec les autres.

Verrouillage en écriture :

Le verrouillage en écriture est exclusif, ce qui signifie qu'un verrou en écriture bloquera les autres verrous en écriture et en lecture. De plus, les verrous en écriture ont une priorité plus élevée que les verrous en lecture, donc une demande de verrou en écriture peut être insérée devant la file d'attente des verrous en lecture, mais le verrou en lecture ne peut pas être inséré devant le verrou en écriture

Verrouillage de table :

InnoDB dispose également de deux verrous de table : le verrouillage partagé d'intention (IS), le verrouillage exclusif d'intention (IX)

verrouillage de ligne :

　InnoDB implémente deux types de verrous au niveau des lignes, les verrous partagés et les verrous exclusifs

Optimiste lock :

Le verrouillage optimiste, également appelé contrôle de concurrence optimiste, suppose que les transactions simultanées multi-utilisateurs ne s'affecteront pas pendant le traitement et que chaque transaction peut gérer une partie de son impact sans générer de données de verrouillage. Avant de valider les mises à jour des données, chaque transaction vérifiera d'abord si d'autres transactions ont modifié les données après que la transaction ait lu les données. S'il existe des mises à jour provenant d'autres transactions, la transaction en cours de validation sera annulée.

Verrouillage pessimiste :

Verrouillage pessimiste, également appelé contrôle de concurrence pessimiste, lorsque la transaction A applique un verrou sur une certaine ligne de données, et lorsque cette transaction libère le verrou, d'autres transactions peuvent s'exécuter et L'opération de conflit de verrouillage, le verrouillage imposé par la transaction A est ici appelé verrouillage pessimiste. Les verrous partagés et les verrous exclusifs (verrous de rangée, verrous d'espacement, verrous de clé suivante) sont tous deux des verrous pessimistes

La mise en œuvre de verrous pessimistes et de verrous optimistes :

La mise en œuvre de verrous pessimistes repose sur le base de données Le mécanisme de verrouillage fourni est implémenté, tel que select * from news où id=12 pour la mise à jour, tandis que le verrouillage optimiste repose sur l'enregistrement de la version des données, c'est-à-dire en ajoutant le champ du numéro de version à la table comme facteur clé pour savoir si la la soumission peut être réussie.

Verrou partagé (S) :

Le verrouillage partagé est également appelé verrouillage en lecture. Si une transaction acquiert un verrou partagé d'une ligne de données, d'autres transactions peuvent le faire. acquérir le verrou partagé correspondant à la ligne, mais le verrou exclusif ne peut pas être obtenu, c'est-à-dire que lorsqu'une transaction lit une ligne de données, d'autres transactions peuvent également lire, mais ne peuvent pas ajouter, supprimer ou modifier la ligne de données

Définir le verrouillage partagé : SELECT .... VERROUILLER EN MODE PARTAGE ;

Verrouillage exclusif (X) :

Le verrouillage exclusif est également appelé verrouillage en écriture si une transaction acquiert un verrouillage exclusif. une ligne de données, les autres transactions ne pourront pas être acquises. Aucun autre verrou (serrures exclusives ou verrous partagés) sur la ligne ne peut être acquis, c'est-à-dire que lorsqu'une transaction lit une ligne de données, les autres transactions ne peuvent pas ajouter, supprimer, modifier ou vérifier la ligne de données

Définition d'un verrouillage exclusif : SELECT .... POUR LA MISE À JOUR

　Remarque :

Pour sélectionner , innodb n'ajoutera aucun verrou, c'est-à-dire que plusieurs opérations de sélection peuvent être effectuées simultanément sans aucun conflit de verrouillage car il n'y a aucun verrou du tout.
Pour les opérations d'insertion, de mise à jour et de suppression, innodb ajoutera automatiquement des verrous exclusifs aux données impliquées. Seule la sélection de requête nous oblige à définir manuellement le verrou exclusif.

Intention Shared Lock (IS) :

Informez la base de données des verrous qui doivent être appliqués ensuite et verrouillez la table. Si vous devez ajouter un verrou partagé à l'enregistrement A, innodb trouvera d'abord cette table, ajoutera un verrou partagé d'intention à la table, puis ajoutera un verrou partagé à l'enregistrement A. C'est-à-dire qu'avant d'ajouter un verrou partagé à une ligne de données, il faut d'abord obtenir le verrou IS de la table

intention de verrouillage exclusif (IX) :

Notifier la base de données de quel verrou doit être appliqué ensuite et ajouter le verrou de table. Si vous devez ajouter un verrou exclusif à l'enregistrement A, innodb trouvera d'abord cette table, ajoutera un verrou exclusif intentionnel à la table, puis ajoutera un verrou partagé à l'enregistrement A. C'est-à-dire qu'avant d'ajouter un verrou exclusif à une ligne de données, il faut d'abord obtenir le verrou IX de la table

La différence entre le verrou partagé et le verrou partagé d'intention, le verrou exclusif et le verrou exclusif d'intention :

Verrous partagés et verrous exclusifs, le système ajoutera automatiquement des verrous partagés ou des verrous exclusifs sous certaines conditions, ou vous pouvez ajouter manuellement des verrous partagés ou des verrous exclusifs.
Les verrous partagés d'intention et les verrous exclusifs d'intention sont automatiquement ajoutés et automatiquement libérés par le système, et l'ensemble du processus ne nécessite aucune intervention manuelle.
Les verrous partagés et les verrous exclusifs sont à la fois des enregistrements de lignes de verrous, ainsi que des verrous partagés d'intention et des verrous exclusifs d'intention.

Méthode d'implémentation du verrouillage :

Dans MySQL, les verrous au niveau des lignes ne verrouillent pas directement les enregistrements, mais verrouillent les index. Les index sont divisés en index de clé primaire et index de clé non primaire. Si une instruction SQL opère sur l'index de clé primaire, MySQL verrouillera l'index de clé primaire ; verrouillez d'abord l'index de clé non primaire, puis verrouillez l'index de clé primaire correspondant .

Les verrous de ligne InnoDB sont implémentés en verrouillant les entrées d'index. S'il n'y a pas d'index, InnoDB verrouillera les enregistrements via un index cluster caché. C'est-à-dire : si les données ne sont pas récupérées via les conditions d'index, alors InnoDB verrouillera toutes les données de la table, et l'effet réel est le même que le verrouillage de la table

Les verrous de ligne sont divisés en trois situations :

Verrouillage d'enregistrement : verrouillez l'élément d'index, c'est-à-dire verrouillez un enregistrement.

Gap Lock : verrouillez "l'écart" entre les éléments d'index, l'écart avant le premier enregistrement ou l'écart après le dernier enregistrement, c'est-à-dire verrouillez une plage d'enregistrements, à l'exclusion de l'enregistrement lui-même

Verrouillage à clé suivante : verrouillez une plage d'enregistrements et incluez l'enregistrement lui-même (une combinaison des deux ci-dessus)

Remarque : Le niveau par défaut d'InnoDB est à lecture répétable (lecture répétée) niveau. La norme ANSI/IOS SQL définit 4 niveaux d'isolation des transactions : lecture non validée, lecture validée, lecture répétable, sérialisable

Gap Lock et La différence entre le verrouillage à clé suivante :

　Suivant- Key Lock est une combinaison de verrouillage de ligne et de verrouillage d'espacement. De cette façon, lorsqu'InnoDB analyse l'enregistrement d'index, il ajoutera d'abord un verrou de ligne à l'enregistrement d'index sélectionné (Record Lock), puis ajoutera un verrouillage d'espacement (Gap Lock) à. les espaces des deux côtés de l’enregistrement d’index. Si un espace est verrouillé par la transaction T1, les autres transactions ne peuvent pas insérer d'enregistrements dans cet espace.

Les verrous de ligne empêchent la modification ou la suppression d'autres transactions, les verrous Gap empêchent l'ajout d'autres transactions et le verrou Next-Key formé par la combinaison des verrous de ligne et des verrous GAP résout conjointement le problème du secteur RR problème de lecture fantôme lors de l'écriture des données.

Quand utiliser les verrous de table dans InnoDB :

InnoDB utilisera des verrous au niveau des lignes dans la plupart des cas en raison des verrous de transaction et de ligne. C'est souvent la raison pour laquelle nous choisissons InnoDB, mais dans certains cas, nous envisageons également d'utiliser des verrous au niveau de la table

Lorsque la transaction doit mettre à jour la plupart des données, le La table est relativement volumineuse, si le verrou de ligne par défaut est utilisé, il est non seulement inefficace, mais provoque également facilement une longue attente d'autres transactions et des conflits de verrouillage.
Les transactions sont complexes et peuvent provoquer des blocages et des annulations.

Sous InnoDB, vous devez faire attention aux deux points suivants lorsque vous utilisez les verrous de table.

(1) Bien que vous puissiez ajouter des verrous au niveau de la table à InnoDB à l'aide de LOCK TALBES, il faut noter que les verrous de table ne sont pas gérés par la couche du moteur de stockage InnoDB , mais par La couche supérieure MySQL Server est responsable de Uniquement lorsque autocommit=0, innodb_table_lock=1 (paramètre par défaut), la couche InnoDB peut connaître le verrou de table ajouté par MySQL et MySQL Server peut détecter le verrou de ligne ajouté par InnoDB. Dans ce cas, ce n'est que dans cette condition qu'InnoDB peut identifier automatiquement les blocages impliquant des verrous au niveau de la table ; sinon, InnoDB ne sera pas en mesure de détecter et de gérer automatiquement de tels blocages.

(2) Lorsque vous utilisez LOCAK TABLES pour verrouiller InnoDB, veillez à définir AUTOCOMMIT sur 0, sinon MySQL ne verrouillera pas la table avant la fin de la transaction, n'utilisez pas UNLOCAK TABLES pour libérer le verrou de la table ; , car UNLOCK TABLES validera implicitement la transaction ; COMMIT ou ROLLBACK ne peuvent pas libérer le verrou au niveau de la table ajouté avec LOCAK TABLES. Le verrou de table doit être libéré avec UNLOCK TABLES. La méthode correcte est la suivante :

Par exemple. : Si vous devez écrire la table t1 et lire

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and here];COMMIT;
UNLOCK TABLES;

Copier après la connexion

à partir de la table t Deadlock :

Nous avons dit que l'impasse ne se produira pas dans MyISAM, parce que MyISAM toujours Il s'agit d'obtenir tous les verrous nécessaires à la fois, soit pour tous les satisfaire, soit pour les attendre tous. Dans InnoDB, les verrous sont acquis progressivement, entraînant la possibilité d'un blocage.

Après un blocage, InnoDB peut généralement le détecter et faire en sorte qu'une transaction libère le verrou et annule, et qu'une autre acquière le verrou pour terminer la transaction. Cependant, InnoDB ne peut pas détecter entièrement automatiquement les blocages lorsque des verrous ou des verrous externes sont impliqués. Cela doit être résolu en définissant le paramètre de délai d'attente de verrouillage innodb_lock_wait_timeout. Il convient de noter que ce paramètre n'est pas seulement utilisé pour résoudre le problème de blocage. Lorsque l'accès simultané est relativement élevé, si un grand nombre de transactions sont suspendues parce qu'elles ne peuvent pas obtenir immédiatement les verrous requis, cela occupera une grande quantité de ressources informatiques. et causer de sérieux problèmes de performances et même faire tomber la base de données. Nous pouvons éviter cette situation en définissant un seuil de délai d'attente de verrouillage approprié.

Il existe de nombreuses façons d'éviter les blocages. En voici trois courantes :

Si différents programmes accèdent à plusieurs tables simultanément, essayez d'accepter d'accéder aux tables dans le même ordre. ce qui peut réduire considérablement les risques de blocage. Si deux sessions accèdent aux deux tables dans un ordre différent, le risque de blocage est très élevé ! Mais si les accès sont effectués dans le même ordre, le blocage peut être évité.
Dans la même transaction, essayez de verrouiller toutes les ressources nécessaires en même temps pour réduire la probabilité de blocage.
Pour les parties métier très sujettes aux blocages, vous pouvez essayer de mettre à niveau la granularité du verrouillage et de réduire le risque de blocages grâce au verrouillage au niveau de la table.
Lorsque le programme traite les données par lots, si les données sont triées à l'avance pour garantir que chaque thread traite les enregistrements dans un ordre fixe, la possibilité d'un blocage peut également être considérablement réduite .
Sous le niveau d'isolement REPEATEABLE-READ, si deux threads utilisent SELECT...ROR UPDATE sur le même enregistrement conditionnel en même temps pour ajouter un verrou exclusif, si le l'enregistrement ne correspond pas au , les deux threads se verrouilleront avec succès. Le programme découvre que l'enregistrement n'existe pas encore et tente d'insérer un nouvel enregistrement. Si les deux threads le font, un blocage se produira. Dans ce cas, modifier le niveau d'isolement sur READ COMMITTED peut éviter le problème.
Lorsque le niveau d'isolement est READ COMMITED, si les deux threads exécutent d'abord SELECT...FOR UPDATE, déterminez s'il existe des enregistrements qui remplissent les conditions, et sinon, insérez les enregistrements. À ce stade, un seul thread peut s'insérer avec succès et l'autre thread attendra le verrou. Lorsque le premier thread sera soumis, le deuxième thread fera à nouveau une erreur en raison de la clé primaire, mais bien que ce thread fasse une erreur, il le fera. obtiendra un cadenas exclusif ! A ce moment, si un troisième thread vient demander un verrou exclusif, un blocage se produira également. Dans ce cas, vous pouvez effectuer directement l'opération d'insertion, puis intercepter l'exception de duplication de clé primaire, ou lorsque vous rencontrez l'erreur de duplication de clé primaire, toujours exécuter ROLLBACK pour libérer le verrou exclusif acquis

ps : Si un blocage se produit, vous pouvez utiliser la commande SHOW INNODB STATUS pour déterminer la cause du dernier blocage et les mesures d'amélioration.

Résumé :

Pour la table InnoDB, on retrouve principalement les points suivants

(1) Le marketing InnoDB est basé sur des index. Si les données ne sont pas accessibles via un index, InnoDB utilise des verrous de table.

(2) Mécanisme de verrouillage des écarts d'InnoDB et raison pour laquelle InnoDB utilise le verrouillage des écarts.

(3) Sous différents niveaux d'isolement, le mécanisme de verrouillage d'InnoDB et la stratégie de lecture cohérente sont différents.

(4) La récupération et la réplication MySQL ont également un grand impact sur le mécanisme de verrouillage InnoDB et la stratégie de lecture cohérente.

(5) Les conflits de verrouillage et même les blocages sont difficiles à éviter complètement.

Après avoir compris les caractéristiques de verrouillage d'InnoDB, les utilisateurs peuvent réduire les conflits de verrouillage et les blocages grâce à des ajustements de conception et SQL, notamment :

Essayez d'utiliser un niveau d'isolement inférieur
Concevez soigneusement l'index et essayez d'utiliser l'index pour accéder aux données afin de rendre le verrouillage plus précis et de réduire le risque de conflit de verrouillage.
Choisissez une taille de transaction raisonnable et la probabilité de conflits de verrouillage pour les petites transactions est plus petite.
Lorsque l'affichage verrouille l'ensemble d'enregistrements, il est préférable de demander un niveau de verrouillage suffisant en une seule fois. Par exemple, si vous souhaitez modifier des données, il est préférable de demander directement un verrou exclusif au lieu de demander d'abord un verrou partagé, puis de demander un verrou exclusif lors de la modification, ce qui peut facilement provoquer un blocage.
Lorsque différents programmes accèdent à un groupe de tables, ils doivent essayer de s'entendre pour accéder à chaque table dans le même ordre. Pour une table, essayez d'accéder aux lignes du tableau dans un ordre fixe. commande. . Cela peut réduire considérablement le risque de blocage.
Essayez d'utiliser des conditions d'égalité pour accéder aux données, afin d'éviter l'impact des verrouillages d'espacement sur l'insertion simultanée.
Ne demandez pas un niveau de verrouillage qui dépasse le besoin réel, sauf si cela est nécessaire, n'affichez pas le verrouillage lors de l'interrogation.
Pour certaines transactions spécifiques, les verrous de table peuvent être utilisés pour augmenter la vitesse de traitement ou réduire le risque de blocage.