Comprendre ce qu'est un verrou et comment résoudre le problème de lecture fantôme dans MySQL

La colonne

Tutoriel MySQL présente comment les verrous résolvent le problème de lecture fantôme.

Préface

Aujourd'hui, je vais vous présenter les connaissances liées aux verrous dans MySQL.

Sauf indication contraire, cet article utilise le moteur InnoDB par défaut. Si d'autres moteurs ou bases de données sont impliqués, ils seront spécifiquement signalés.

Qu'est-ce qu'un verrou

Un verrou est une méthode utilisée pour garantir que chaque transaction peut toujours lire et modifier les données de manière cohérente dans un scénario simultané. Une fois les données verrouillées, d'autres transactions. ne peut pas le modifier ou ne peut que bloquer et attendre que le verrou soit libéré. ​​Par conséquent, la granularité du verrou peut affecter dans une certaine mesure les performances d'accès à la base de données.

En termes de granularité des verrous, nous pouvons diviser les verrous en verrous de table et verrous de ligne.

Verrouillage de table

Comme son nom l'indique, le verrouillage de table consiste à verrouiller directement la table. Dans le moteur MyISAM, il n'y a que le verrouillage de table.

La méthode de verrouillage du verrouillage de table est :

LOCK TABLE 表名 READ;--锁定后表只读
UNLOCK TABLE; --解锁复制代码

Verrouillage de ligne

Le verrouillage de ligne, d'après le nom, consiste à verrouiller une ligne de données. Cependant, la mise en œuvre réelle de. verrouillage de ligne L'algorithme sera relativement complexe, et parfois il ne verrouille pas seulement un certain élément de données. Cela sera développé plus tard.

L'idée normale est la suivante : après avoir verrouillé une ligne de données, les autres transactions ne peuvent pas accéder à ces données. Ensuite, nous imaginons que si la transaction A accède à une donnée, elle la sort simplement pour la lire et ne veut pas le faire. modifiez-le. , il arrive que la transaction B vienne également accéder à ces données, et elle veut juste les extraire et les lire, et ne veut pas les modifier. Si elle est bloquée à ce moment-là, ce sera un peu. un gaspillage de performances. Ainsi, afin d'optimiser ce scénario de lecture de données, nous divisons les verrous de ligne en deux grands types : les verrous partagés et les verrous exclusifs .

Verrouillage partagé

Le verrouillage partagé, Shared Lock, est également appelé verrouillage en lecture, verrouillage S, ce qui signifie qu'après l'ajout d'une donnée avec un verrou S, d'autres transactions peuvent également lire le données Oui Partager un verrou.
Nous pouvons ajouter un verrou partagé via l'instruction suivante :

select * from test where id=1 LOCK IN SHARE MODE;复制代码

Après le verrouillage, le verrou sera libéré jusqu'à la fin de la transaction verrouillée (validation ou restauration).

Verrouillage exclusif

Verrouillage exclusif, verrouillage exclusif, également appelé verrouillage en écriture, verrouillage X. C'est-à-dire qu'après l'ajout d'un verrou X à une donnée, les autres transactions souhaitant accéder à ces données ne peuvent que bloquer et attendre que le verrou soit libéré, ce qui est exclusif.

MySQL ajoutera automatiquement un verrou exclusif lorsque nous modifierons des données, telles que l'insertion, la mise à jour, la suppression. De même, nous pouvons ajouter manuellement un verrou exclusif via l'instruction SQL suivante :

select * from test where id=1 for update;复制代码

Dans le. Le moteur InnoDB, les verrous de ligne et les verrous de table sont autorisés à coexister.

Mais il y aura un problème. Si la transaction A verrouille une ligne de données dans la table t et que la transaction B veut verrouiller la table t à ce moment-là, que devons-nous faire à ce moment-là ? Comment la transaction B sait-elle s'il y a un verrou de ligne dans la table t ? Si la traversée complète de la table est utilisée, lorsque les données de la table sont volumineuses, le verrouillage prendra une demi-journée, donc MySQL introduit le verrouillage d'intention.

Verrouillage d'intention

Le verrouillage d'intention est un verrou de table, qui est divisé en deux types : le verrouillage partagé d'intention et le verrouillage exclusif d'intention. Les verrous peuvent être appelés respectivement verrous IS et verrous IX.

Les verrous d'intention sont maintenus par MySQL lui-même et les utilisateurs ne peuvent pas ajouter manuellement d'intentions.

