Une explication détaillée des verrous MySql au niveau des lignes et des verrous au niveau des tables

Verrouillage au niveau de la table de verrouillage MySql

Comment garantir la cohérence et l'efficacité de l'accès simultané aux données est un problème que toutes les bases de données doivent résoudre. Les conflits de verrouillage sont également un facteur important affectant les performances de l'accès simultané aux bases de données. De ce point de vue, les verrous sont particulièrement importants et complexes pour les bases de données.

Présentation du verrouillage MySQL

Comparé à d'autres bases de données, le mécanisme de verrouillage de MySQL est relativement simple. Sa caractéristique la plus importante est que différents moteurs de stockage prennent en charge différents mécanismes de verrouillage.
Par exemple, les moteurs de stockage

MyISAM et MEMORY utilisent le verrouillage au niveau de la table.

Le moteur de stockage InnoDB prend en charge à la fois le verrouillage au niveau des lignes et le verrouillage au niveau des tables, mais le verrouillage au niveau des lignes est utilisé par défaut.

Les caractéristiques de ces trois verrous dans MySQL peuvent être résumées comme suit

Verrous au niveau de la table : faible surcharge, verrouillage rapide ; pas de blocages ; grande granularité de verrouillage, probabilité la plus élevée de conflits de verrouillage et concurrence la plus faible.

Verrouillages au niveau des lignes : des blocages lents et élevés peuvent survenir ; la granularité du verrouillage est la plus faible, la probabilité de conflits de verrouillage est la plus faible et la concurrence est la plus élevée.

Verrouillage de page : le coût et le temps de verrouillage se situent entre les verrous de table et les verrous de ligne ; la granularité du verrouillage se situe entre les verrous de table et les verrous de ligne, et la concurrence est moyenne.

Du point de vue des verrous : les verrous au niveau de la table sont plus adaptés aux applications qui sont principalement basées sur des requêtes et n'ont qu'une petite quantité de données mises à jour en fonction des conditions d'index, telles que les applications Web, tandis que les verrous au niveau des lignes sont plus adaptés ; convient aux applications avec un grand nombre de mises à jour simultanées basées sur des conditions d'indexation, une petite quantité de données différentes et des applications de requêtes simultanées, telles que certains systèmes de traitement de transactions en ligne (OLTP). Dans les sections suivantes, nous nous concentrons sur les problèmes des verrous de table MySQL et des verrous de ligne InnoDB.

Verrouillage de table MyISAM

Le moteur de stockage MyISAM ne prend en charge que les verrous de table, qui sont également le seul type de verrouillage pris en charge dans les premières versions de MySQL. Avec l'amélioration continue des exigences des applications en matière d'intégrité et de concurrence des transactions, MySQL a commencé à développer des moteurs de stockage basés sur les transactions. Plus tard, le moteur de stockage BDB qui prend en charge les verrous de page et le moteur de stockage InnoDB qui prend en charge les verrous de ligne sont progressivement apparus (en fait, InnoDB est un moteur distinct). Une société désormais rachetée par Oracle). Cependant, le verrou de table de MyISAM reste le type de verrou le plus utilisé. Cette section présentera en détail l'utilisation des verrous de table MyISAM.

Requête de conflit de verrouillage au niveau de la table

Vous pouvez analyser les conflits de verrouillage de table sur votre système en vérifiant les variables d'état table_locks_waited et table_locks_immediate :

mysql> | Valeur |

| Table_locks_immediate |

| Table_locks_waited | 0 |

2 lignes dans l'ensemble (0,00 sec)

Si la valeur de Table_locks_waited est relativement élevée , alors Cela indique qu'il existe un grave conflit de verrouillage au niveau de la table. Mode de verrouillage du verrouillage au niveau de la table MySQLLe verrouillage au niveau de la table MySQL a deux modes : le verrouillage en lecture partagé de la table (Table Read Lock) et le verrouillage en écriture exclusif de la table (Table Write Lock).

L'opération de lecture de la table MyISAM ne bloquera pas les demandes de lecture des autres utilisateurs pour la même table, mais elle bloquera les demandes d'écriture de la même table ;

L'opération d'écriture de la table MyISAM bloquera la lecture et l'écriture des autres utilisateurs ; ;

  Les opérations de lecture et d'écriture de la table MyISAM, ainsi que les opérations d'écriture, sont en série ;

  Lorsqu'un thread obtient un verrou en écriture sur une table, seul le thread détenant le verrou peut mettre à jour la table ; . Les opérations de lecture et d'écriture à partir d'autres threads attendront que le verrou soit libéré.

Obtenir le verrouillage WRITE de la table film_text

mysql> verrouiller la table film_text write;

Requête OK, 0 ligne affectée (0,00 sec)

La session en cours peut effectuer des opérations de requête, de mise à jour et d'insertion sur la table verrouillée :

mysql> ; sélectionnez film_id,title from film_text où film_id = 1001;

| film_id |

|

1 rang in set (0,00 sec)

mysql> insérer dans film_text (film_id,title) valeurs (1003,'Test');

Requête OK, 1 ligne affectée (0,00 sec)mysql> ' où film_id = 1001 ;Requête OK, 1 ligne affectée (0,00 sec)

Lignes correspondantes : 1 Modifiée : 1 Avertissements : 0

Les requêtes des autres sessions sur la table verrouillée sont bloquées et doivent attendre que le verrou soit activé. être libéré :

mysql> ; sélectionnez film_id,title from film_text où film_id = 1001 ;

En attente

libérez le verrou :

mysql>

Session2 obtient le verrou, la requête renvoie ：

mysql> select film_id,title from film_text où film_id = 1001;

|

| 1001 | Test |

1 ligne dans l'ensemble (57,59 sec)

Comment ajouter un verrouillage de table ?

MyISAM ajoutera automatiquement des verrous en lecture à toutes les tables impliquées avant d'exécuter l'instruction de requête (SELECT). Avant d'exécuter les opérations de mise à jour (UPDATE, DELETE, INSERT, etc.), il ajoutera automatiquement des verrous en écriture aux tables impliquées. not Aucune intervention de l'utilisateur n'est requise. Par conséquent, les utilisateurs n'ont généralement pas besoin d'utiliser directement la commande LOCK TABLE pour verrouiller explicitement la table MyISAM. Dans l’exemple, le verrouillage explicite est essentiellement effectué pour des raisons de commodité et n’est pas requis.

