Explication détaillée du principe du mécanisme de verrouillage MySQL (2)

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.

Verrouillage InnoDB

La plus grande différence entre InnoDB et MyISAM réside dans deux points :

Premièrement, il prend en charge TRANSACTION

La seconde est l'utilisation de 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.

1. 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 4 attributs, généralement appelés les attributs ACID de la. transaction.

1. 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 pas exécutées du tout.

2. Cohérent : les données doivent rester cohérentes lorsqu'une transaction est démarrée et terminée. Cela signifie que toutes les règles de données pertinentes doivent être appliquées aux modifications de transaction pour maintenir l'intégrité ; à 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.

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

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

2. Problèmes causés par les 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 en charge plus d'utilisateurs. Cependant, le traitement simultané des transactions entraînera également certains problèmes, notamment les situations suivantes.

1. 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, puisque chaque transaction n'est pas au courant de l'existence des autres transactions, un problème de mise à jour perdue. se produit - 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 cette transaction ne soit soumise, les données de cet enregistrement sont dans un état incohérent à ce moment-là, une autre transaction vient également en lecture si le même enregistrement est effectué. prise sans contrôle, la deuxième transaction lit les données « sales » et effectue un traitement ultérieur en conséquence, ce qui entraînera des dépendances de données non validées. Ce phénomène est vivement appelé « lecture sale ».

3. Lectures non répétables : Une transaction a changé lors de la lecture de certaines données, ou 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". ".

3. Niveau d'isolement des transactions Parmi les problèmes causés par le traitement simultané des transactions, 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.

Dans le contrôle de concurrence MVCC, les opérations de lecture peuvent être divisées en deux catégories : la lecture d'instantané et la lecture en cours. La lecture d'instantané lit la version visible de l'enregistrement (qui peut être la version historique) sans verrouillage. La lecture actuelle lit la dernière version de l'enregistrement et l'enregistrement renvoyé par la lecture actuelle sera verrouillé pour garantir que d'autres transactions ne modifieront pas cet enregistrement simultanément.
Dans un système prenant en charge le contrôle de concurrence MVCC, quelles opérations de lecture sont des lectures d'instantanés ? Quelles opérations sont actuellement en cours de lecture ? Prenons MySQL InnoDB comme exemple :

Lecture d'instantané : une simple opération de sélection, qui est une lecture d'instantané et ne nécessite pas de verrous. (Bien sûr, il y a des exceptions)

select * from table where ?;

Lecture actuelle : les opérations de lecture spéciales, les opérations d'insertion/mise à jour/suppression, appartiennent à la lecture en cours et doivent être verrouillées.
Les déclarations suivantes sont toutes des lectures actuelles, en lisant la dernière version du dossier. De plus, après la lecture, il faut également s'assurer que d'autres transactions concurrentes ne puissent pas modifier l'enregistrement en cours et verrouiller l'enregistrement lu. Parmi elles, à l'exception de la première instruction, qui ajoute un verrou S (verrouillage partagé) à l'enregistrement lu, toutes les autres opérations ajoutent un verrou X (verrouillage exclusif).

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 incompatible avec " " La concurrence" est un oxymore. 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 que la logique métier requiert. que la contradiction entre « isolement » et « concurrence » soit compensée en sélectionnant différents niveaux d'isolement. Le tableau suivant résume bien les caractéristiques de ces 4 niveaux d’isolement.

Obtenir la contention de verrouillage de ligne InonoD

Vous pouvez analyser le système en vérifiant la variable d'état InnoDB_row_lock Row conflit de verrouillage :

mysql> show status like 'innodb_row_lock%';

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 définir InnoDB Surveille pour observer plus en détail les tables, les lignes de données, etc. présentant des conflits de verrouillage et analyser les causes des conflits de verrouillage.

Mode de verrouillage de ligne InnoDB et méthode de verrouillage

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

Verrou(s) partagé(s) : également appelé verrouillage en lecture. Permet à une transaction de lire une ligne, empêchant ainsi d'autres transactions d'obtenir un verrou exclusif sur le même ensemble de données. Si la transaction T ajoute un verrou S à l'objet de données A, la transaction T peut lire A mais ne peut pas modifier A. Les autres transactions ne peuvent ajouter qu'un verrou S à A, mais ne peuvent pas ajouter un verrou X, jusqu'à ce que T libère le verrou S sur A. Cela garantit que les autres transactions peuvent lire A, mais ne peuvent apporter aucune modification à A jusqu'à ce que T libère le verrou S sur A.