Il existe deux règles de verrouillage principales pour les verrous d'intention :

• Lorsqu'il est nécessaire d'ajouter un verrou S à une ligne de données, MySQL ajoutera d'abord un verrou IS à la table .
• Lorsqu'il est nécessaire d'ajouter un verrou X à une ligne de données, MySQL ajoutera d'abord un verrou IX à la table.

Dans ce cas, le problème ci-dessus sera facilement résolu. Lorsque vous devez verrouiller une table, il vous suffit de vérifier si la table a un verrou d'intention correspondant. Il n'est pas nécessaire de parcourir le. toute la surface du tableau.

Compatibilité des différents verrous

L'image ci-dessous montre la compatibilité des différents verrous, reportez-vous au site officiel :

	X	IX	S	IS
X	互斥	互斥	互斥	互斥
IX	互斥	共享	冲突	共享
S	互斥	互斥	共享	共享
IS	互斥	共享	共享	共享

X IX S IS X Mutuellement exclusif Mutuellement exclusif Mutuellement exclusif exclusif Mutuellement exclusif IX Mutuellement exclusif Partagé Conflit Partagé S Mutuellement exclusif Mutuellement exclusif Partagé Partagé IS Mutuellement exclusif Partager Partager Partager tableau>

锁到底锁的是什么

