Verrous MySQL et niveaux d'isolation des transactions (introduction)

Dans l’Internet d’aujourd’hui, vous ne pouvez pas développer une application multijoueur à grande échelle sans base de données. Comment garantir que tout le monde puisse lire et écrire avec une simultanéité élevée a toujours été un problème architectural difficile. Tout d'abord, la simultanéité élevée est éliminée, et la méthode la plus couramment utilisée pour garantir une lecture et une écriture cohérentes est la transaction, et le point clé de la mise en œuvre. une transaction est un mécanisme de verrouillage.

Aujourd'hui, nous présenterons le principe et la mise en œuvre de la manière dont le moteur de stockage InnoDB implémente le mécanisme de verrouillage sous haute concurrence pour assurer une lecture et une écriture cohérentes.

Verrouillage

Le mécanisme de verrouillage de la base de données est une caractéristique clé qui la distingue du système de fichiers. Utilisé pour gérer l’accès simultané aux ressources partagées. InnoDB utilise des mécanismes de verrouillage à de nombreux endroits, tels que les tables de données d'exploitation, les listes de pages LRU et les lignes de données dans le pool de mémoire tampon. Afin de garantir la cohérence et l'intégrité, un mécanisme de verrouillage est requis.

Pour différentes bases de données, la conception et la mise en œuvre du mécanisme de verrouillage sont complètement différentes :

● Moteur MyISAM : conception du verrouillage de table, la lecture simultanée ne pose aucun problème, mais les performances d'écriture simultanée sont médiocres.

● Microsoft SQL Server : prend en charge la concurrence optimiste et la concurrence pessimiste. La maintenance des verrous au niveau des lignes est coûteuse. Lorsque le nombre de verrous de ligne dépasse le seuil, il sera mis à niveau vers les verrous de table.

●Moteur InnoDB : prend en charge les verrouillages de lignes et fournit des lectures cohérentes non verrouillables. Les verrous de ligne n’entraînent aucune surcharge supplémentaire ni aucune dégradation des performances.

●Oracle : Très similaire au moteur InnoDB.

Deux types de verrous : verrou et loquet

Les verrous et les loquets dans la base de données peuvent être appelés verrous, mais il y a une grande différence .

Le verrou est généralement appelé un verrou, qui est utilisé pour garantir l'exactitude des ressources critiques exploitées par les threads simultanés. L'objet est une structure de données mémoire. Le temps de verrouillage doit être très court et il n'y aura pas de blocage. détecté. Dans le moteur InnoDB, il est divisé en mutex (mutex) et rwlock (verrouillage en lecture-écriture).

Le verrouillage est utilisé pour verrouiller les objets de la base de données, tels que les tables, les pages et les lignes. La cible est une transaction. Elle est libérée après la validation/restauration et un blocage sera détecté. Divisé en verrous de ligne, verrous de table et verrous d'intention.

Les serrures que nous avons ci-dessous font référence aux serrures de type serrure.

Quatre types de verrous

InnoDB prend en charge quatre verrous :

● Verrouillage partagé (S Lock) : permet les transactions Lire un ligne de données

● Verrouillage exclusif (X Lock) : permet à une transaction de supprimer ou de mettre à jour une ligne de données

● Verrouillage partagé d'intention (Intention S Lock) : une transaction souhaite obtenir certaines lignes dans une table Verrou partagé

● Verrouillage exclusif d'intention (Intention Les données de la ligne ne seront pas modifiées, donc la transaction T2 peut également obtenir directement le verrou partagé de la ligne r, qui est appelé compatible avec le verrouillage.

Lorsque la transaction T3 veut obtenir le verrou exclusif de la ligne r pour modifier les données, elle doit attendre que T1/T2 libère le verrou partagé de la ligne. C'est ce qu'on appelle l'incompatibilité du verrouillage.

Les verrous S et X sont tous deux des verrous de ligne, tandis que les verrous IS et IX sont des verrous d'intention et appartiennent aux verrous de table. Les verrous d'intention sont conçus pour révéler le type de verrou qui sera demandé pour la ligne suivante d'une transaction, c'est-à-dire pour verrouiller avec la granularité plus fine d'un verrou de table. Étant donné qu'InnoDB prend en charge les verrous de table, les verrous d'intention ne bloqueront aucune requête, à l'exception des analyses complètes de table.

