Explication détaillée du principe du mécanisme de verrouillage mysql (1)

Lock est un mécanisme permettant aux ordinateurs de coordonner plusieurs processus ou threads pour accéder simultanément à une ressource. Dans une base de données, outre la concurrence traditionnelle pour les ressources informatiques (telles que le CPU, la RAM, les E/S, etc.), les données sont également une ressource partagée par de nombreux utilisateurs. 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. Dans ce chapitre, nous nous concentrons sur les caractéristiques du mécanisme de verrouillage MySQL, les problèmes de verrouillage courants et certaines méthodes ou suggestions pour résoudre les problèmes de verrouillage MySQL.
Mysql utilise de nombreux mécanismes de verrouillage de ce type, tels que les verrous de ligne, les verrous de table, les verrous de lecture, les verrous d'écriture, etc., qui sont tous verrouillés avant les opérations. Ces verrous sont collectivement appelés verrous pessimistes.

Présentation du verrouillage MySQL

Comparé à d'autres bases de données, le mécanisme de verrouillage de MySQL est relativement simple et sa caractéristique la plus importante est le stockage différent. Le moteur prend en charge différents mécanismes de verrouillage. Par exemple, les moteurs de stockage MyISAM et MEMORY utilisent des verrous au niveau de la table (table-level locking) ; le moteur de stockage BDB utilise des verrous de page (page-level locking), mais prend également en charge les verrous au niveau de la table ；InnoDB. moteur de stockage Les verrous au niveau de la ligne (row-level locking) et les verrous au niveau de la table sont pris en charge, mais les verrous au niveau de la ligne sont utilisés par défaut.
Verrouillage au niveau de la table : faible surcharge, verrouillage rapide ; pas de blocage ; grande granularité de verrouillage, probabilité la plus élevée de conflits de verrouillage et concurrence la plus faible.
Verrouillage au niveau de la ligne : une surcharge élevée et un verrouillage lent se produiront ; la granularité du verrouillage est la plus petite, la probabilité de conflit de verrouillage est la plus faible et la concurrence est la plus élevée.
Verrouillage de page : la surcharge et le temps de verrouillage se situent entre les verrous de table et les verrous de ligne ; des blocages se produiront ; la granularité du verrouillage se situe entre les verrous de table et les verrous de ligne, et la concurrence est moyenne
Cela peut Il ressort des caractéristiques ci-dessus qu'il est difficile de dire quelle serrure est la meilleure en général. Nous pouvons seulement dire quelle serrure est la plus adaptée en fonction des caractéristiques d'applications spécifiques ! Juste du point de vue du verrouillage : les verrous au niveau des tables sont plus adaptés aux applications qui sont principalement basées sur des requêtes et ne mettent à jour qu'une petite quantité de données en fonction des conditions d'index, telles que les applications Web, tandis que les verrous au niveau des lignes sont plus adaptés aux applications ; qui comportent un grand nombre de mises à jour simultanées basées sur des conditions d'index et un petit nombre de données différentes, ainsi que des applications de requêtes simultanées, telles que certains systèmes de traitement de transactions en ligne (OLTP).

Verrouillage de table MyISAM