Le verrouillage de l'affichage des tables MyISAM est généralement effectué pour simuler les opérations de transaction dans une certaine mesure et obtenir une lecture cohérente de plusieurs tables à un moment donné. Par exemple, il existe une table de commande commandes, qui enregistre le montant total de chaque commande, et il existe également une table de détail de commande order_detail, qui enregistre le montant sous-total de chaque produit de chaque commande. Supposons que nous devions vérifier les deux tables. Pour vérifier si les montants totaux correspondent, vous devrez peut-être exécuter les deux SQL suivants :

Sélectionnez la somme (total) des commandes

Sélectionnez la somme (sous-total) dans order_detail

À ce stade, si vous ne verrouillez pas le deux tables en premier. Des résultats erronés peuvent être produits car la table order_detail peut avoir changé lors de l'exécution de la première instruction. Par conséquent, la méthode correcte devrait être :

Verrouiller les commandes des tables lues localement, order_detail lire localement ;

Sélectionner la somme (total) des commandes ;

Sélectionner la somme (sous-total) de order_detail ;

Déverrouiller les tables ;

Instructions spéciales suite à deux points.

L'exemple ci-dessus ajoute l'option "local" lors du VERROUILLAGE DES TABLES. Sa fonction est de permettre à d'autres utilisateurs d'insérer simultanément des enregistrements à la fin de la table lorsque les conditions d'insertion simultanées de la table MyISAM sont remplies. la table MyISAM, nous en discuterons également plus tard. Sera présenté plus loin.

Lorsque vous utilisez LOCK TABLES pour ajouter explicitement des verrous de table à une table, tous les verrous impliqués dans la table doivent être obtenus en même temps et MySQL ne prend pas en charge les mises à niveau de verrous. C'est-à-dire qu'après avoir exécuté LOCK TABLES, vous ne pouvez accéder qu'aux tables explicitement verrouillées, mais pas aux tables déverrouillées en même temps ; si vous ajoutez un verrou en lecture, vous ne pouvez effectuer que des opérations de requête, mais pas d'opérations de mise à jour. En fait, c'est fondamentalement le cas dans le cas du verrouillage automatique. MyISAM obtient toujours tous les verrous requis par l'instruction SQL en même temps. C'est pourquoi les tables MyISAM ne seront pas bloquées (Deadlock Free).

Une session utilise la commande LOCK TABLE pour ajouter un verrou de lecture à la table film_text. Cette session peut interroger les enregistrements de la table verrouillée, mais une erreur sera générée lors de la mise à jour ou de l'accès à d'autres tables en même temps, une autre session ; peut interroger les enregistrements de la table, mais lors de la mise à jour, une attente de verrouillage se produira.

Lorsque vous utilisez LOCK TABLES, non seulement vous devez verrouiller toutes les tables utilisées en même temps, mais aussi, peu importe le nombre de fois où la même table apparaît dans l'instruction SQL, vous devez la verrouiller via le même alias que dans l'instruction SQL. , sinon une erreur se produira !

Les exemples sont les suivants.

(1) Obtenir le verrouillage en lecture sur la table d'acteur :

mysql> verrouiller la table d'acteur en lecture ;

Requête OK, 0 ligne affectée (0,00 sec)

(2) Cependant, l'accès via un alias provoquera une erreur :

mysql> sélectionnez a.first_name,a.last_name,b.first_name,b.last_name de l'acteur a,acteur b où a.first_name = b.first_name et a.first_name = 'Lisa' et a.last_name = 'Tom' et a .last_name b.last_name;

ERROR 1100 (HY000) : La table 'a' n'a pas été verrouillée avec LOCK TABLES

(3) Les alias doivent être verrouillés séparément :

mysql> ,acteur comme b read;

Requête OK, 0 ligne affectée (0,00 sec)

(4) La requête selon l'alias peut être exécutée correctement :

mysql> sélectionnez a.first_name,a.last_name,b.first_name ,b.last_name de l'acteur a,acteur b où a.first_name = b.first_name et a.first_name = 'Lisa' et a.last_name = 'Tom' et a.last_name

| prénom | nom | prénom | nom |

|

1 ligne dans l'ensemble (0,00 sec)

Inserts simultanés

Comme mentionné ci-dessus, la lecture et l'écriture des tables MyISAM sont en série, mais c'est en général. Sous certaines conditions, les tables MyISAM prennent également en charge les opérations de requête et d'insertion simultanées.

Le moteur de stockage MyISAM possède une variable système concurrent_insert, qui est spécifiquement utilisée pour contrôler son comportement d'insertion simultanée. Sa valeur peut être respectivement 0, 1 ou 2.

Lorsque concurrent_insert est défini sur 0, les insertions simultanées ne sont pas autorisées.

