Quelle est la différence entre les verrous de table et les verrous de ligne dans MySQL

La différence entre les verrous de table et les verrous de ligne dans MySQL est la suivante : 1. Les verrous de table favorisent le moteur de stockage myisam et les verrous de ligne favorisent le moteur de stockage innodb. 2. Les verrous de table ont une petite surcharge, tandis que les verrous de ligne en ont ; une surcharge importante ;3. Verrouillage de table La granularité du verrouillage est grande et la granularité du verrouillage de ligne est faible.

Cet article présentera en détail les verrous de table et de ligne MySQL, et analysera et comparera les différences. J'espère que ce sera une référence pour tout le monde.

(Tutoriel vidéo recommandé : Tutoriel vidéo mysql )

1. Verrouillage de table

Caractéristiques : Moteur de stockage MyISAM préféré, La surcharge est faible, le verrouillage est rapide et il n'y a pas de blocage. La granularité du verrouillage est grande, la probabilité de conflit de verrouillage est la plus élevée et la concurrence est la plus faible.

Lorsque nous modifions une table ou exécutons une instruction pour modifier une table, nous ajoutons généralement un verrou de table à la table pour éviter certaines choses asynchrones : L'un est une lecture. lock et l’autre est un verrou en écriture.

Nous pouvons ajouter manuellement ces deux verrous à la table. L'instruction est :

lock table 表名 read(write);

Libérer les verrous de toutes les tables :

unlock tables;

Afficher les verrous. Tableau :

show open tables;

Ajouter un verrou en lecture (verrou partagé) :

Quel sera l'effet si nous ajoutons un verrou en lecture à la table ?

1. Le processus auquel nous avons ajouté le verrou de lecture peut lire la table avec le verrou de lecture, mais ne peut pas lire les autres tables.

2. Le processus avec verrou en lecture ne peut pas mettre à jour la table avec verrou en lecture.

3. D'autres processus peuvent lire la table verrouillée en lecture (car il s'agit d'un verrou partagé), et peuvent également lire d'autres tables

4. table verrouillée en lecture La table sera toujours en attente du verrou et la mise à jour ne réussira pas tant que le verrou ne sera pas libéré.

Verrouillage en écriture (verrouillage exclusif) :

1. Le processus de verrouillage peut effectuer n'importe quelle opération (CURD) sur la table verrouillée.

2. Les autres processus ne peuvent pas interroger la table verrouillée et doivent attendre que le verrou soit libéré

Résumé :

Lire le verrou bloquera l'écriture, mais ne bloquera pas la lecture. Le verrou en écriture bloquera à la fois la lecture et l’écriture. (Portez une attention particulière au processus)

Analyse :

show status like 'table%';

Entrez la commande ci-dessus pour obtenir :

+----------------------------+----------+
| Variable_name        | Value |
+----------------------------+----------+
| Table_locks_immediate | 105         |
| Table_locks_waited   | 3           |
+----------------------------+----------+

Table_locks_immediate : le nombre de tables- verrous de niveau générés, indiquant le nombre de requêtes pouvant acquérir immédiatement le verrou, et la valeur du verrou est augmentée de 1 pour chaque acquisition immédiate.

Table_locks_waited : le nombre de fois d'attente en raison d'un conflit de verrouillage au niveau de la table (le nombre de fois où le verrou ne peut pas être obtenu immédiatement, la valeur du verrou augmente de 1 pour chaque attente), une valeur élevée indique l'existence d'une table plus sérieuse Conditions de conflit de verrouillage de niveau.

2. Verrouillage de ligne

Caractéristiques : favorise le moteur de stockage InnoDB, une surcharge élevée, des blocages lents peuvent survenir ; la granularité du verrouillage est minime et la probabilité de conflits de verrouillage ; se produit Le degré de concurrence le plus bas et le plus élevé.

Le verrouillage de ligne prend en charge les transactions, les connaissances sur les transactions seront donc résumées dans le prochain blog.

Comportement :

1. Lorsque nous mettons à jour une ligne mais ne la soumettons pas, d'autres processus mettent également à jour la ligne et doivent attendre. Il s'agit du verrouillage de la ligne.

2. Si nous mettons à jour une ligne, les autres processus mettant à jour d'autres lignes ne seront pas affectés.

Le verrouillage de ligne est mis à niveau vers le verrouillage de table :

Lorsque notre verrouillage de ligne implique un échec d'index, le comportement de verrouillage de table sera déclenché.

Normalement, chacun verrouille sa propre ligne et ne s'affecte pas l'un l'autre. L'un vaut 2000 et l'autre vaut 3000.

Puisqu'un index est construit sur le champ de colonne b, s'il est. n'est pas utilisé normalement, cela entraînera le changement du verrouillage de ligne en verrouillage de table

Par exemple, si des guillemets simples ne sont pas ajoutés, l'index devient invalide, le verrouillage de ligne se transforme en verrouillage de table

et est bloqué , en attendant. Ce n'est qu'après la soumission de Session_1 que le blocage est libéré et la mise à jour est terminée

Donc, à partir de là, nous devons encore faire bon usage des requêtes d'index.

Verrouillage des espaces :

Lorsque nous récupérons des données en utilisant des conditions de plage au lieu de conditions d'égalité et demandons des verrous partagés ou exclusifs, InnoDB donnera les données existantes qui répondent aux conditions L'index l'entrée de l'enregistrement est verrouillée ; pour l'enregistrement dont la valeur de clé est dans la plage de conditions mais n'existe pas, cela s'appelle un "espace". InnoDB verrouillera également cet "espace". Ce mécanisme de verrouillage est ce qu'on appelle le verrouillage d'espace. ( Verrouillage à clé suivante).

Parce que si Query réussit une recherche de plage lors de l'exécution, il verrouillera toutes les valeurs de clé d'index dans toute la plage, même si la valeur de clé n'existe pas.

Le verrouillage des espaces a une faiblesse fatale, c'est-à-dire qu'après avoir verrouillé une plage de valeurs clés, même certaines valeurs clés inexistantes seront innocemment verrouillées, ce qui rendra impossible leur insertion lorsqu'elles sont verrouillées. toutes les données comprises dans la plage de valeurs clés. Dans certains scénarios, cela peut nuire gravement aux performances

Suggestions d'optimisation :

Autant que possible, toutes les récupérations de données doivent être effectuées via des index pour éviter les verrouillages inutiles des lignes d'index. sont mis à niveau vers des verrous de table.

Concevez l'index de manière raisonnable et réduisez autant que possible la portée des verrous

Aussi peu de conditions de recherche que possible pour éviter les verrous d'espacement

Essayez de contrôler la taille des transactions et réduisez la quantité de ressources verrouillées et la durée

Isolement des transactions de bas niveau possible

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!