Verrouillage exclusif (X) : également appelé verrouillage en écriture. Autorisez les transactions qui acquièrent des verrous exclusifs à mettre à jour les données et empêchez d'autres transactions d'acquérir des verrous de lecture partagés et des verrous d'écriture exclusifs sur le même ensemble de données. Si la transaction T ajoute un verrou X à l'objet de données A, la transaction T peut lire ou modifier A. Les autres transactions ne peuvent pas ajouter de verrous supplémentaires à A jusqu'à ce que T libère le verrou sur A.
Tout le monde peut avoir une bonne compréhension des verrous partagés, c'est-à-dire que plusieurs transactions ne peuvent que lire les données mais ne peuvent pas les modifier.

对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表:

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。
事务可以通过以下语句显式给记录集加共享锁或排他锁：

1、共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

2、排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示：
创建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
mysql> alter table tab_with_index add index id(id);

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

mysql> alter table tab_with_index drop index name;
1
Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 
Warnings: 0
mysql> insert into tab_with_index  values(1,'4');
1
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_with_index where id = 1;

InnoDB存储引擎使用相同索引键的阻塞例子 ：

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);
1Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 
Warnings: 0

InnoDB存储引擎的表使用不同索引的阻塞例子 ：

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

mysql> explain select * from tab_with_index where name = 1 \G
mysql> explain select * from tab_with_index where name = '1' \G

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！下面这个例子假设emp表中只有101条记录，其empid的值分别是1,2,……,100,101。 
InnoDB存储引擎的间隙锁阻塞例子 

小结

本文重点介绍了MySQL中MyISAM表级锁和InnoDB行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决办法。

Pour les verrous de table MyISAM, les points suivants sont principalement abordés :
(1) Les verrous en lecture partagés (S) sont compatibles, mais les verrous en lecture partagés (S) et les verrous en écriture exclusifs (X) sont compatibles, et l'exclusivité le verrouillage en écriture (X) s'exclut mutuellement, ce qui signifie que la lecture et l'écriture sont en série.
(2) Sous certaines conditions, MyISAM permet l'exécution simultanée des requêtes et des insertions. Nous pouvons utiliser cela 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. Utiliser les instructions DELETE.
(4) En raison de la grande granularité de verrouillage des verrous de table et de la sérialisation entre la lecture et l'écriture, s'il y a de nombreuses opérations de mise à jour, la table MyISAM peut avoir de sérieuses attentes de verrouillage. Vous pouvez envisager d'utiliser les tables InnoDB pour réduire les conflits de verrous. .

Pour les tables InnoDB, cet article traite principalement du contenu suivant :
(1) Les verrous de ligne d'InnoDB sont basés sur des index. Si les données ne sont pas accessibles via les index, InnoDB utilisera des verrous de table.
(2) Présente le mécanisme de verrouillage d'espacement InnoDB (Next-key) et les 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.

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 à des ajustements de conception et SQL, notamment :

Utiliser autant que possible un niveau d'isolement inférieur et concevoir soigneusement les index ; Essayez d'utiliser des index pour accéder aux données afin de rendre 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 sera plus faible lors du verrouillage explicite de l'ensemble d'enregistrements ; pour le demander en une seule fois. Niveau de verrouillage suffisant. 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. Cela est sujet à un blocage lorsque différents programmes accèdent à un groupe de données. tables, ils doivent essayer d'accepter d'accéder dans le même ordre. Chaque table, pour une table, accède autant que possible aux lignes du tableau dans un ordre fixe. Cela peut réduire considérablement le risque de blocage ; essayez d'utiliser des conditions égales pour accéder aux données, afin d'éviter l'impact des verrous d'espacement sur les insertions simultanées ; n'appliquez pas plus de niveaux de verrouillage que ceux réellement requis ; n'affichez pas les verrous lors de l'interrogation, sauf si cela est nécessaire ; ; pour certaines transactions spécifiques, vous pouvez utiliser des verrous de table pour augmenter la vitesse de traitement ou réduire le risque de blocage.

Pour plus de contenu connexe, 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!