Lorsque concurrent_insert est défini sur 1, s'il n'y a pas de trous dans la table MyISAM (c'est-à-dire qu'il n'y a pas de lignes supprimées au milieu de la table), MyISAM permet à un processus de lire la table tandis qu'un autre processus insère des enregistrements du bout de tableau. C'est également le paramètre par défaut pour MySQL.

Lorsque concurrent_insert est défini sur 2, les enregistrements peuvent être insérés simultanément à la fin de la table, qu'il y ait ou non des trous dans la table MyISAM.

Vous pouvez utiliser la fonctionnalité d'insertion simultanée du moteur de stockage MyISAM pour résoudre les conflits de verrouillage lors de l'interrogation et de l'insertion de la même table dans l'application. Par exemple, définir la variable système concurrent_insert sur 2 permet toujours une insertion simultanée ; en même temps, l'instruction OPTIMIZE TABLE est régulièrement exécutée pendant la période d'inactivité du système pour défragmenter l'espace et récupérer les trous intermédiaires provoqués par la suppression des enregistrements. Pour une introduction détaillée à l'instruction OPTIMIZE TABLE, veuillez vous référer à la section « Deux méthodes d'optimisation simples et pratiques » au chapitre 18.

Planification des verrous MyISAM

Comme mentionné précédemment, le verrou en lecture et le verrou en écriture du moteur de stockage MyISAM s'excluent mutuellement et les opérations de lecture et d'écriture sont en série. Ainsi, si un processus demande un verrou en lecture sur une table MyISAM et qu'en même temps un autre processus demande également un verrou en écriture sur la même table, comment MySQL le gère-t-il ? La réponse est que le processus d’écriture acquiert le verrou en premier. De plus, même si la demande de lecture arrive en premier dans la file d'attente de verrouillage et que la demande d'écriture arrive plus tard, le verrou d'écriture sera inséré avant la demande de verrouillage de lecture ! En effet, MySQL considère que les requêtes d'écriture sont généralement plus importantes que les requêtes de lecture. C'est pourquoi les tables MyISAM ne conviennent pas aux applications comportant un grand nombre d'opérations de mise à jour et d'opérations de requête, car un grand nombre d'opérations de mise à jour rendront difficile l'obtention de verrous de lecture par les opérations de requête, ce qui peut bloquer définitivement. Cette situation peut parfois devenir vraiment mauvaise ! Heureusement, nous pouvons ajuster le comportement de planification de MyISAM via certains paramètres.

En spécifiant le paramètre de démarrage low-priority-updates, le moteur MyISAM donne la priorité aux requêtes de lecture par défaut.

En exécutant la commande SET LOW_PRIORITY_UPDATES=1, la priorité des requêtes de mise à jour émises par cette connexion est réduite.

Réduisez la priorité des instructions INSERT, UPDATE et DELETE en spécifiant l'attribut LOW_PRIORITY de l'instruction.

Bien que les trois méthodes ci-dessus soient soit la mise à jour en premier, soit la requête en premier, elles peuvent toujours être utilisées pour résoudre le grave problème de l'attente de verrouillage en lecture dans les applications où la requête est relativement importante (comme le système de connexion utilisateur).

De plus, MySQL fournit également une méthode de compromis pour ajuster les conflits de lecture et d'écriture, c'est-à-dire définir une valeur appropriée pour le paramètre système max_write_lock_count Lorsque le verrou de lecture d'une table atteint cette valeur, MySQL donnera temporairement la priorité aux demandes d'écriture. Le niveau est abaissé pour donner au processus de lecture une certaine chance d'obtenir le verrou.

Les problèmes et les solutions causés par le mécanisme de planification des priorités d'écriture ont été discutés ci-dessus. Un point supplémentaire doit être souligné ici : certaines opérations de requête qui nécessitent de longs temps d'exécution « affameront » également le processus d'écriture ! Par conséquent, vous devriez essayer d'éviter les opérations de requête de longue durée dans votre application. N'essayez pas toujours d'utiliser une instruction SELECT pour résoudre le problème, car cette instruction SQL apparemment intelligente est souvent plus complexe et prend plus de temps à s'exécuter. Les instructions SQL peuvent être « décomposées » dans une certaine mesure en utilisant des tables intermédiaires et d'autres mesures afin que chaque étape de la requête puisse être complétée plus rapidement, réduisant ainsi les conflits de verrouillage. Si des requêtes complexes sont inévitables, elles doivent être planifiées pour être exécutées pendant les périodes d'inactivité de la base de données. Par exemple, certaines statistiques régulières peuvent être planifiées pour être exécutées la nuit.

Verrouillage InnoDB

Les plus grandes différences entre InnoDB et MyISAM sont deux points : premièrement, il prend en charge les transactions (TRANSACTION) et deuxièmement, il utilise des verrous au niveau des lignes. Il existe de nombreuses différences entre les verrous au niveau des lignes et les verrous au niveau des tables. De plus, l'introduction des transactions entraîne également de nouveaux problèmes. Commençons par quelques connaissances de base, puis discutons en détail du problème de verrouillage d'InnoDB.

1. Transaction (Transaction) et ses attributs ACID

Une transaction est une unité de traitement logique composée d'un ensemble d'instructions SQL. Une transaction possède les 4 attributs suivants, qui sont généralement appelés attributs ACID de la transaction.

(Atomicité) Atomicité : Une transaction est une unité d'opération atomique, et toutes les modifications apportées aux données sont soit exécutées, soit aucune n'est exécutée.

Cohérence (cohérente) : les données doivent rester cohérentes au début et à la fin d'une transaction. Cela signifie que toutes les règles de données pertinentes doivent être appliquées aux modifications de transaction pour maintenir l'intégrité des données ; à la fin de la transaction, toutes les structures de données internes (telles que les index B-tree ou les listes doublement chaînées) doivent également être correctes.

Isolement (Isolement) : le système de base de données fournit un certain mécanisme d'isolation pour garantir que les transactions sont exécutées dans un environnement « indépendant » qui n'est pas affecté par des opérations simultanées externes. Cela signifie que les états intermédiaires au cours d’une transaction ne sont pas visibles du monde extérieur, et vice versa.

Durabilité (Durable) : Une fois la transaction terminée, sa modification des données est permanente et peut être maintenue même en cas de panne du système.

Le virement bancaire est un exemple typique de transaction.

2. Problèmes causés par le traitement simultané des transactions

Par rapport au traitement en série, le traitement des transactions simultanées peut considérablement augmenter l'utilisation des ressources de la base de données et améliorer le débit des transactions du système de base de données, prenant ainsi en charge davantage d'utilisateurs. Cependant, le traitement simultané des transactions entraînera également certains problèmes, notamment les situations suivantes.

Mise à jour perdue : lorsque deux transactions ou plus sélectionnent la même ligne, puis mettent à jour la ligne en fonction de la valeur initialement sélectionnée, le problème de mise à jour perdue se produit car chaque transaction ignore l'existence des autres transactions --La dernière mise à jour remplace. mises à jour effectuées par d'autres entreprises. Par exemple, deux éditeurs réalisent des copies électroniques du même document. Chaque éditeur modifie indépendamment sa copie, puis enregistre la copie modifiée, écrasant ainsi le document original. L'éditeur qui a enregistré en dernier lieu une copie de ses modifications écrase les modifications apportées par un autre éditeur. Ce problème peut être évité si un éditeur ne peut pas accéder au même fichier jusqu'à ce qu'un autre éditeur termine et valide la transaction.

Lectures sales : une transaction modifie un enregistrement. Avant que la transaction ne soit terminée et soumise, les données de cet enregistrement sont dans un état incohérent à ce moment-là, une autre transaction lit également le même enregistrement, si elle n'est pas cochée, la deuxième transaction. lit ces données « sales » et effectue un traitement ultérieur en conséquence, ce qui entraîne des dépendances de données non validées. Ce phénomène est vivement appelé « lecture sale ».

Lectures non répétables : une transaction lit à nouveau les données précédemment lues à un moment donné après avoir lu certaines données, pour constater que les données lues ont changé, ou que certains enregistrements ont été supprimés ! Ce phénomène est appelé « lecture non répétable ».

Lectures fantômes : une transaction relit les données précédemment récupérées selon les mêmes conditions de requête, pour constater que d'autres transactions ont inséré de nouvelles données qui répondent à ses conditions de requête. Ce phénomène est appelé "lectures fantômes".

3. Niveau d'isolement des transactions

Parmi les problèmes causés par le traitement simultané des transactions mentionnés ci-dessus, la « perte de mise à jour » devrait généralement être complètement évitée. Cependant, la prévention de la perte de mise à jour ne peut pas être résolue par le seul contrôleur de transactions de base de données. L'application doit ajouter les verrous nécessaires aux données à mettre à jour. Par conséquent, la prévention de la perte de mise à jour doit relever de la responsabilité de l'application.

« Lecture sale », « lecture non répétable » et « lecture fantôme » sont en fait des problèmes de cohérence de lecture de la base de données, qui doivent être résolus par la base de données fournissant un certain mécanisme d'isolation des transactions. Les manières dont les bases de données mettent en œuvre l’isolation des transactions peuvent être essentiellement divisées en deux types suivants.

La première consiste à verrouiller les données avant de les lire pour empêcher d'autres transactions de modifier les données.

L'autre consiste à générer un instantané de données cohérent (Snapshot) du moment de la demande de données via un certain mécanisme sans ajouter de verrous, et à utiliser cet instantané pour fournir un certain niveau (niveau d'instruction ou niveau de transaction) de lecture cohérente. Du point de vue de l'utilisateur, il semble que la base de données puisse fournir plusieurs versions des mêmes données. C'est pourquoi cette technologie est appelée contrôle de concurrence multi-versions de données (MVCC ou MCC en abrégé), également souvent appelée base de données multi-versions.

