Que sont les verrous au niveau de la table, les verrous au niveau de la ligne, les verrous exclusifs et les verrous partagés dans MySQL ?

Avant-propos

En raison de la complexité de notre activité, plusieurs instructions SQL associées qui composent une transaction sont nécessaires. Alors, expliquons d’abord ce qu’est une transaction. Une transaction fait référence à un groupe d'instructions SQL qui sont exécutées ensemble. Toutes doivent être exécutées avec succès, sinon toutes ont échoué. Une réussite ou un échec partiel n'est pas autorisé. Une transaction a des caractéristiques ACIDE :

• Atomicité : Soit tous réussissent, soit tous échouent, afin d'assurer la cohérence de la transaction

• Cohérence : Par exemple, un virement bancaire déduit l'argent d'une personne ; vous devez ajouter de l'argent à une autre personne, vous ne pouvez pas simplement le déduire sans l'ajouter, sinon il y aura des problèmes dans l'entreprise et la cohérence des données sera détruite

• Persistance : Après avoir validé les données, les données ; est d'abord écrit dans le cache Parmi eux, les données du cache mettent encore du temps à être écrites sur le disque. En cas de panne de courant, d'arrêt ou de redémarrage, nous avons un journal de rétablissement pour garantir la durabilité de la base de données ;

• Isolement :

  Cette section peut expliquer pourquoi les transactions doivent être isolées, car les transactions doivent pouvoir être exécutées simultanément. Une entreprise implique de nombreuses transactions, et nous avons souvent de nombreuses entreprises en arrière-plan. Nous devons pouvoir leur permettre de le faire. exécuter simultanément. Si toutes les transactions sont en série. Si l'exécution est correcte, alors si nous écrivons un programme multithread, un seul thread fera le travail, ce qui est très inefficace. Par conséquent, les transactions doivent être exécutées simultanément, mais l'exécution simultanée implique certains problèmes : Sécurité et cohérence des transactions et Problèmes d'efficacité de la concurrence Nous utilisons ces deux éléments comme points de référence pour obtenir les différents niveaux de concurrence/isolation de MySQL, si nous le faisons. ne pas s'isoler du tout lorsque les transactions sont exécutées simultanément, une lecture sale peut se produire (la transaction B lit les données non validées de la transaction A puis utilise les données non validées de la transaction A pour effectuer des calculs, et obtient beaucoup d'autres résultats, puis la transaction A annule ces données, alors les données calculées par la transaction B sont toutes des données problématiques, une lecture sale causera certainement des problèmes), lecture non répétable (supprime une donnée dans les mêmes conditions, puis quand j'interroge à nouveau, je constater que la valeur des données a changé. Bien entendu, une lecture non répétable ne pose pas nécessairement de problèmes. Elle est autorisée dans certains scénarios commerciaux. Cela dépend du fait que la sécurité et la cohérence des données commerciales sont strictes) et phantom. Lisez (le volume de données des résultats de deux requêtes avant et après les mêmes conditions sont différentes dans la transaction) ces problèmes.

Ensuite afin de résoudre les problèmes rencontrés dans l'exécution concurrente des transactions, nous donnons le niveau d'isolement de la transaction :

Sérialisation, la sérialisation est entièrement implémentée à l'aide de verrous, et toutes les transactions sont triées via des verrous L'exécution dans l'ordre offre une sécurité élevée des données mais une faible efficacité de concurrence. En général, nous ne le ferons pas.
Lecture non validée. Pour le programme multithread que nous avons écrit, il n'y a pas de contrôle de concurrence pour le segment de code de section critique. Bien que la concurrence soit élevée, la sécurité des données est très faible, ce qui permet également l'existence de lectures sales. , cela pose problème, les lectures non validées ne doivent jamais être utilisées.
• La sérialisation et la lecture non validée ne seront pas utilisées dans les projets réels. Généralement, les moteurs de base de données fonctionnent par défaut sur la lecture validée et la lecture répétable. Ces deux niveaux d'isolement combinent la sécurité et la cohérence des données avec l'efficacité de la concurrence des données, ces deux niveaux étant réalisés par le multi-MVCC. mécanisme de contrôle de concurrence de versions

  .

