La colonne
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.
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.
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; --解锁复制代码
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 .
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, é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.
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 :
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.
L'image ci-dessous montre la compatibilité des différents verrous, reportez-vous au site officiel :
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 互斥 |
互斥 |
互斥 |
互斥 |
| IX | 互斥 |
共享 |
冲突 |
共享 |
| S | 互斥 |
互斥 |
共享 |
共享 |
| IS | 互斥 |
共享 |
共享 |
共享 |
建立以下两张表，并初始化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 :
Dans le moteur InnoDB, ce qui est verrouillé, c'est l'index :
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.
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.
Lorsque notre requête n'atteint pas l'enregistrement, InnoDB ajoutera un verrou d'espacement à ce moment-là.
| Transaction A | Transaction B | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
||||||||||||||
| SELECT * FROM test WHERE id=1 FOR UPDATE; | ||||||||||||||
INSÉRER DANS LA VALEUR de test (2,'Zhang 2');
|
||||||||||||||
| 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) |
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,+∞)| 事务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; （释放锁） |
| 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的，这种形成等待环路的现象又叫做死锁。
死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。
| 事务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 ?
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 :
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.
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会优化事务以减少与更改表数据事务的相关开销。 |
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 |
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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