Lecture cohérente, également appelée lecture instantanée. Le mécanisme MVCC est utilisé pour lire les données soumises en annulation. Sa lecture est donc non bloquante.

La lecture de cohérence doit lire les données qui ont été soumises à un moment donné. Il existe un cas particulier : les données modifiées dans cette transaction, même les données non validées, peuvent être lues dans la partie ultérieure de cette transaction. Une lecture cohérente fait référence à une instruction select ordinaire sans clauses telles que for update, in share mode, etc. Les données soumises lors de l'annulation sont utilisées et aucun verrou n'est requis (sauf MDL). La lecture actuelle fait référence à la lecture effectuée par des instructions telles que update, delete, select for update, select in share mode, etc. Elles lisent les dernières données de la base de données et verrouillent les lignes et les espaces lus (heure d'isolement RR). Si le verrou ne peut pas être obtenu, il attendra jusqu'à ce qu'il soit obtenu ou expirera.

Plus l'isolation des transactions de la base de données est stricte, plus les effets secondaires de la concurrence sont faibles, mais plus le prix à payer est élevé, car l'isolation des transactions rend essentiellement les transactions « sérialisées » dans une certaine mesure, ce qui est évidemment contradictoire avec la « concurrence » de. Dans le même temps, différentes applications ont des exigences différentes en matière de cohérence de lecture et d'isolation des transactions. Par exemple, de nombreuses applications ne sont pas sensibles aux « lectures non répétables » et aux « lectures fantômes » et peuvent être plus préoccupées par la possibilité d'accéder simultanément aux données.

Afin de résoudre la contradiction entre « isolement » et « concurrence), ISO/ANSI SQL92 définit 4 niveaux d'isolement des transactions. Chaque niveau a un degré d'isolement différent et permet différents effets secondaires. Les applications peuvent être basées sur leur propre logique métier. Équilibrez la contradiction entre « isolement » et « concurrence » en choisissant différents niveaux d'isolement. Le tableau 20-5 fournit un bon résumé des caractéristiques de ces quatre niveaux d'isolement.

Chaque base de données spécifique n'implémente pas nécessairement entièrement les quatre niveaux d'isolement ci-dessus. Oracle ne fournit que deux niveaux d'isolement standard : lecture validée et sérialisable, et fournit également son propre niveau d'isolement en lecture seule défini. SQL Server prend en charge l'ISO/ANSI SQL92 mentionné ci-dessus ; définitions En plus des 4 niveaux d'isolement, il prend également en charge un niveau d'isolement appelé « instantané », mais à proprement parler, il s'agit d'un niveau d'isolement sérialisable implémenté à l'aide de MVCC.

MySQL prend en charge les 4 niveaux d'isolement, mais dans l'implémentation spécifique, il existe certaines caractéristiques. Par exemple, la lecture de cohérence MVCC est utilisée dans certains niveaux d'isolement, mais dans certains cas, ce contenu ne sera pas abordé dans les chapitres suivants. . Faites une introduction plus approfondie.

4. Obtenir la contention de verrouillage de ligne InnoDB

Vous pouvez analyser la contention de verrouillage de ligne sur le système en vérifiant la variable d'état InnoDB_row_lock.

mysql> affiche le statut comme 'innodb_row_lock%';

| noDB_row_lock_current_waits |

| _time_avg | 0 || InnoDB_row_lock_time_max || | 0 |

5 lignes dans l'ensemble (0,01 sec)

Si vous trouvez que le conflit de verrouillage est sérieux, comme les valeurs ​​​​de InnoDB_row_lock_waits et InnoDB_row_lock_time_avg sont relativement élevées, vous pouvez également configurer les moniteurs InnoDB pour observer davantage les tables, les données lignes, etc. où des conflits de verrouillage se produisent, et analysez les verrous Cause de conflit.

La méthode spécifique est la suivante :

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

Requête OK, 0 ligne affectée (0,14 sec)

Ensuite, vous pouvez utiliser l'instruction suivante pour afficher :

mysql> Afficher l'état d'innodb Dans le contenu, il y aura des informations détaillées sur le verrou en attente actuel, y compris le nom de la table, le type de verrou, l'état de l'enregistrement du verrou, etc., pour faciliter une analyse plus approfondie et la détermination des problèmes. Après l'ouverture du moniteur, le contenu surveillé sera enregistré dans le journal toutes les 15 secondes par défaut. S'il est ouvert pendant une longue période, le fichier .err deviendra très volumineux. Par conséquent, après avoir confirmé la cause du problème, l'utilisateur. il faut penser à supprimer la table de surveillance pour la fermer, ou en démarrant le serveur en utilisant l'option "--console" pour désactiver l'écriture du fichier journal.

5. Le mode de verrouillage de ligne et la méthode de verrouillage d'InnoDB

InnoDB implémente les deux types de verrouillage de ligne suivants.

Verrouillage partagé (S) : permet à une transaction de lire une ligne et empêche d'autres transactions d'obtenir un verrouillage exclusif sur le même ensemble de données.