Lecture soumise, niveau de travail par défaut d'Oracle. La lecture de données non validées n'est pas autorisée. Ce niveau autorise toujours les lectures non répétables et les lectures virtuelles.
Lecture répétable, niveau de travail par défaut de MySQL. Il est garanti que les mêmes données seront toujours obtenues lors de la relecture de la transaction, ce qui résout partiellement la lecture virtuelle, mais la lecture virtuelle aura toujours lieu plus de performances sont dépensées pour éviter les conflits, c'est-à-dire que l'efficacité est faible.
Au niveau "lecture répétable", cela peut effectivement résoudre certains problèmes de lecture virtuelle, mais il ne peut pas empêcher les problèmes de lecture virtuelle causés par les mises à jour. Pour interdire l'apparition de lectures virtuelles, vous devez toujours définir le paramètre. niveau d'isolement de sérialisation.

Le principe de mise en œuvre du niveau d'isolement des transactions :

Lock + MVCC. Le principe de mise en œuvre sous-jacent de la sérialisation est celui des verrous. Les verrous comprennent les verrous partagés, les verrous exclusifs, les verrous partagés d'intention, les verrous exclusifs d'intention, les verrous d'espacement et les blocages. Le principe de mise en œuvre sous-jacent de la lecture validée et de la lecture répétable d'InnoDB : MVCC (contrôle de concurrence de versions multiples), MVCC fournit une méthode de lecture simultanée, y compris la lecture d'instantanés (les mêmes données auront plusieurs versions), la lecture en cours, l'annulation du journal et le rétablissement du journal.

MVCC est le principe de la lecture engagée et de la lecture répétable, et le verrouillage est le principe de la sérialisation

Le journal des transactions est utilisé pour implémenter les fonctionnalités ACID, tandis que les verrous partagés, les verrous exclusifs et MVCC sont utilisés pour implémenter les fonctionnalités de cohérence (I). Le journal des transactions est divisé en journal d'annulation (journal de restauration) et journal de rétablissement (journal de rétablissement)

1. 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, la probabilité de conflit de verrouillage est élevée et la concurrence est faible

• Verrouillage au niveau de la ligne :

  Verrouillez un enregistrement de ligne. Cela coûte cher (vous devez trouver les enregistrements correspondants dans la table, et il existe un processus de recherche dans la table et l'index), un verrouillage lent et des blocages se produiront, la granularité du verrouillage est la plus faible, la probabilité de conflits de verrouillage est la plus élevée ; le plus bas et la concurrence est élevée
Le moteur de stockage MyISAM ne prend en charge que les verrous au niveau de la table, tandis qu'InnoDB prend en charge le traitement des transactions, les verrous au niveau des lignes et de meilleures capacités de concurrence

2. Verrous exclusifs et verrous partagés

Verrouillage exclusif :

également connu sous le nom de verrouillage X, verrouillage en écriture

• Verrouillage 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), l'écriture et l'écriture (XX) s'excluent mutuellement

1. Testez la compatibilité des verrous exclusifs et des verrous partagés entre différentes transactions

Nous vérifions d'abord la table SQL et le contenu

Afficher le niveau d'isolement :

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

Ouvrez la transaction B sur un autre client :

Quel que soit le verrou exclusif ou le verrou partagé, l'id=7 est bloqué et ne peut pas être interrogé, car la transaction A a ajouté un verrou exclusif aux données de la ligne avec id=7, qui est un verrou en écriture, et d'autres ne sait ni lire ni écrire.

Résumé : Pour les verrous de données entre différentes transactions, seuls les verrous SS peuvent coexister, ainsi que XX, SX et sur l'arborescence d'index.

Chaque fois que vous terminez un test, annulez ce que vous venez de faire.

Utilisez le champ non indexé de la table comme condition de filtrage

Maintenant, la transaction 2 obtient les enregistrements de différentes lignes chenwei