建立以下两张表，并初始化5条数据，注意test表有2个索引而test2没有索引：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NAME_INDEX` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test VALUE(1,'张1');
INSERT INTO test VALUE(5,'张5');
INSERT INTO test VALUE(8,'张8');
INSERT INTO test VALUE(10,'张10');
INSERT INTO test VALUE(20,'张20');

CREATE TABLE `test2` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test2 VALUE(1,'张1');
INSERT INTO test2 VALUE(5,'张5');
INSERT INTO test2 VALUE(8,'张8');
INSERT INTO test2 VALUE(10,'张10');
INSERT INTO test2 VALUE(20,'张20');复制代码

举例猜测

在行锁中，假如我们对一行记录加锁，那么到底是把什么东西锁住了，我们来看下面两个例子：
举例1（操作test表）：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=5 FOR UPDATE; 加锁成功
COMMIT; （释放锁）
	SELECT * FROM test WHERE id=1 FOR UPDATE; 加锁成功

举例2（操作test2表）：

事务A	事务B
BEGIN;
SELECT * FROM test2 WHERE id=1 FOR UPDATE;
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test2 WHERE id=5 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 加锁成功

从上面两个例子我们可以发现，test表好像确实是锁住了id=1这一行的记录，而test2表好像不仅仅是锁住了id=1这一行记录，实际上经过尝试我们就知道，test2表是被锁表了，所以其实MySQL中InnoDB锁住的是索引，当没有索引的时候就会锁表。

接下来再看一个场景：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

这个例子中我们是把name索引锁住了，然后我们在事务B中通过主键索引只查id，这样就用到name索引了，但是最后发现也被阻塞了。所以我们又可以得出下面的结论，MySQL索引不但锁住了辅助索引，还会把辅助索引对应的主键索引一起锁住。

到这里，可能有人会有怀疑，那就是我把辅助索引锁住了，但是假如加锁的时候，只用到了覆盖索引，然后我再去查主键会怎么样呢？

接下来让我们再验证一下：

事务A	事务B
BEGIN;
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

Nous pouvons voir que même si seul le verrouillage d'index auxiliaire est utilisé, MySQL verrouillera toujours l'index de clé primaire, et le nœud feuille d'arborescence B+ de l'index de clé primaire stocke toutes les données, donc tout champ interrogé sera verrouillé.

À ce stade, nous pouvons clairement tirer une conclusion sur ce qu'est le verrou :

Conclusion

Dans le moteur InnoDB, ce qui est verrouillé, c'est l'index :

• Si une table n'a pas d'index, MySQL verrouillera la table (en fait l'index de clé primaire de la colonne cachée ROWID est verrouillé)
• Si nous verrouillons l'index auxiliaire, alors l'index auxiliaire index correspond à L'index de clé primaire sera également verrouillé
• L'index de clé primaire est verrouillé, ce qui signifie en fait que l'ensemble de l'enregistrement est verrouillé (le nœud feuille d'index de clé primaire stocke toutes les données)

Algorithme de verrouillage de ligne

Lorsque nous avons introduit les transactions dans l'article précédent, nous avons mentionné que MySQL empêche les lectures fantômes par verrouillage, mais si le verrouillage de ligne ne verrouille qu'une ligne d'enregistrements, il ne semble pas empêcher les lectures fantômes. lit. , donc le verrouillage de ligne d'un enregistrement n'est qu'un des cas. En fait, il existe trois algorithmes de verrouillage de ligne : Record Lock, Gap Lock et Next-Key Lock. La raison pour laquelle il peut empêcher la lecture fantôme est exactement la clé. le verrou le fait.

Verrouillage d'enregistrement

Le verrouillage d'enregistrement est introduit ci-dessus Lorsque notre requête atteint un enregistrement, InnoDB utilisera le verrouillage d'enregistrement pour verrouiller la ligne d'enregistrements atteinte.

Gap Lock

Lorsque notre requête n'atteint pas l'enregistrement, InnoDB ajoutera un verrou d'espacement à ce moment-là.

BEGIN;

Transaction A

Transaction B

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	INSERT INTO test VALUE (2,‘张2’); 阻塞
	INSERT INTO test VALUE (3,‘张3’); 阻塞
	SELECT * FROM test WHERE id=2 FOR UPDATE; 加锁成功
COMMIT; （释放锁）

SELECT * FROM test WHERE id=1 FOR UPDATE;

INSÉRER DANS LA VALEUR de test (2,'Zhang 2');

Blocage

INSÉRER DANS LA VALEUR de test (3,'Zhang 3');Blocage

SELECT * FROM test WHERE id=2 FOR UPDATE;Verrouillage réussi

COMMIT; ( Libérer le verrouillage)

De l'exemple ci-dessus, nous pouvons conclure :

Il n'y a pas de conflit entre les verrous d'espacement et les verrous d'espacement, c'est-à-dire que la transaction A ajoute des verrous d'espacement et que la transaction B peut ajouter des verrous d'espacement dans le même espace.

(La raison pour laquelle le verrouillage des espaces est utilisé est lorsqu'il n'y a pas de données, il n'est donc pas nécessaire de bloquer la lecture, et il n'est pas nécessaire d'empêcher d'autres transactions de verrouiller le même espace)

Verrouillage de l'espace Il bloquera principalement l'opération d'insertion

Comment l'espace est-il déterminé ?

Il y a 5 enregistrements dans la table de test et les valeurs de la clé primaire ​sont : 1, 5, 8, 10, 20. Il y aura alors les six écarts suivants :

(-∞,1),(1,5),(5,8),(8,10),(10,20),(20,+∞)

• Si la clé primaire n'est pas de type int, elle sera convertie en code ASCII puis l'écart sera déterminé.
• Next-Key Lock

Next-Key Lock est une combinaison de verrouillage d'enregistrement et de verrouillage d'espace. Lorsque nous effectuons une requête de plage et que nous atteignons non seulement un ou plusieurs enregistrements, mais incluons également des espaces, le verrouillage temporaire par clé sera utilisé. Le verrouillage sans clé est l'algorithme par défaut pour les verrous de ligne dans InnoDB.

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id>=2 AND id
	INSERT INTO test VALUE (2,‘张2’); 阻塞
	INSERT INTO test VALUE (6,‘张6’); 阻塞
	INSERT INTO test VALUE (8,‘张8’); 阻塞
	SELECT * FROM test WHERE id=8 FOR UPDATE; 阻塞
	INSERT INTO test VALUE (9,‘张9’); 插入成功
COMMIT; （释放锁）

Remarque, ceci concerne uniquement le niveau d'isolation RR. Pour le niveau d'isolation RC, en plus des contraintes de clé étrangère et des contraintes d'unicité, des verrous d'espacement seront ajoutés, sans verrous d'espacement. pas de verrous à clé temporaires, donc les verrous de ligne ajoutés au niveau RC sont tous des verrous d'enregistrement. Si aucun enregistrement n'est atteint, aucun verrou ne sera verrouillé. Par conséquent, le niveau RC ne résout pas le problème de lecture fantôme. Le verrouillage temporaire par clé sera rétrogradé en verrouillage d'espacement ou en verrouillage d'enregistrement dans les deux conditions suivantes : Lorsque la requête manque l'enregistrement de tâche, elle sera rétrogradée en verrouillage de l'espace. Lorsqu'un enregistrement est atteint à l'aide de la clé primaire ou d'un index unique, il sera rétrogradé en verrouillage d'enregistrement. corps>

Transaction A	Transaction B
BEGIN;
SELECT * FROM test WHERE id>=2 AND id
	INSÉRER DANS LA VALEUR de test (2, 'Zhang 2');Blocage
	INSERT INTO test VALUE (6,'Zhang 6');Blocage
	INSERT INTO test VALEUR (8,'Zhang 8');Blocage
	SELECT * FROM test WHERE id=8 FOR MISE À JOUR ;Blocage
	INSÉRER DANS LA VALEUR du test (9,'Zhang 9');Insérer Succès
COMMIT;(libérer le verrouillage)

上面这个例子，事务A加的锁跨越了(1,5)和(5,8)两个间隙，且同时命中了5，然后我们发现我们对id=8这条数据进行操作也阻塞了，但是9这条记录插入成功了。

临键锁加锁规则

临键锁的划分是按照左开右闭的区间来划分的，也就是我们可以把test表中的记录划分出如下区间：(-∞,1],(1,5],(5,8],(8,10],(10,20],(20,+∞)。

那么临键锁到底锁住了哪些范围呢？

**临键锁中锁住的是最后一个命中记录的 key 和其下一个左开右闭的区间**

那么上面的例子中其实锁住了(1,5]和(5,8]这两个区间。

临键锁为何能解决幻读问题

临键锁为什么要锁住命中记录的下一个左开右闭的区间？答案就是为了解决幻读。

我们想一想上面的查询范围id>=2且id

当然，其实如果我们执行的查询刚好是id>=2且id

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务持有锁的时候，会阻止其他的事务获取锁，这个时候会造成阻塞等待，那么假如事务一直等待下去，就会一直占用CPU资源，所以，锁等待会有一个超时时间，在InnoDB引擎中，可以通过参数：innodb_lock_wait_timeout查询：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';复制代码

默认超时时间是50s，超时后会自动释放锁回滚事务。但是我们想一下，假如事务A在等待事务B释放锁，而事务B又在等待事务A释放锁，这时候就会产生一个等待环路了，而这种情况是无论等待多久都不可能会获取锁成功的，所以是没有必要去等50s的，这种形成等待环路的现象又叫做死锁。

死锁(Dead Lock)

什么是死锁

死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=10 FOR UPDATE;
	BEGIN;
	SELECT * FROM test WHERE id=20 FOR UPDATE;
SELECT * FROM test WHERE id=20 FOR UPDATE;
	SELECT * FROM test WHERE id=10 FOR UPDATE;
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查询出结果

Nous pouvons voir qu'après un blocage, il sera immédiatement annulé, au lieu d'attendre sans but 50 secondes avant l'expiration du délai, puis d'annuler la transaction. Alors, comment MySQL sait-il qu'il y a un blocage. s'est produit ? Oui, comment détecter l'apparition d'un blocage ?

Détection des blocages

Actuellement, la plupart des bases de données utilisent la méthode du graphique d'attente (wait graph) pour détecter les blocages. Le moteur InnoDB utilise également cette méthode pour détecter les blocages. Deux types d'informations seront enregistrées dans la base de données :

• Liste des informations de verrouillage
• Liste d'attente des transactions
  L'algorithme du graphique d'attente construira un graphique basé sur ces deux éléments d'information. Lorsqu'il y a une boucle dans la figure, cela prouve qu'il y a une impasse :
  Dans la figure ci-dessous, il y a une boucle entre t1 et t2, ce qui prouve qu'il y a une impasse entre les transactions de t1 et t2. t2
  

Éviter les blocages

• Diviser autant que possible les transactions longues en plusieurs petites transactions
• Éviter les requêtes sans instructions conditionnelles Where lors de l'interrogation et utilisez autant que possible des requêtes d'index
• Si possible, essayez d'utiliser une requête équivalente

Verrouiller la requête d'informations

InnoDB fournit 3 tables sous le schéma d'information bibliothèque pour que nous puissions interroger et résoudre les problèmes liés aux transactions et aux verrous.

INNODB_TRX

enregistre des informations sur chaque transaction actuellement exécutée dans InnoDB, y compris si la transaction attend un verrou, quand la transaction a démarré et l'instruction SQL que la transaction exécute (le cas échéant) ).

列名	含义
trx_id	InnoDD引擎中的事务的唯一ID
trx_state	事务状态:RUNNING, LOCK WAIT, ROLLING BACK,COMMITTING
trx_started	事务的开始时间
trx_requested_lock_id	等待会务的锁ID,如果trx_state不为LOCK WAIT时，为null
trx_wait_started	事务等待开始的时间
trx_weight	事务的权重，反映了一个事务修改和锁住的行数，当发生死锁时候，InnoDB会选择该值最小的事务进行回滚
trx_mysql_thread_id	MySQL中的线程ID，可以通过SHOW PROCESSLIST查询
trx_query	事务运行的sql语句
trx_operation_state	事务的当前操作状态，如果没有则为NULL
trx_tables_in_use	当前事务中执行的sql语句用到的表数量
trx_tables_locked	已经被锁定表的数量(因为用的是行锁，所以虽然显示一张表被锁了，但是可能只是锁定的其中一行或几行，所以其他行还是可以被其他事务访问)
trx_lock_structs	当前事务保留的锁数量
trx_lock_memory_bytes	当前事务的索结构在内存中的大小
trx_rows_locked	当前事务中锁住的大致行数，包括已经被打上删除标记等物理存在的但是对当前事务不可见的数据
trx_rows_modified	当前事务修改或者插入的行数
trx_concurrency_tickets	并发数，指的是当前事务未结束前仍然可以执行的并发数,可以通过系统变量innodb_concurrency_tickets设置
trx_isolation_level	当前事务隔离级别
trx_unique_checks	是否为当前事务打开或者关闭唯一约束:0-否1-是
trx_foreign_key_checks	是否为当前事务打开或者关闭外键约束:0-否1-是
trx_last_foreign_key_error	最后一个外键错误信息，没有则为空
trx_adaptive_hash_latched	自适应哈希索引是否被当前事务锁定。在分区自适应哈希索引搜索系统时，单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由innodb_adaptive_hash_index_parts控制，默认设置为8。
trx_adaptive_hash_timeout	是立即放弃自适应哈希索引的搜索latch，还是在来自MySQL的调用中保留它。当没有自适应哈希索引争用时，这个值将保持为零，并且语句会保留latch直到它们完成。在争用期间，它的计数减少到零，并且语句在每一行查找之后立即释放锁存。当自适应哈希索引搜索系统被分区时(由innodb_adaptive_hash_index_parts控制)，该值保持为0。
trx_is_read_only	当前事务是否只读:0-否1-是
trx_autocommit_non_locking	值为1表示这是一条不包含for update和lock in share model的语句，而且是在开启autocommit情况下执行的有且仅有这一条语句，当这列和TRX_IS_READ_ONLY都为1时，InnoDB会优化事务以减少与更改表数据事务的相关开销。

INNODB_LOCKS

Enregistre des informations pour chaque verrou demandé par une transaction mais n'a pas obtenu et pour chaque verrou qu'une transaction détenait mais bloquait une autre transaction.

列名	含义
lock_id	锁的id(虽然LOCK_ID当前包含TRX_ID，但LOCK_ID中的数据格式随时可能更改，不要编写解析LOCK_ID值的应用程序)
lock_trx_id	上一张表的事务ID
lock_mode	锁的模式: S, X, IS, IX, GAP, AUTO_INC,UNKNOWN
lock_type	锁的类型是表锁还是行锁
lock_table	被锁住的表
lock_index	被锁住的索引，表锁则为NULL
lock_space	锁记录的空间id，表锁则为NULL
lock_page	事务锁定页的数量，表锁则为NULL
lock_rec	事务锁定行的数量，表锁则为NULL
lock_data	事务锁定的主键值，表锁则为NULL

INNODB_LOCK_WAITS

enregistre les informations d'attente de verrouillage. Chaque transaction InnoDB bloquée contient une ou plusieurs lignes représentant le verrou demandé et tous les verrous qui bloquaient la demande.

列名	含义
lock_id	锁的id(虽然LOCK_ID当前包含TRX_ID，但LOCK_ID中的数据格式随时可能更改，不要编写解析LOCK_ID值的应用程序)
requesting_trx_id	申请锁资源的事务ID
requested_lock_id	申请的锁的ID
blocking_trx_id	阻塞的事务ID
blocking_lock_id	阻塞的锁的ID

Plus de recommandations d'apprentissage gratuites associées : tutoriel MySQL(vidéo)

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!