Verrou exclusif (X) : autorise une transaction qui obtient un verrou exclusif pour mettre à jour les données et empêche d'autres transactions d'obtenir des verrous de lecture partagés et des verrous d'écriture exclusifs sur le même ensemble de données.

De plus, afin de permettre aux verrous de ligne et de table de coexister et de mettre en œuvre un mécanisme de verrouillage multi-granularité, InnoDB dispose également de deux verrous d'intention utilisés en interne (Intention Locks).

Les deux verrous d'intention sont des verrous de table.

Intention shared lock (IS) : la transaction a l'intention d'ajouter un verrou de partage de ligne à la ligne de données. La transaction doit d'abord obtenir le verrou IS de la table avant d'ajouter un verrou partagé à une ligne de données.

Verrou exclusif d'intention (IX) : la transaction a l'intention d'ajouter un verrou exclusif de ligne à la ligne de données. La transaction doit d'abord obtenir le verrou IX de la table avant d'ajouter un verrou exclusif à une ligne de données.

Si le mode de verrouillage demandé par une transaction est compatible avec le verrou actuel, InnoDB accordera le verrou demandé à la transaction sinon, si les deux sont incompatibles, la transaction attendra que le verrou soit libéré ; .

Les verrous d'intention sont automatiquement ajoutés par InnoDB et ne nécessitent aucune intervention de l'utilisateur.

Le résumé est le suivant : 1. Pour les instructions UPDATE, DELETE et INSERT, InnoDB ajoutera automatiquement des verrous exclusifs (X) aux ensembles de données impliqués ;

2. Pour les instructions SELECT ordinaires, InnoDB n'ajoutera aucun verrou ;

3 . Une transaction peut ajouter des verrous partagés ou des verrous exclusifs au jeu d'enregistrements via l'instruction suivante.

Verrou(s) partagé(s) : SELECT * FROM nom_table OÙ... VERROUILLER EN MODE PARTAGE.

Verrou exclusif (X) : SELECT * FROM nom_table OÙ ... POUR LA MISE À JOUR.

Utilisez SELECT ... EN MODE PARTAGE pour obtenir un verrou partagé. Il est principalement utilisé pour confirmer si une certaine ligne d'enregistrements existe lorsque des dépendances de données sont requises et pour garantir que personne n'effectue d'opérations de MISE À JOUR ou DE SUPPRESSION sur cet enregistrement.

Cependant, si la transaction en cours doit également mettre à jour l'enregistrement, cela est susceptible de provoquer un blocage. Pour les applications qui doivent mettre à jour l'enregistrement de ligne après l'avoir verrouillé, la méthode SELECT... FOR UPDATE doit être utilisée pour obtenir un. serrure exclusive.

6. Méthode d'implémentation du verrouillage de ligne InnoDB

Le verrouillage de ligne InnoDB est implémenté en verrouillant les éléments d'index sur l'index. Ceci est différent de MySQL et Oracle. Ce dernier est obtenu en verrouillant les lignes de données correspondantes dans le bloc de données réalisé.

La fonctionnalité d'implémentation du verrouillage de ligne d'InnoDB signifie qu'InnoDB utilisera des verrous au niveau de la ligne uniquement lorsque les données sont récupérées via des conditions d'index. Sinon, InnoDB utilisera des verrous de table !

Dans les applications pratiques, une attention particulière doit être accordée à cette fonctionnalité des verrous de ligne InnoDB, sinon elle peut provoquer un grand nombre de conflits de verrouillage, affectant ainsi les performances de concurrence.

(1) Lors d'une requête sans conditions d'index, InnoDB utilise des verrous de table au lieu de verrous de ligne.

(2) Étant donné que le verrou de ligne de MySQL est un verrou pour l'index, pas pour l'enregistrement, bien que les enregistrements de lignes différentes soient accessibles,

mais si la même clé d'index est utilisée, un conflit de verrouillage se produira. Veuillez y prêter attention lors de la conception de votre application.

(3) Lorsqu'une table a plusieurs index, différentes transactions peuvent utiliser différents index pour verrouiller différentes lignes.

De plus, InnoDB utilisera des verrous de ligne, qu'il s'agisse d'index de clé primaire, d'index uniques ou d'index ordinaires.

(4) Même si un champ d'index est utilisé dans la condition, MySQL détermine s'il faut utiliser l'index pour récupérer des données en jugeant le coût des différents plans d'exécution. Si MySQL estime qu'une analyse complète de la table est plus efficace, par exemple. comme pour certaines petites tables, elle n'utilisera pas d'index, auquel cas InnoDB utilisera des verrous de table au lieu de verrous de lignes. Par conséquent, lors de l'analyse des conflits de verrouillage, n'oubliez pas de vérifier le plan d'exécution SQL pour confirmer si l'index est réellement utilisé. Pour une discussion détaillée des circonstances dans lesquelles MySQL n'utilise pas d'index, consultez l'introduction de la section « Problèmes d'index » de ce chapitre.

7. Verrouillage de l'espace (verrouillage à clé suivante)

Lorsque nous utilisons des conditions de plage au lieu de conditions d'égalité pour récupérer des données et demander des verrous partagés ou exclusifs, InnoDB verrouillera les entrées d'index des enregistrements de données existants qui remplissent les conditions pour les enregistrements dont les valeurs de clé se trouvent dans la plage de conditions mais ne le font pas ; existe, appelé "GAP", InnoDB verrouillera également ce "gap", ce mécanisme de verrouillage est ce qu'on appelle le verrouillage de l'espace (Next-Key lock). S'il n'y a que 101 enregistrements dans la table emp, les valeurs empid sont 1, 2,...,100,101. Le SQL suivant :

