| Non |
Oui |
|
4. Classification de compatibilité des verrous
Dans MySQL, la lecture des données est principalement divisée en lecture actuelle et lecture d'instantané :
-
Lecture d'instantané
Lecture d'instantané, ce qui est lu est données d'instantané, sans ajouter de sélections ordinaires de les verrous sont des lectures d'instantanés. SELECT * FROM table WHERE ...
-
Lecture actuelle
La lecture actuelle signifie la lecture des dernières données, et non des données historiques Locked SELECT, ou l'ajout, la suppression et la modification de données effectueront tous la lecture actuelle. SELECT * FROM table LOCK IN SHARE MODE;
SELECT FROM table FOR UPDATE;
INSERT INTO table values ...
DELETE FROM table WHERE ...
UPDATE table SET ...
Dans la plupart des cas, nous exploitons la base de données sur la base des lectures actuelles, et dans des scénarios simultanés, nous devons non seulement permettre aux situations lecture-lecture de ne pas être affectées, mais aussi faire en sorte que écrire-écrire, lire -Les opérations d'écriture ou de lecture se bloquent, vous devez utiliser des verrous partagés et des verrous exclusifs dans MySQL.
4.1 Verrous partagés et verrous exclusifs
Les verrous partagés (verrous partagés) peuvent également être appelés verrous en lecture, en abrégé verrous S. Les données peuvent être lues simultanément, mais aucune transaction ne peut modifier les données. Les
Exclusive Locks (Exclusive Locks), peuvent également être appelés verrous exclusifs ou verrous en écriture, appelés X locks. Si quelque chose ajoute un verrou exclusif à une ligne, seule cette transaction peut la lire et l'écrire. Avant la fin de cette transaction, les autres transactions ne peuvent pas y ajouter de verrous. Les autres processus peuvent lire mais ne peuvent pas effectuer d'opérations d'écriture. libérer.
Analysons la situation d'acquisition des verrous : s'il y a la transaction A et la transaction B
La transaction A acquiert le verrou S d'un enregistrement, et à ce moment la transaction B veut également acquérir le verrou S de l'enregistrement, alors La transaction B peut également Le verrou est acquis, ce qui signifie que la transaction A et la transaction B détiennent le verrou S de l'enregistrement en même temps. Si la transaction B souhaite acquérir le verrou X de l'enregistrement, cette opération sera bloquée jusqu'à ce que le verrou S soit libéré après la validation de la transaction A. Si la transaction A acquiert d'abord le verrou X, que la transaction B souhaite acquérir le verrou S ou le verrou X de l'enregistrement, elle sera bloquée jusqu'à ce que la transaction A soit validée.
Par conséquent, nous pouvons dire que le verrouillage S et le verrouillage S sont compatibles, le verrouillage S et le verrouillage X sont incompatibles, et le verrouillage X et le verrouillage X sont également incompatibles.
4.2 Intention Lock
Intention Shared Lock (Intention Shared Lock), appelé IS Lock. Lorsqu'une transaction doit ajouter un verrou S à un enregistrement, elle doit d'abord ajouter un verrou IS au niveau de la table.
Intention Exclusive Lock (Intention Exclusive Lock), appelé IX Lock. Lorsqu'une transaction doit ajouter un verrou X à un enregistrement, elle doit d'abord ajouter un verrou IX au niveau de la table.
Les verrous d'intention sont des verrous au niveau de la table Ils sont proposés uniquement pour juger rapidementsi les enregistrements de la table sont verrouillés lors de l'ajout de verrous S au niveau de la table et s'il n'y a aucun enregistrement verrouillé dans la table. C'est-à-dire que le verrouillage IS est compatible avec le verrouillage IS et le verrouillage IX est compatible avec le verrouillage IX.
Pourquoi avez-vous besoin d'un verrouillage d'intention ?
Le verrou d'intention d'InnoDB est principalement utilisé lorsque plusieurs verrous granulaires coexistent. Par exemple, la transaction A souhaite ajouter un verrou S à une table. Si une ligne de la table a été ajoutée à un verrou X par la transaction B, l'application du verrou doit également être bloquée. S'il y a beaucoup de données dans la table, la surcharge liée à la vérification de l'indicateur de verrouillage ligne par ligne sera très importante et les performances du système en seront affectées. Par exemple, s'il y a 100 millions d'enregistrements dans la table et que la transaction A verrouille les lignes sur plusieurs enregistrements, la transaction B doit ajouter des verrous au niveau de la table à la table. S'il n'y a pas de verrouillage intentionnel, recherchez si. ces 100 millions d'enregistrements sont verrouillés dans la table. S'il y a un verrou d'intention, alors si la transaction A ajoute un verrou d'intention puis un verrou X avant de mettre à jour un enregistrement, la transaction B vérifie d'abord s'il y a un verrou d'intention sur la table et si le verrou d'intention existant entre en conflit avec le verrou qu'elle prévoit à ajouter En cas de conflit, attendez que la transaction A soit libérée sans vérifier chaque enregistrement. La transaction B n'a pas besoin de savoir exactement quelle ligne est verrouillée lors de la mise à jour de la table, elle a seulement besoin de savoir qu'au moins une ligne a été verrouillée.
Pour parler franchement, la fonction principale des verrous d'intention est de gérer la contradiction entre les verrous de ligne et les verrous de table. Ils peuvent montrer qu'une transaction détient un verrou sur une certaine ligne, ou se prépare à détenir un verrou. . Compatibilité des différents verrous au niveau table :
| S
IS |
| X
IX |
|
S|
Compatible |
Compatible | Non compatible | Non compatible | Non compatible | Non Compatible
|
IS |
| Compatible | Compatible | Non compatible | Non compatible
4.3 Verrous pour les opérations de lecture
Pour les opérations de lecture MySQL, il existe deux façons de verrouiller.
1️⃣ SELECT * FROM table LOCK IN SHARE MODE
Si la transaction en cours exécute cette instruction, elle ajoutera des verrous S aux enregistrements qu'elle lit, permettant ainsi à d'autres transactions de continuer à acquérir des verrous S pour ces enregistrements (pour Par exemple, d'autres transactions utilisent également l'instruction SELECT ... LOCK IN SHARE MODE pour lire ces enregistrements), mais ne peuvent pas obtenir les verrous X de ces enregistrements (par exemple, utilisez l'instruction SELECT .. . FOR UPDATE pour lire ces enregistrements, ou modifier ces enregistrements directement). SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），但是不能获取这些记录的 X 锁（比方说使用 SELECT ... FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。
如果别的事务想要获取这些记录的 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉
2️⃣ SELECT FROM table FOR UPDATE
如果当前事务执行了该语句，那么它会为读取到的记录加 X 锁，这样既不允许别的事务获取这些记录的 S 锁（比方说别的事务使用 SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的 X 锁（比如说使用 SELECT ... FOR UPDATESi d'autres transactions souhaitent acquérir les verrous X de ces enregistrements, elles se bloqueront jusqu'à ce que les verrous S sur ces enregistrements soient libérés une fois la transaction en cours validée
2️⃣ SELECT FROM table FOR UPDATE
Si la transaction en cours s'exécute Si cette instruction est saisie, elle ajoutera des verrous X aux enregistrements lus. Cela ne permettra pas à d'autres transactions d'obtenir les verrous S de ces enregistrements (par exemple, d'autres transactions utilisent SELECT... LOCK IN SHARE MODE pour lire ces enregistrements), et il n'est pas non plus permis d'obtenir le verrou X de ces enregistrements (par exemple, utiliser l'instruction <code>SELECT ... FOR UPDATE pour lire ces enregistrements, ou modifier directement ces enregistrements).
Si d'autres transactions souhaitent acquérir le verrou S ou le verrou X de ces enregistrements, elles se bloqueront jusqu'à ce que le verrou X sur ces enregistrements soit libéré après la validation de la transaction en cours. 4.4 Verrous pour les opérations d'écriture
DELETE, UPDATE et INSERT sont des opérations d'écriture courantes dans MySQL. Verrouillage implicite, verrouillage et déverrouillage automatiques.
1️⃣ DELETE
Le processus d'exécution d'une opération DELETE sur un enregistrement consiste d'abord à localiser l'enregistrement dans l'arborescence B+, puis à obtenir le verrou X de l'enregistrement, puis à effectuer l'opération de suppression de marque. Nous pouvons également comprendre ce processus comme l'utilisation de la méthode de lecture verrouillée pour acquérir le verrou X pour localiser la position de l'enregistrement à supprimer dans l'arborescence B+.
2️⃣ INSERT
Normalement, l'opération d'insertion d'un nouvel enregistrement n'est pas verrouillée. InnoDB utilise un type de verrou implicite pour protéger cet enregistrement nouvellement inséré contre l'utilisation par d'autres avant que la transaction ne soit validée.
3️⃣ UPDATE
Il existe trois situations lors de l'exécution d'une opération UPDATE sur un enregistrement :
① Si la valeur clé de l'enregistrement n'a pas été modifiée et que l'espace de stockage occupé par la colonne mise à jour n'a pas changé avant et après la modification, puis localisez d'abord l'emplacement de cet enregistrement dans l'arborescence B+, puis obtenez le verrou X de l'enregistrement, et enfin effectuez les opérations de modification à l'emplacement de l'enregistrement d'origine. On peut également considérer le processus d'enregistrement de l'emplacement à modifier dans l'arborescence B+ comme une opération de lecture de verrou pour acquérir le verrou X. ② Si la valeur clé de l'enregistrement n'a pas été modifiée et que l'espace de stockage occupé par au moins une colonne mise à jour a changé avant et après la modification, localisez d'abord la position de l'enregistrement dans l'arborescence B+, puis obtenez le X verrouillez l'enregistrement, supprimez complètement l'enregistrement (c'est-à-dire déplacez complètement l'enregistrement vers la liste des déchets) et enfin insérez un nouvel enregistrement. Ce processus de localisation de la position de l'enregistrement à modifier dans l'arborescence B+ est considéré comme une lecture verrouillée pour obtenir le verrou X. L'enregistrement nouvellement inséré est protégé par le verrou implicite fourni par l'opération INSERT.
③ Si la valeur clé de l'enregistrement est modifiée, cela équivaut à effectuer une opération DELETE sur l'enregistrement d'origine puis à effectuer une opération INSERT. L'opération de verrouillage doit être effectuée selon les règles de DELETE et INSERT.
PS : Pourquoi d'autres transactions peuvent-elles toujours être lues lorsque le verrou en écriture est verrouillé ? Étant donné qu'InnoDB dispose d'un mécanisme MVCC (Multi-version Concurrency Control), les lectures d'instantanés peuvent être utilisées sans être bloquées. 4. Classification de la granularité du verrouillage
Qu'est-ce que la granularité du verrouillage ? La granularité du verrouillage fait référence à la portée de ce que vous souhaitez verrouiller. Par exemple, si vous allez aux toilettes à la maison, il vous suffit de verrouiller la salle de bain. Il n'est pas nécessaire de verrouiller toute la maison pour empêcher les membres de la famille d'entrer. Qu'est-ce qu'une granularité de verrouillage raisonnable ? En effet, la salle de bain ne sert pas seulement à aller aux toilettes, mais aussi à prendre une douche et à se laver les mains. Cela pose la question de l’optimisation de la granularité du verrouillage. Lorsque vous prenez une douche dans la salle de bain, d'autres peuvent effectivement entrer et se laver les mains en même temps, à condition qu'ils soient isolés si les toilettes, la baignoire et le lavabo sont tous séparés et relativement indépendants (humides et secs). sont séparés), en fait La salle de bain peut être utilisée par trois personnes en même temps, mais bien sûr les trois personnes ne peuvent pas faire la même chose. Cela affine la granularité de la serrure. Il vous suffit de fermer la porte de la salle de bain lorsque vous prenez une douche, et les autres peuvent toujours entrer et se laver les mains. Si les différentes zones fonctionnelles ne sont pas séparées lors de la conception d’une salle de bains, les ressources de la salle de bains ne peuvent pas être maximisées. De même, il existe également une granularité de verrouillage dans MySQL. Habituellement divisé en trois types, verrous de ligne, verrous de table et verrous de page . 4.1 Verrouillage de ligne Dans l'introduction des verrous partagés et des verrous exclusifs, ils sont en fait enregistrés pour une certaine ligne, ils peuvent donc également être appelés verrous de ligne. Le verrouillage d'un enregistrement n'affecte que cet enregistrement, donc la granularité du verrouillage des verrous de ligne est la meilleure de MySQL. 🎜Le verrouillage par défaut du moteur de stockage InnoDB est le verrouillage des lignes🎜. 🎜🎜Il présente les caractéristiques suivantes :🎜
-
La plus faible probabilité de conflit de verrouillage et une concurrence élevée
En raison de la faible granularité des verrous de ligne, un conflit de ressources de verrouillage se produit La probabilité est également la plus faible, donc la probabilité de conflit de verrouillage est faible et la concurrence est plus élevée.
-
C'est cher et lent d'ajouter des verrous
Les verrous consomment beaucoup de performances. plusieurs éléments de données dans la base de données occuperont inévitablement beaucoup de ressources, et pour le verrouillage, vous devez attendre que le verrou précédent soit libéré avant de verrouiller.
-
produira une impasse
Pour ce qu'est une impasse, vous pouvez lire ci-dessous.
4.2 Verrouillage de la table
Le verrouillage au niveau de la table est un verrouillage au niveau de la table, qui verrouillera la table entière#🎜 🎜#, Il peut très bien éviter les blocages et constitue également le plus grand mécanisme de verrouillage granulaire de MySQL.
Le verrou par défaut du moteur de stockage MyISAM est le verrou de table . Il présente les caractéristiques suivantes :
-
Petit aérien et verrouillage rapide#🎜🎜 #Puisque la table entière est verrouillée, la vitesse doit être plus rapide que le verrouillage d'une seule donnée.
- Il n'y aura pas d'impasse
La table entière est verrouillée et les autres transactions ne sont pas affectées à tout Sans verrou, il n'y aura naturellement pas d'impasse.
- La granularité du verrouillage est grande, la probabilité de conflit de verrouillage est élevée et la concurrence est faible
#🎜🎜 ## 🎜🎜#4.3 Verrouillage de page
Le verrouillage au niveau de la page est un niveau de verrouillage unique dans MySQL et n'est pas courant dans d'autres logiciels de gestion de bases de données.
La granularité des verrous au niveau de la page se situe entre les verrous au niveau de la ligne et les verrous au niveau de la table, de sorte que la surcharge de ressources requise pour obtenir les verrous et les capacités de traitement simultané qu'ils peuvent fournir se situent également entre les deux ci-dessus. entre. De plus, les verrous au niveau de la page, comme les verrous au niveau des lignes, peuvent provoquer des blocages.
row lock
table lock | #🎜 🎜#pagelock |
|
| verrouiller la granularité small
big# 🎜 🎜 #| entre les deux |
|
efficacité du verrouillage |
lent |
rapide#🎜 🎜##🎜 🎜#entre les deux
|
probabilité de conflit |
faible |
élevée | -
|
Performances de concurrence |
Élevée |
Faible |
Moyenne # 🎜🎜# | Surcharge de performances | big | small | between#🎜 🎜# # 🎜 🎜#C'est une impasse 🎜# |
5. 算法实现分类
对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。
InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。
不过即使是行锁，InnoDB 里也是分成了各种类型的。换言之，即使对同一条记录加上行锁，不同的锁类型也会产生不同的效果。通常有以下几种常用的行锁类型。
5.1 Record Lock
记录锁，单条索引记录上加锁。
Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。
5.2 Gap Locks
间隙锁，对索引前后的间隙上锁，不对索引本身上锁。
MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。
如存在这样一张表： CREATE TABLE test (
id INT (1) NOT NULL AUTO_INCREMENT,
number INT (1) NOT NULL COMMENT '数字',
PRIMARY KEY (id),
KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
# 插入以下数据
INSERT INTO test VALUES (1, 1);
INSERT INTO test VALUES (5, 3);
INSERT INTO test VALUES (7, 8);
INSERT INTO test VALUES (11, 12);如下：
开启一个事务 A： BEGIN;
SELECT * FROM test WHERE number = 3 FOR UPDATE; 此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。
如果此时在开启一个事务 B 进行插入数据，如下： BEGIN;
# 阻塞
INSERT INTO test (id, number) VALUES (2,2); 结果如下：
为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如： INSERT INTO test (id, number) VALUES (8,8); 5.3 Next-Key Locks
next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。
InnoDB 默认使用 REPEATABLE READ 隔离级别。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。
6. 乐观锁和悲观锁
乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。
6.1 乐观锁
所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。
实现乐观锁常见的方式
常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。
在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。
6.2 悲观锁
所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。
悲观锁的实现方式有两种
共享锁（读锁）和排它锁（写锁），参考上面。
7. 死锁
是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。
产生的条件
互斥条件：一个资源每次只能被一个进程使用； 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放； 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺； 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。
MySQL 中其实也是一样的，如下还是这样一张表： CREATE TABLE `user` (
`id` bigint NOT NULL COMMENT '主键',
`name` varchar(20) DEFAULT NULL COMMENT '姓名',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`age` varchar(10) DEFAULT NULL COMMENT '年龄',
`url` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net'); 按照如下顺序执行：
|
A |
B |
| ① |
BEGIN |
|
| ② |
|
BEGIN |
| ③ |
SELECT * FROM user WHERE name='a' FOR UPDATE |
|
| ④ |
|
SELECT * FROM user WHERE name='b' FOR UPDATE |
| ⑤ |
SELECT * FROM user WHERE name='b' FOR UPDATE |
|
| ⑥ |
|
SELECT * FROM user WHERE name='a' FOR UPDATE |
1、开启 A、B 两个事务；
2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；
3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；
4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。
此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。
如何避免
从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。
|
|
2. Verrous et classifications dans MySQL