Compatibilité de verrouillage :

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

Trois tables stockant les informations de transaction et de verrouillageNous pouvons passer

Commande pour afficher les informations de la demande de verrouillage en cours dans la section transaction.

show engine innodb status À partir d'InnoDB1.0, INNODB_TRX (table de transaction), INNODB_LOCKS (table de verrouillage), INNODB_LOCK_WAITS (table d'attente de verrouillage) ont été ajoutés sous l'architecture INFORMATION_SCHEMA. Grâce à ces trois tables, nous pouvons surveiller la situation actuelle dans. en temps réel et analyser les éventuels problèmes de table.

Les définitions des trois tableaux sont :

INNODB_LOCKS
lock_id	锁ID
lock_trx_id	事务ID
lock_mode	锁的模式
lock_type	锁的类型，表锁或行锁
lock_table	要加锁的表
lock_index	锁住的索引
lock_space	锁对象的space id
lock_page	事务锁定页的数量，表锁时为NULL
lock_rec	事务锁定行的数量，表锁时为NULL
lock_data	事务锁定记录的主键值，表锁时为NULL

INNODB_LOCK_WAITS
requesting_trx_id	申请锁资源的事务ID
requesting_lock_id	申请的锁的ID
blocking_trx_id	阻塞的事务ID
blocking_lock_id	阻塞的锁的ID

Grâce à INNODB_TRX, nous pouvons voir toutes les transactions, si la transaction est bloquée et quel est l'ID de verrouillage bloqué. Après
, utilisez INNODB_LOCKS pour afficher toutes les informations de verrouillage. Après
, vous pouvez consulter les informations d'attente et la relation de blocage de la serrure via INNODB_LOCK_WAITS.

Grâce à ces trois tableaux, vous pouvez visualiser plus clairement l'état de la transaction et du verrouillage, et vous pouvez également effectuer des requêtes conjointes. Dans les scénarios suivants, nous afficherons le contenu de ces trois tableaux.

Niveau d'isolement

Parlons d'abord des quatre niveaux d'isolement des transactions de la base de données :

Quantity　LIRE UNCOMMITTED ( 0 ) : Niveau d'accès à la navigation, il y a des lectures sales, des lectures non répétables, des lectures fantômes

　 ● READ COMMITTED (1) : Niveau de stabilité du curseur, il y a des lectures non répétables, des lectures fantômes

● LECTURE RÉPÉTABLE (2) : Il existe des lectures fantômes

● SERIALIZABLE (3) : Niveau d'isolement, garantissant la sécurité des transactions, mais entièrement en série et peu performant

Ces quatre niveaux d'isolement des transactions sont spécifiés par la norme SQL. Le niveau d'isolement par défaut d'InnoDB est REAPEATABLE READ, mais contrairement à d'autres bases de données, il utilise également l'algorithme de verrouillage Next-Key-Lock, qui peut éviter l'apparition de lectures fantômes, afin de pouvoir répondre pleinement aux exigences d'isolation des transactions. , c'est-à-dire qu'il peut atteindre le niveau d'isolation SERIALIZABLE.

Plus le niveau d'isolement est bas, moins il y a de verrous demandés par la transaction ou plus le temps de maintien du verrou est court, de sorte que le niveau d'isolement par défaut de la plupart des bases de données est READ COMMITED. Cependant, une analyse pertinente souligne également que la surcharge de performances des niveaux d'isolation est presque la même, de sorte que les utilisateurs n'ont pas besoin d'ajuster le niveau d'isolation pour améliorer les performances.

Commandes pour visualiser et modifier le niveau d'isolement des transactions :

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

Dans l'exemple, le niveau d'isolement des transactions de cette session est modifié Si vous devez modifier les paramètres globaux, vous pouvez remplacer session. avec mondial. Si vous souhaitez apporter des modifications permanentes, vous devez modifier le fichier de configuration :

[mysqld]
transaction-isolation = READ-COMMITED

