Que sont les verrous et classifications MySQL ?

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

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 < /code> 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.

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.

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

Qu'est-ce que la granularité du verrouillage

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.

verrous de ligne, verrous de table et verrous de page

4.1 Verrouillage de ligne

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

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

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');

按照如下顺序执行：

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 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。