Sélectionnez * from emp où empid > Récupération des conditions, InnoDB verrouillera non seulement les enregistrements avec une valeur empid de 101 qui remplissent les conditions, mais verrouillera également les "espaces" avec un empid supérieur à 101 (ces enregistrements n'existent pas). Le but d'InnoDB utilisant des verrous d'espacement est, d'une part, d'empêcher les lectures fantômes et de répondre aux exigences des niveaux d'isolation pertinents. Pour l'exemple ci-dessus, si les verrous d'espacement ne sont pas utilisés, si d'autres transactions insèrent un enregistrement avec un emid supérieur à 100. , alors si cette transaction Si l'instruction ci-dessus est à nouveau exécutée, une lecture fantôme se produira en revanche, c'est pour répondre à ses besoins de récupération et de réplication ; L'impact de sa récupération et de sa réplication sur le mécanisme de verrouillage, ainsi que l'utilisation de verrous d'espacement par InnoDB sous différents niveaux d'isolement, seront présentés plus en détail dans les chapitres suivants.

Évidemment, lors de l'utilisation de conditions de plage pour récupérer et verrouiller des enregistrements, le mécanisme de verrouillage d'InnoDB bloquera l'insertion simultanée de valeurs clés dans la plage qualifiée, ce qui entraîne souvent de sérieuses attentes de verrouillage. Par conséquent, dans le développement d'applications réelles, en particulier les applications avec de nombreuses insertions simultanées, nous devons essayer d'optimiser la logique métier, essayer d'utiliser des conditions égales pour accéder aux données de mise à jour et éviter d'utiliser des conditions de plage.

La note spéciale est qu'InnoDB, en plus d'utiliser des verrous d'espacement lors du verrouillage via des conditions de plage, utilisera également des verrous d'espacement si des conditions égales sont utilisées pour demander un verrouillage pour un enregistrement inexistant !

Le besoin de récupération et de réplication, l'impact sur le mécanisme de verrouillage InnoDB

MySQL utilise BINLOG pour enregistrer l'exécution réussie d'INSERT, UPDATE, DELETE et d'autres instructions SQL qui mettent à jour les données, et réalise ainsi la récupération et la réplication maître-esclave de la base de données MySQL. Le mécanisme de récupération de MySQL (la réplication est en fait une récupération continue basée sur BINLOG sur l'esclave Mysql) présente les caractéristiques suivantes.

Tout d'abord, la récupération MySQL se fait au niveau de l'instruction SQL, c'est-à-dire la réexécution de l'instruction SQL dans BINLOG. Ceci est différent de la base de données Oracle, qui est basée sur des blocs de fichiers de base de données.

Deuxièmement, le BINLOG de MySQL est enregistré dans l'ordre dans lequel les transactions sont soumises, et la récupération est également effectuée dans cet ordre. Ceci est également différent d'Oracle. Oracle restaure les données en fonction du numéro de modification du système (SCN). Lorsque chaque transaction démarre, Oracle attribue un SCN unique au monde. L'ordre du SCN et l'ordre horaire du début de la transaction sont cohérents.

Il ressort des deux points ci-dessus que le mécanisme de récupération de MySQL exige : avant qu'une transaction ne soit soumise, les autres transactions simultanées ne peuvent insérer aucun enregistrement qui remplit ses conditions de verrouillage, c'est-à-dire que les lectures fantômes ne sont pas autorisées, ce qui a dépassé ISO/ ANSI SQL92 L'exigence du niveau d'isolement « lecture répétable » nécessite en fait que les transactions soient sérialisées.

De plus, pour les instructions SQL telles que "insérer dans target_tab, sélectionner * depuis source_tab où..." et "créer une table new_tab ...select ... Depuis source_tab où ...(CTAS)", l'utilisateur ne le fait pas ayez le source_tab Effectuez toute opération de mise à jour, mais MySQL effectue un traitement spécial pour ce type d'instruction SQL.