Au niveau d'isolation des transactions de SERIALIZABLE, InnoDB ajoutera automatiquement LOCK IN SHARE MODE après chaque instruction SELECT pour ajouter un verrou partagé au opération de lecture , les lectures non verrouillables cohérentes ne sont donc plus prises en charge.

Étant donné qu'InnoDB peut atteindre SERIALIZABLE au niveau d'isolement REPEATABLE READ, il n'est généralement pas nécessaire d'utiliser le niveau d'isolement le plus élevé.

Lecture non verrouillable cohérente et contrôle de concurrence multi-versions

Lecture non verrouillable cohérente (lecture non verrouillable cohérente) fait référence à InnoDB Méthode de contrôle de version multi-lignes (Multi Version Concurrency Control, MVCC) pour lire les données de ligne dans la base de données au moment de l'exécution actuel.

C'est-à-dire que si la ligne lue subit une opération de modification, la lecture n'attendra pas la libération du verrou de ligne, mais lira un instantané des données de la ligne. Un instantané fait référence à des données historiques de la ligne, qui sont complétées par l'opération d'annulation. Cette méthode améliore considérablement la concurrence de la base de données, qui est également le paramètre par défaut d'InnoDB.

Un instantané est une version historique de la ligne actuelle, mais il peut y avoir plusieurs versions. Les données de ligne contiennent plusieurs données d'instantané. Cette technologie devient une technologie multi-version de ligne et le contrôle de concurrence qui en résulte est appelé multi-. instantané. Contrôle de concurrence de versions (MVCC). InnoDB utilise des lectures cohérentes non verrouillables dans les niveaux d'isolement READ COMMITED et REPEATABLE READ, mais les définitions de données rapides utilisées dans ces deux niveaux d'isolement sont différentes :

●　READ COMMITED : toujours lire le dernier instantané

● LECTURE RÉPÉTABLE : lisez toujours la version des données de la ligne au début de la transaction

Nous exécutons un exemple :

td>

Lecture cohérente sans verrouillage

Heure

Session A

Session B

1

BEGIN

一致性非锁定读
时间	会话A	会话B
1	BEGIN
2	select * from z where a = 3;
3		BEGIN
4		update z set b=2 where a=3;
5	select * from z where a = 3;
6		COMMIT;
7	select * from z where a = 3;
8	COMMIT;

2

sélectionnez * à partir de z où a = 3 ;

3

BEGIN

4

mettre à jour z définir b= 2 où a=3;

5

sélectionner * à partir de z où a = 3 ;

6

COMMIT ;

7

sélectionner * à partir de z où a = 3;

8

COMMIT;

在这个例子中我们可以清晰的看到0、1、2三种隔离级别的区别：

#在事务开始前我们可以分别调整为0、1、2三种隔离级别，来查看不同的输出
mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where a = 3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update z set b=2 where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# A会话：T1事务，如果此时隔离级别是READ-UNCOMMITTED，因为此刻事务2可能会回滚，所以出现了脏读
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-UNCOMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# A会话：T1事务，如果此时隔离级别是READ-COMMITTED，因为数据和事务开始时读取的出现了不一致，因此称为不可重复读，能够读到其他事务的结果，违反了事务的隔离性
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-COMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

一致性锁定读和SERIALIZABLE隔离

在默认的REPEATABLE READ隔离级别时，InnoDB使用的是一致性非锁定读。但有时我们也需要显示的指定使用一致性锁定读来保证读取操作时对数据进行加锁达到一致性。这要求数据库支持锁定读加锁语句：

　●　select ... for update: 读取时对行记录加X锁

　●　select ... lock in share mode：读取时对行记录加一个S锁

这两种锁必须在一个事务中，当事务提交后锁也就释放了，因此务必加上BEGIN, START TRANSACTION或者SET AUTOCOMMIT=0。

我们在前面隔离级别时也说过SERIALIZABLE隔离级别会对读操作自动加上LOCK IN SHARE MODE指令来加上一个共享锁，因此不再支持一致性的非锁定读。这也是隔离级别3的一大特性。

总结

由于锁的概念非常重要，这里先讲了锁的概念、锁的类型、锁的信息查看、事务的隔离级别和区别，后面我们会继续说锁的算法、锁的三种问题和幻读、死锁和锁升级。

推荐学习：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!