Les verrous MySQL au niveau de la table ont deux modes : le verrouillage en lecture partagé de la table (Table Read Lock) et le verrouillage en écriture exclusif de la table (Verrouillage d'écriture de table).
Les opérations de lecture sur la table MyISAM ne bloqueront pas les demandes de lecture des autres utilisateurs pour la même table, mais bloqueront les demandes d'écriture sur la même table ; les opérations d'écriture sur la table MyISAM bloqueront les demandes de lecture et d'écriture des autres utilisateurs sur la même table ; Opérations d'écriture ; les opérations de lecture et d'écriture de la table MyISAM, ainsi que les opérations d'écriture, sont en série ! D'après l'exemple présenté dans le tableau 20-2, nous pouvons savoir que 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é.

Exemple de lecture bloquant le verrouillage en écriture du moteur de stockage MyISAM :
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é.

Exemple de verrouillage en lecture du moteur de stockage MyISAM bloquant l'écriture :
Une session utilise la commande LOCK TABLE pour ajouter un verrou en lecture à la table film_text. Cette session peut interroger le verrouillé. enregistrements de la table, mais la mise à jour ou l'accès à d'autres tables provoqueront des erreurs en même temps, une autre session peut interroger les enregistrements de la table, mais les mises à jour entraîneront des attentes de verrouillage.

Comment ajouter un verrou 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. L'intervention de l'utilisateur n'est pas requise, les utilisateurs n'ont donc généralement pas besoin d'utiliser directement la commande LOCK TABLE pour verrouiller explicitement la table MyISAM. Dans les exemples, le verrouillage explicite est principalement destiné à des fins de démonstration 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 :

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

À ce stade, si vous ne verrouillez pas d'abord les deux tables, des résultats erronés peuvent survenir, car lors de l'exécution du première instruction, la table order_detail a peut-être changé. Par conséquent, la méthode correcte devrait être :

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

Expliquez spécifiquement les deux points suivants :
1. L'exemple ci-dessus ajoute l'option "local" à LOCK TABLES, qui est utilisée pour satisfaire la concurrence des tables MyISAM. . Dans le cas de conditions d'insertion, d'autres utilisateurs sont autorisés à insérer simultanément des enregistrements à la fin de la table. La question de l'insertion simultanée dans la table MyISAM sera abordée plus en détail ultérieurement.
2. 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 des 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 d'un 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).

Lorsque vous utilisez LOCK TABLES, non seulement vous devez verrouiller toutes les tables utilisées en même temps, mais aussi, combien de fois la même table apparaît dans l'instruction SQL, vous devez la verrouiller via le même alias que dans l'instruction SQL. Instruction SQL. Sinon, quelque chose peut mal se passer ! Un exemple est donné ci-dessous.

(1) Obtenez un verrou en lecture sur la table des acteurs :

mysql> lock table actor read; 
Query OK, 0 rows affected (0.00 sec)

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

mysql> select a.first_name,a.last_name,b.first_name,b.last_name 
from actor a,actor b 
where a.first_name = b.first_name and a.first_name = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table ‘a’ was not locked with LOCK TABLES

(3) Le l'alias doit être verrouillé séparément :

mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select a.first_name,a.last_name,b.first_name,b.last_name 
from actor a,actor b where a.first_name = b.first_name 
and a.first_name = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
+————+———–+————+———–+ 
| first_name | last_name | first_name | last_name | 
+————+———–+————+———–+ 
| Lisa | Tom | LISA | MONROE | 
+————+———–+————+———–+ 
1 row in set (0.00 sec)

Confirmation de verrouillage au niveau de la table de requête

Les conflits de verrouillage de table sur le système peuvent être analysés en vérifiant les variables d'état table_locks_waited et table_locks_immediate :

mysql> show status like 'table%';
1Variable_name | Value 
Table_locks_immediate | 2979 
Table_locks_waited | 0 
2 rows in set (0.00 sec))

Si la valeur de Table_locks_waited est relativement élevée, cela indique qu'il existe un problème grave. conflit de verrouillage au niveau de la table.

Insertions simultanées

Comme mentionné ci-dessus, la lecture et l'écriture des tables MyISAM sont en série, mais c'est globalement. 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.

1. Lorsque concurrent_insert est défini sur 0, l'insertion simultanée n'est pas autorisée.

2. Lorsque concurrent_insert est défini sur 1, s'il n'y a aucun trou 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 pendant un autre processus insère à partir de la fin du tableau. C'est également le paramètre par défaut pour MySQL.

3. 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.

Dans l'exemple suivant, session_1 obtient le verrou READ LOCAL d'une table. Ce thread peut interroger la table, mais ne peut pas mettre à jour la table (session_2), bien qu'ils ne puissent pas interroger la table, supprimer et mettre à jour ; les opérations sont effectuées, mais des opérations d'insertion simultanées peuvent être effectuées sur la table. On suppose qu'il n'y a pas de trous dans la table.

Comme mentionné ci-dessus, la lecture et l'écriture des tables MyISAM sont en série, mais c'est le cas 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 aucun trou 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 à partir de la fin de la table. le 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.

Dans l'exemple suivant, session_1 obtient le verrou READ LOCAL d'une table. Ce thread peut interroger la table, mais ne peut pas mettre à jour la table (session_2), bien qu'ils ne puissent pas interroger la table, supprimer et mettre à jour ; les opérations sont effectuées, mais des opérations d'insertion simultanées peuvent être effectuées sur la table. On suppose qu'il n'y a pas de trous dans la table.

Exemple de simultanéité de lecture et d'écriture (INSERT) du moteur de stockage MyISAM :

Vous pouvez utiliser la fonctionnalité d'insertion simultanée du moteur de stockage MyISAM pour résoudre le problème de insérer la même table dans l'application. Verrouiller les conflits pour les requêtes et les insertions. 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.

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 verrous en lecture et en écriture les opérations 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.

1. 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.

2. En exécutant la commande SET LOW_PRIORITY_UPDATES=1, la priorité de la demande de mise à jour émise par la connexion est réduite.

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

Bien que les trois méthodes ci-dessus soient soit la mise à jour d'abord, soit la requête d'abord, elles peuvent toujours être utilisées pour résoudre le grave problème de l'attente de verrouillage en lecture dans les applications où les requêtes sont relativement importantes (telles que les systèmes de connexion des utilisateurs).
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 bloquera temporairement la demande d'écriture. . La priorité est abaissée, donnant 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. Il faut également le souligner ici : certaines opérations de requête qui nécessitent de longs temps d'exécution vont également « affamer » 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.

Je vous expliquerai le verrouillage InnoDB plus tard.

Pour plus de questions connexes, veuillez visiter le site Web PHP chinois : Tutoriel vidéo MySQL

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!