(Ici, InnoDB ajoute un verrou partagé à source_tab et n'utilise pas de technologie de lecture de cohérence des données multi-versions !)

Dans l'exemple ci-dessus, la simple lecture des données dans la table source_tab équivaut à exécuter une instruction For SELECT ordinaire , utilisez simplement une lecture cohérente. ORACLE fait exactement cela. Il utilise des données multiversions implémentées par la technologie MVCC pour obtenir une lecture cohérente sans ajouter de verrous à source_tab. Nous savons qu'InnoDB implémente également des données multiversions et ne nécessite aucun verrou pour une lecture cohérente de SELECT ordinaire ; mais ici, InnoDB ajoute un verrou partagé à source_tab et n'utilise pas de technologie de lecture cohérente de données multiversions !

Pourquoi MySQL fait-il cela ? La raison est de garantir l'exactitude de la récupération et de la réplication. Car sans verrouillage, si d'autres transactions mettent à jour source_tab pendant l'exécution de l'instruction ci-dessus, cela peut conduire à des résultats de récupération de données incorrects. Pour démontrer cela, répétons l'exemple précédent. La différence est qu'avant que session_1 n'exécute la transaction, la valeur de la variable système innodb_locks_unsafe_for_binlog est définie sur "on" (sa valeur par défaut est off)

Comme le montre ce qui précède, après avoir défini la valeur de la variable système innodb_locks_unsafe_for_binlog sur "on", InnoDB ne verrouille plus le source_tab, et le résultat est conforme à la logique de l'application. Cependant, si le contenu de BINLOG est analysé. :

SET TIMESTAMP=1169175130;

BEGIN;

# at 274

#070119 10:51:57 identifiant du serveur 1 end_log_pos 105 Requête thread_id=1 exec_time=0 error_code=0

SET =1169175117;

mettre à jour source_tab set name = '8 ' où name = '1 ';

# at 379

#070119 10:52:10 identifiant du serveur 1 end_log_pos 406 474 Query thread_id= 2 exec_time=0 error_code=0

SET TIMESTAMP= 1169175134;

BEGIN;

# à 474

#070119 10:51:29 identifiant du serveur 1 end_log_pos 119 Requête thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1169175089;

insérer dans target_tab, sélectionnez d1, name from source_tab Where name = '1';

# at 593

#070119 10:52:14 server id 1 end_log_pos 620 Xid = 7

COMMIT;

On peut trouver que dans BINLOG, l'emplacement du l'opération de mise à jour est avant INSERT...SELECT. Si ce BINLOG est utilisé pour la récupération de la base de données, le résultat de la récupération ne correspondra pas à la logique réelle de l'application ; si la réplication est effectuée, la base de données maître-esclave sera incohérente !

Par conséquent, les instructions INSERT...SELECT... et CREATE TABLE...SELECT... peuvent empêcher les mises à jour simultanées de la table source, provoquant l'attente du verrouillage de la table source. Si la requête est complexe, elle entraînera de sérieux problèmes de performances et nous devrions essayer d'éviter de l'utiliser dans nos applications. En fait, MySQL appelle ce type de SQL SQL non déterministe et n'est pas recommandé.

Si ce type de SQL doit être utilisé pour implémenter la logique métier dans l'application et que vous ne souhaitez pas affecter les mises à jour simultanées de la table source, vous pouvez prendre les deux mesures suivantes :

Premièrement, adoptez l'approche dans le exemple ci-dessus et modifiez la valeur de innodb_locks_unsafe_for_binlog défini sur "on" pour forcer MySQL à utiliser la lecture de cohérence des données multi-versions. Mais le prix à payer est que les données risquent de ne pas être restaurées ou copiées correctement à l'aide de binlog, cette méthode n'est donc pas recommandée.

La seconde consiste à y parvenir indirectement en utilisant une combinaison d'instructions "select * from source_tab ... Into outfile" et "load data infile ..." De cette façon, MySQL ne verrouillera pas source_tab.

8. Différences dans la lecture et le verrouillage cohérents d'InnoDB sous différents niveaux d'isolement

Comme mentionné précédemment, les verrous et les données multi-versions sont le moyen pour InnoDB d'obtenir une lecture cohérente et des niveaux d'isolement ISO/ANSI SQL92, par conséquent, dans différents sous. Le niveau d'isolement, la stratégie de lecture cohérente et les verrous requis utilisés par InnoDB lors du traitement SQL sont différents. Dans le même temps, les caractéristiques des mécanismes de récupération et de réplication des données ont également un impact important sur certaines stratégies de lecture et stratégies de verrouillage cohérentes avec SQL. Ces caractéristiques sont résumées comme indiqué dans le tableau 20-16 pour la commodité des lecteurs.

1 : Lorsque le niveau d'isolement est RC, les verrous d'espacement ne sont pas utilisés. La documentation officielle explique comme suit :

Chaque lecture cohérente, même au sein de la même transaction, définit et lit son propre instantané. Pour plus d'informations sur les lectures cohérentes, voir Section 14.8.2.3, « Lectures non verrouillables cohérentes ». permet l'insertion gratuite de nouveaux enregistrements à côté des enregistrements verrouillés. Le verrouillage des espaces est uniquement utilisé pour la vérification des contraintes de clé étrangère et la vérification des clés en double.

L'adresse officielle de la documentation est : https://dev.mysql.com/doc/ refman. /5.5/en/innodb-transaction-isolation-levels.html

2 : Sous le niveau d'isolement de lecture répétable, si l'index est unique et que la recherche est également unique, les verrous d'espacement ne sont pas utilisés, sinon les verrous d'espacement sont utilisés, le La description officielle est la suivante :

REPEATABLE READ

Il s'agit du niveau d'isolement par défaut pour InnoDB. Les lectures cohérentes au sein de la même transaction lisent l'instantané établi par la première lecture. Cela signifie que si vous émettez plusieurs instructions SELECT simples (non verrouillables). Pour la même transaction, ces instructions SELECT sont également cohérentes les unes par rapport aux autres. Voir Section 14.8.2.3, « Lectures non verrouillables cohérentes ».

Pour les lectures verrouillables (SELECT avec FOR UPDATE ou LOCK IN SHARE MODE), UPDATE et DELETE, le verrouillage dépend du fait que l'instruction utilise un index unique avec une condition de recherche unique ou une condition de recherche de type plage.

Pour un index unique avec une condition de recherche unique, InnoDB verrouille uniquement l'enregistrement d'index trouvé, pas l'espace qui le précède.

Pour d'autres conditions de recherche, InnoDB verrouille la plage d'index analysée, en utilisant des verrous d'espacement ou des verrous de clé suivante pour bloquer les insertions par d'autres sessions dans les espaces couverts par la plage. Pour plus d'informations sur les verrous d'espacement et les verrous de clé suivante, voir la section 14.8. . 1, « Verrouillage InnoDB ».

L'adresse officielle de description du document est : https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

9 Quand utiliser les verrous de table ?

Pour les tables InnoDB. , Les verrous au niveau des lignes doivent être utilisés dans la plupart des cas, car les transactions et les verrous de lignes sont souvent les raisons pour lesquelles nous choisissons les tables InnoDB. Cependant, dans des transactions spéciales individuelles, des verrous au niveau de la table peuvent également être envisagés.

La première situation est la suivante : 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 en provoquer d'autres. les transactions attendent pendant de longues périodes et provoquent des conflits de verrouillage. Dans ce cas, vous pouvez envisager d'utiliser des verrous de table pour améliorer la vitesse d'exécution de la transaction.

La deuxième situation est la suivante : la transaction implique plusieurs tables, ce qui est relativement complexe et est susceptible de provoquer une impasse et de provoquer un grand nombre d'annulations de transactions. Dans ce cas, vous pouvez également envisager de verrouiller une seule fois les tables impliquées dans la transaction pour éviter les blocages et réduire la surcharge de la base de données causée par l'annulation de la transaction.

Bien sûr, il ne devrait pas y avoir trop de ces deux types de transactions dans l'application, sinon vous devriez envisager d'utiliser les tables MyISAM.

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 des tables à InnoDB à l'aide de LOCK TABLES, 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. autocommit =0, innodb_table_locks=1 (paramètre par défaut), la couche InnoDB peut connaître les verrous de table ajoutés par MySQL, et MySQL Server peut également percevoir les verrous de ligne ajoutés par InnoDB. Dans ce cas, InnoDB peut identifier automatiquement les blocages impliquant le niveau de la table. locks ; sinon, InnoDB ne sera pas en mesure de détecter et de gérer automatiquement de tels blocages. Concernant l’impasse, nous continuerons à en discuter dans la section suivante.

(2) Lorsque vous utilisez LOCK TABLES pour verrouiller la table InnoDB, veillez à définir AUTOCOMMIT sur 0, sinon MySQL ne verrouillera pas la table ; n'utilisez pas UNLOCK TABLES pour libérer le verrou de la table avant la fin de la transaction, car UNLOCK TABLES le fera. Validez implicitement la transaction ; COMMIT ou ROLLBACK ne peuvent pas libérer les verrous au niveau de la table ajoutés avec LOCK TABLES, et les verrous de table doivent être libérés avec UNLOCK TABLES. La bonne manière est la suivante.

Par exemple, si vous devez écrire dans la table t1 et lire dans la table t, vous pouvez procéder comme suit :

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, . ..;

[faire quelque chose avec les tables t1 et t2 ici];

COMMIT;

UNLOCK TABLES;

10. Concernant les blocages

Comme mentionné ci-dessus, les verrous de table MyISAM sont sans blocage, car MyISAM les acquiert toujours à une fois que tous les verrous requis sont satisfaits ou en attente, aucun blocage ne se produit. Mais dans InnoDB, à l'exception des transactions composées d'un seul SQL, les verrous sont acquis progressivement, ce qui détermine qu'un blocage est possible dans InnoDB. Le Tableau 20-17 montre un exemple de blocage.

Dans l'exemple ci-dessus, les deux transactions doivent obtenir le verrou exclusif détenu par l'autre partie pour continuer à terminer la transaction. Ce type d'attente de verrouillage cyclique est une impasse typique.

Après un blocage, InnoDB peut généralement le détecter automatiquement et faire en sorte qu'une transaction libère le verrou et annule, tandis qu'une autre transaction acquiert le verrou et continue de terminer la transaction. Cependant, lorsque des verrous externes ou des verrous de table sont impliqués, InnoDB ne peut pas détecter complètement automatiquement les blocages. Cela doit être résolu en définissant le paramètre de délai d'attente du 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 que les verrous requis ne peuvent pas être obtenus immédiatement, cela occupera une grande quantité de ressources informatiques. et causer de sérieux problèmes de performances. Le problème se propage même dans les bases de données. Nous pouvons éviter cette situation en définissant un seuil de délai d'attente de verrouillage approprié.

De manière générale, les blocages sont un problème de conception d'applications. En ajustant les processus métier, la conception des objets de base de données, la taille des transactions et les instructions SQL qui accèdent à la base de données, la plupart des blocages peuvent être évités.

Ce qui suit présentera plusieurs méthodes courantes pour éviter les impasses à travers des exemples.

(1) Dans une application, si différents programmes accèdent à plusieurs tables simultanément, essayez de convenir d'accéder aux tables dans le même ordre, ce qui peut réduire considérablement le risque de blocage. Dans l'exemple suivant, étant donné que les deux sessions accèdent aux deux tables dans des ordres différents, le risque de blocage est très élevé ! Mais si les accès sont effectués dans le même ordre, les blocages peuvent être évités.

(2) 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, le risque de blocage peut également être considérablement réduit.

(3) Dans une transaction, si vous souhaitez mettre à jour un enregistrement, vous devez directement demander un verrou de niveau suffisant, c'est-à-dire un verrou exclusif. Vous ne devez pas d'abord demander un verrou partagé, puis ensuite. demander un verrou exclusif lors de la mise à jour, car lorsque l'utilisateur demande un verrou exclusif à ce moment-là, d'autres transactions peuvent avoir obtenu le verrou partagé du même enregistrement, provoquant des conflits de verrouillage, voire des blocages. Pour une démonstration spécifique, veuillez vous référer à l'exemple de la section 20.3.3.

(4) Comme mentionné précédemment, sous le niveau d'isolement REPEATABLE-READ, si deux threads utilisent SELECT...FOR UPDATE pour ajouter des verrous exclusifs aux enregistrements avec la même condition en même temps, s'il n'y a aucun enregistrement qui remplit la condition , les deux threads ajouteront le verrouillage réussi. 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.

(5) Lorsque le niveau d'isolement est READ COMMITTED, 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 un 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 ! À ce stade, si un troisième thread demande un verrou exclusif, un blocage se produira également.

Dans ce cas, vous pouvez effectuer l'opération d'insertion directement, puis intercepter l'exception de duplication de clé primaire, ou toujours exécuter ROLLBACK pour libérer le verrou exclusif acquis lorsque vous rencontrez une erreur de duplication de clé primaire.

Bien que les blocages puissent être considérablement réduits grâce à la conception, à l'optimisation SQL et aux autres mesures présentées ci-dessus, les blocages sont difficiles à éviter complètement. Par conséquent, c'est une bonne habitude de programmation de toujours détecter et gérer les exceptions de blocage dans la programmation.

Si un blocage se produit, vous pouvez utiliser la commande SHOW INNODB STATUS pour déterminer la cause du dernier blocage. Les résultats renvoyés incluent des informations détaillées sur les transactions liées au blocage, telles que l'instruction SQL qui a provoqué le blocage, les verrous acquis par la transaction, les verrous qu'elle attend et les transactions qui ont été annulées. Sur cette base, les causes du blocage et les mesures d'amélioration peuvent être analysées.

Ce qui suit est un exemple de sortie de SHOW INNODB STATUS :

mysql> show innodb status G

InnoDB Summary

Ce chapitre se concentre sur les fonctionnalités d'implémentation des verrous au niveau de la table MyISAM et des verrous au niveau des lignes InnoDB dans MySQL, et aborde les deux problèmes de verrouillage et les solutions souvent rencontrés par les moteurs de stockage.

Pour les verrous de table MyISAM, les points suivants sont principalement abordés :

(1) Les verrous en lecture partagés (S) sont compatibles, mais entre les verrous en lecture partagés (S) et les verrous en écriture exclusifs (X), et les verrous en écriture exclusifs (X ) s'excluent mutuellement, ce qui signifie que la lecture et l'écriture sont en série.

(2) Sous certaines conditions, MyISAM permet d'exécuter simultanément des requêtes et des insertions. Nous pouvons l'utiliser pour résoudre le problème de conflit de verrouillage pour les requêtes et les insertions dans la même table dans les applications.

(3) Le mécanisme de planification de verrouillage par défaut de MyISAM est la priorité en écriture, ce qui ne convient pas nécessairement à toutes les applications. Les utilisateurs peuvent ajuster le conflit de verrouillage en lecture-écriture en définissant le paramètre LOW_PRIORITY_UPDATES ou en spécifiant l'option LOW_PRIORITY dans INSERT, UPDATE et DELETE. déclarations.

(4) En raison de la grande granularité de verrouillage des verrous de table et des opérations de lecture et d'écriture en série, s'il y a de nombreuses opérations de mise à jour, la table MyISAM peut subir de sérieuses attentes de verrouillage. Vous pouvez envisager d'utiliser les tables InnoDB pour réduire les conflits de verrouillage.

Pour les tables InnoDB, ce chapitre traite principalement du contenu suivant.

Le verrouillage de ligne d'InnoDB est basé sur l'index de verrouillage. Si les données ne sont pas accessibles via l'index, InnoDB utilisera le verrouillage de table.

Présentation du mécanisme de verrouillage d'espacement InnoDB (Next-key) et des raisons pour lesquelles InnoDB utilise des verrous d'espacement.

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

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.

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 à la conception et à l'ajustement SQL et à d'autres mesures, notamment :

Utilisez autant que possible un niveau d'isolement inférieur

Concevez l'index avec soin et essayez de ; utilisez l'index pour accéder aux données, rendez le verrouillage plus précis, réduisant ainsi le risque de conflits 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 se mettre d'accord 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. Cela réduit 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.

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!