Introduction détaillée au mécanisme de verrouillage dans la base de données MySQL

Cet article vous apporte une introduction détaillée au mécanisme de verrouillage dans la base de données MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer. J'espère qu'il vous sera utile.

Verrouillage pessimiste et verrouillage optimiste :
Verrouillage pessimiste : comme son nom l'indique, il est très pessimiste. Chaque fois que vous obtenez les données, vous pensez que d'autres les modifieront, vous les verrouillerez donc à chaque fois. vous obtenez les données. De cette façon, si d'autres personnes souhaitent obtenir ces données, elles les bloqueront jusqu'à ce qu'elles obtiennent le verrou. De nombreux mécanismes de verrouillage de ce type sont utilisés dans les bases de données relationnelles traditionnelles, 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.

Verrouillage optimiste : comme son nom l'indique, il est très optimiste. Chaque fois que vous allez récupérer les données, vous pensez que les autres ne les modifieront pas, donc vous ne les verrouillerez pas, mais quand vous le ferez. mettez-le à jour, vous jugerez que d'autres ne le modifieront pas pendant cette période. Avez-vous mis à jour ces données ? Vous pouvez utiliser des mécanismes tels que les numéros de version. Le verrouillage optimiste convient aux types d'applications à lectures multiples, ce qui peut améliorer le débit. Si une base de données fournit un mécanisme similaire à write_condition, elle fournit en fait un verrouillage optimiste.

Niveau table : Le moteur MyISAM verrouille directement toute la table. Pendant votre période de verrouillage, les autres processus ne peuvent pas écrire dans la table. Si vous disposez d'un verrou en écriture, les autres processus ne sont pas autorisés à lire

Niveau page : Moteur BDB, les verrous au niveau de la table sont rapides mais ont de nombreux conflits, les verrous au niveau de la ligne ont peu de conflits mais sont lents. Un compromis a donc été fait au niveau de la page, verrouillant un groupe d'enregistrements adjacents à la fois

niveau ligne : le moteur INNODB, verrouille uniquement les enregistrements spécifiés, afin que d'autres processus puissent toujours verrouiller la même table pour opérer sur d’autres enregistrements.

Les caractéristiques des trois verrous ci-dessus peuvent être grossièrement résumées comme suit :
1) Verrouillage au niveau de la table : faible surcharge, verrouillage rapide ; pas de blocage important, un conflit de verrouillage se produit ; probabilité la plus élevée et concurrence la plus faible.
2) 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.
3) 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 le degré de concurrence est également le plus élevé.

Les trois types de verrous ont chacun leurs propres caractéristiques. Du point de vue du verrouillage, les verrous au niveau de la table sont plus adaptés aux applications qui se concentrent sur les requêtes et mettent à jour uniquement les données en fonction des conditions d'index, telles que WEB. Application ; le verrouillage au niveau des lignes est plus adapté aux applications dans lesquelles un grand nombre de données différentes sont mises à jour simultanément en fonction des conditions d'index et où des requêtes simultanées sont effectuées, comme certains systèmes de traitement de transactions en ligne (OLTP).

Les verrous MySQL au niveau de la table ont deux modes :
1. Verrouillage en lecture partagé de la table (Table Read Lock). Lors de l'exécution d'une opération de lecture sur une table MyISAM, cela ne bloquera pas les demandes de lecture des autres utilisateurs pour la même table, mais bloquera les opérations d'écriture sur la même table
2. Les opérations d'écriture sur la table MyISAM bloqueront les opérations de lecture et d'écriture des autres utilisateurs sur la même table.

La lecture et l'écriture des tables MyISAM sont en série, c'est-à-dire que les opérations d'écriture ne peuvent pas être effectuées pendant que les opérations de lecture sont effectuées, et vice versa. Cependant, sous certaines conditions, la table MyISAM prend également en charge les opérations de requête et d'insertion simultanées. Le mécanisme est effectué en contrôlant une variable système (concurrent_insert). Lorsque sa valeur est définie sur 0, l'insertion simultanée n'est pas autorisée lorsque sa valeur est définie lorsque. c'est 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 dans 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 lorsque sa valeur ; est défini sur 2, que MyISAM ou qu'il y ait un trou dans la table, l'insertion simultanée d'enregistrements à la fin de la table est autorisée.