InnoDB prend en charge les verrous de ligne lorsque l'identifiant de clé primaire a été utilisé comme filtrage. condition, la transaction 1 et la transaction 2 peuvent réussir à acquérir des verrous sur des lignes différentes. Cependant, nous constatons maintenant que nous ne pouvons pas obtenir le verrou exclusif nommé Chenwei. Pourquoi ? Expliquons :

Le verrouillage de ligne d'InnoDB est implémenté en verrouillant les éléments d'index, plutôt que de verrouiller les enregistrements de ligne de table

Et nous utilisons le nom comme condition de filtre sans utiliser l'index, donc naturellement les verrous de ligne ne sont pas utilisés, mais les verrous de table le sont utilisé. Cela signifie qu'InnoDB utilise uniquement des verrous au niveau des lignes pour récupérer des données via des index, sinon InnoDB utilisera des verrous de table !!!

Nous ajoutons un index au champ de nom :

Alors faites ce que nous venons de faire Opération :

Nous avons constaté qu'après avoir ajouté un index au nom, deux transactions peuvent obtenir des verrous exclusifs (pour mise à jour) sur des lignes différentes, ce qui prouve une fois de plus que les verrous de ligne d'InnoDB sont ajoutés aux éléments de l'index.

Parce que le nom est maintenant dans l'index, utilisez zhangsan pour trouver l'identifiant de l'enregistrement de ligne où il se trouve sur l'arborescence d'index auxiliaire, qui est 7, puis accédez à l'arborescence d'index de clé primaire pour obtenir l'exclusivité verrouillage de l'enregistrement de ligne correspondant (je suppose que c'est l'auxiliaire. Les enregistrements correspondants dans l'arborescence d'index et l'arborescence d'index de clé primaire sont verrouillés)

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

Toutes les transactions sérialisées utilisent des verrous partagés ou des verrous exclusifs, pas besoin de les ajouter manuellement. Select acquiert un verrou partagé et insert, delete et update acquièrent des verrous exclusifs.

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 ;

En raison pour insérer doit ajouter un verrou exclusif, mais comme la transaction 1 a ajouté un verrou partagé à la table entière, la transaction 2 ne peut plus verrouiller la table avec succès (sx ne coexiste pas)

restaurer et restaurer tous les états d'acquisition de verrou Abandonné :

Ouvrez deux transactions :

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

transaction 2update ;

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

La transaction 2 recherche zhangsan sur l'arborescence d'index auxiliaire, trouve la valeur de clé primaire correspondante, puis accède à la clé primaire. L'arbre d'index a trouvé l'enregistrement correspondant, mais a constaté que cette ligne d'enregistrements a été verrouillée par un verrou partagé. La transaction 2 peut obtenir le verrou partagé, mais ne peut pas obtenir le verrou exclusif

Utilisons l'index de clé primaire pour voir si l'identifiant peut être mis à jour

est toujours bloqué. Bien que le champ derrière lequel nous utilisons maintenant id au lieu de nom, nom trouve également la clé primaire correspondante via l'arborescence d'index auxiliaire, puis trouve l'enregistrement correspondant sur la clé primaire. arbre d'index et arbre d'index de clé primaire L'enregistrement est verrouillé

Nous avons mis à jour les données avec id=8 et cela a réussi Parce que lorsque nous avons sélectionné, nous avons uniquement ajouté un verrou de ligne aux données avec id=7, donc bien sûr. nous pouvons exploiter avec succès les données avec id=8

S'il y a un index, utilisez le verrouillage de ligne ; s'il n'y a pas d'index, utilisez le verrouillage de table

Qu'il s'agisse d'un verrouillage au niveau de la table ou d'un verrouillage au niveau de la ligne. fait référence à la granularité du verrou, le verrou partagé et le verrou exclusif 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 les verrous partagés et les verrous exclusifs.

La sérialisation utilise des verrous exclusifs. et les verrous partagés. Au niveau de la lecture répétable, si vous ne verrouillez pas manuellement, le mécanisme n'utilise pas de verrous. Si InnoDB ne crée pas d'index, il utilise des verrous de table. L'élément d'index est utilisé dans la requête, il utilise des verrous de ligne. Les verrous de ligne verrouillent l'index au lieu de simplement verrouiller une ligne de données

.

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!