La façon dont la planification des verrouillages MyISAM est mise en œuvre est également un problème très critique. Par exemple, lorsqu'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 donnera-t-il la priorité au processus ? Des recherches ont montré que le processus d'écriture acquiert le verrou en premier (c'est-à-dire que la demande de lecture arrive en premier dans la file d'attente du verrou). Mais cela provoque également un gros défaut, c'est-à-dire qu'un grand nombre d'opérations d'écriture rendront difficile l'obtention de verrous de lecture pour les opérations de requête, ce qui peut provoquer un blocage permanent. Heureusement, nous pouvons ajuster le comportement de planification de MyISAM via certains paramètres. Nous pouvons faire en sorte que le moteur par défaut de MyISAM donne la priorité aux requêtes de lecture en spécifiant le paramètre low-priority-updates et en définissant sa valeur sur 1 (set low_priority_updates=1) pour réduire la priorité.

La plus grande différence entre le verrou InnoDB et le verrou MyISAM est :
1. Il prend en charge les transactions (TRANCSACTION).
2. Le verrouillage au niveau de la ligne est utilisé.

Nous savons qu'une transaction est une unité de traitement logique composée d'un ensemble d'instructions SQL. Elle possède quatre attributs (attributs ACID en abrégé), qui sont :
Atomicité : Une transaction est une Unité d'opération atomique, dont les modifications des données sont soit toutes exécutées, soit pas exécutées du tout ;
Cohérent : les données doivent rester cohérentes au début et à la fin de la transaction
Isolement (Isolation) : 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 concurrentes externes
Durable : Une fois une transaction terminée, sa modification des données est permanente, même en cas de panne du système ; sont également entretenus.

Problèmes causés par le traitement des transactions simultanées
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.
1. Mise à jour perdue : se produit 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, car chaque transaction ignore l'existence des autres transactions. Problème de mise à jour perdue - la dernière mise à jour. écrase les mises à jour effectuées par d'autres transactions. 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.
2. 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. non contrôlée et qu'une seconde transaction lit les données « sales » et effectue un traitement ultérieur en conséquence, des dépendances de données non validées se produiront. Ce phénomène est vivement appelé « lecture sale ».
3. Lectures non répétables : une transaction lit à nouveau les données précédemment lues à un certain moment après avoir lu certaines données, pour constater que les données qu'elle a lues ont changé ou que certains enregistrements ont été supprimés ! Ce phénomène est appelé « lecture non répétable ».
4. Lectures fantômes : une transaction relit les données précédemment récupérées selon les mêmes conditions de requête, mais constate que d'autres transactions ont inséré de nouvelles données qui satisfont à ses conditions de requête. Ce phénomène est appelé "lecture fantôme".

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.
1. La première consiste à verrouiller les données avant de les lire pour empêcher d'autres transactions de modifier les données.
2. 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 cohérence. lecture. 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.

Plus l'isolation des transactions de la base de données est stricte, plus les effets secondaires simultanés 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. à la « concurrence ». 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 « isolation » et « concurrence », ISO/ANSI SQL92 définit 4 niveaux d'isolation des transactions. Chaque niveau a un degré d'isolation différent et permet différents effets secondaires. Les applications peuvent répondre à leurs propres exigences de 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.

Lire la cohérence des données et les effets secondaires de concurrence autorisés
Niveau d'isolement Lire la cohérence des données Lecture sale Lecture non répétable Lecture fantôme
Lecture non validée Le niveau le plus bas, uniquement garanti Ne pas lire les données physiquement endommagées Oui Oui Oui
Lecture validée Niveau de déclaration Non Oui Oui
Lecture répétable (Lecture répétable) Niveau de transaction Non Non Oui
Sérialisable Niveau le plus élevé, niveau de transaction Non Non Non

La dernière chose à noter est que chaque base de données spécifique n'implémente pas nécessairement entièrement les quatre niveaux d'isolement ci-dessus. Par exemple, 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. En plus de prendre en charge les quatre niveaux d'isolement définis par ISO/ANSI SQL92 ci-dessus, SQL Server 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 une implémentation spécifique, il existe certaines caractéristiques. Par exemple, dans certains niveaux d'isolement, la lecture de cohérence MVCC est utilisée, mais dans certains cas, ce n'est pas le cas
InnoDB a deux modes de verrouillage de ligne :
1) Verrou partagé (S) : permet à une transaction de lire une ligne et empêche d'autres transactions d'obtenir un verrou exclusif sur le même ensemble de données.
(Sélectionnez * dans table_name où ……verrouillez en mode partage)
2) Verrouillage exclusif (X) : autorisez les transactions qui obtiennent des verrous exclusifs à mettre à jour les données et empêchez d'autres transactions d'obtenir des verrous de lecture partagés et des écritures exclusives du même ensemble de données Lock. (sélectionnez * from table_name où…..pour la mise à jour)
Afin de permettre aux verrous de ligne et de table de coexister, un mécanisme de verrouillage multi-granularité est implémenté, il existe également deux verrous d'intention utilisés en interne (les deux verrous de table), à savoir les verrous partagés d’intention et les verrous exclusifs d’intention.
1) Intention de verrouillage partagé (IS) : la transaction a l'intention d'ajouter un verrou partagé 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.
2) Verrouillage 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.
Liste de compatibilité du mode de verrouillage de ligne InnoDB
Mode de verrouillage demandé
Est-il compatible
Mode de verrouillage actuel X IX S IS
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
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 pas l'intervention de l'utilisateur. Pour les instructions UPDATE, DELETE et INSERT, InnoDB ajoutera automatiquement des verrous exclusifs (X) aux ensembles de données impliqués ; pour les instructions SELECT ordinaires, InnoDB n'ajoutera aucun verrou partagé ou exclusif à l'ensemble d'enregistrements via ce qui suit : déclarations.
1. Verrou partagé (S) : SELECT * FROM nom_table OÙ… VERROUILLER EN MODE PARTAGE.
2. Verrou exclusif (X) : SELECT * FROM table_name WHERE ... FOR UPDATE.
Le verrouillage des lignes InnoDB est obtenu en verrouillant les entrées d'index sur l'index. Ceci est différent de MySQL et Oracle, qui est obtenu en verrouillant les lignes de données correspondantes dans le bloc de données. La fonctionnalité d'implémentation du verrouillage de ligne d'InnoDB signifie qu'InnoDB utilise des verrous au niveau des lignes 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 cela peut conduire à un grand nombre de conflits de verrouillage, affectant ainsi les performances de concurrence.


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

Confliction de verrouillage de table :

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 :

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

mysql> show status like ‘table%’; 
+———————–+——-+ 
| Variable_name         | Value | 
+———————–+——-+ 
| Table_locks_immediate | 2979  | 
| Table_locks_waited    | 0     | 
+———————–+——-+ 
2 rows in set (0.00 sec))

Confliction 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 :

Expérience de verrouillage en écriture MyISAM :

mysql> show status like ‘innodb_row_lock%’; 
+——————————-+——-+ 
| Variable_name                 | Value | 
+——————————-+——-+ 
| InnoDB_row_lock_current_waits | 0     | 
| InnoDB_row_lock_time          | 0     | 
| InnoDB_row_lock_time_avg      | 0     | 
| InnoDB_row_lock_time_max      | 0     | 
| InnoDB_row_lock_waits         | 0     | 
+——————————-+——-+ 
5 rows in set (0.01 sec)

Pour MyISAM L'opération de lecture de la table 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 les opérations de lecture et d'écriture des autres utilisateurs sur la même table ; table ; table MyISAM Les opérations de lecture et d'écriture, 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é.

USER1 :

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

mysql> lock table film_text write;

USER2 :

mysql> select film_id,title from film_text where film_id = 1001;

En attente

mysql> select film_id,title from film_text where film_id = 1001;

USER1 :

Libérer le verrou :

USER2 :

mysql> unlock tables;

Obtenir le verrou, la requête renvoie :

Stockage InnoDB moteur Expérience de verrouillage partagé

USER1 :

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

La session en cours ajoute le verrouillage partagé en mode partage à l'enregistrement de l'acteur_id=178 :

USER2 :

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

D'autres sessions peuvent toujours interroger l'enregistrement, et peuvent également ajouter un verrou partagé en mode partage à l'enregistrement :

USER1 :

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

La session en cours met à jour l'enregistrement verrouillé. En attente de verrouillage :

En attente de

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2 :

D'autres sessions mettent également à jour l'enregistrement, ce qui entraînera une sortie de blocage :

ERREUR 1213 (40001) : blocage détecté lors de la tentative d'obtention du verrou ; essayez de redémarrer la transaction

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER1 :

Après avoir obtenu le verrou, vous pouvez mettre à jour avec succès :

InnoDB Exemple de verrouillage exclusif du moteur de stockage

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; 
Query OK, 1 row affected (17.67 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

USER1： 
mysql> set autocommit = 0; 
USER2： 
mysql> set autocommit = 0;

USER1：
当前session对actor_id=178的记录加for update的排它锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2：
其他session可以查询该记录，但是不能对该记录加共享锁，会等待获得锁：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178;

USER1：
当前session可以对锁定的记录进行更新操作，更新后释放锁：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2：
其他session获得锁，得到其他session提交的记录：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

更新性能优化的几个重要参数
bulk_insert_buffer_size
批量插入缓存大小,这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时,提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.
concurrent_insert
并发插入,当表没有空洞(删除过记录),在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.
值可以设0不允许并发插入, 1当表没有空洞时,执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1针对表的删除频率来设置.
delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘.值为 0不开启, 1开启.默认开启.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入,将数据先交给内存队列,然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持,目前来看,常用的InnoDB不支持, MyISAM支持.根据实际情况调大,一般默认够用了。

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!