Explication détaillée des verrous MySQL

Lock, dans la vraie vie, est un outil utilisé lorsque l'on veut se cacher du monde extérieur. Dans les ordinateurs, il s'agit d'un mécanisme permettant de coordonner l'accès simultané à une ressource par plusieurs processus ou comtés. Dans une base de données, outre la concurrence pour les ressources informatiques traditionnelles (CPU, RAM, E/S, etc.), les données sont également une ressource partagée et accessible par de nombreux utilisateurs. Comment garantir la cohérence et l'efficacité de l'accès simultané aux données est un problème que toutes les bases de données doivent résoudre. Les conflits de verrouillage sont également un facteur important affectant les performances de l'accès simultané aux bases de données. De ce point de vue, les verrous sont particulièrement importants pour les bases de données.

1. Verrous dans MySQL

MySQL a les concepts de Lock et Latch Dans la base de données, les deux peuvent être appelés ". lock", mais les deux ont des significations complètement différentes.

Le loquet est généralement appelé loquet (serrure légère) car le temps de verrouillage nécessaire doit être très court. Si cela dure longtemps, les performances de l'application seront très mauvaises. Dans le moteur InnoDB, Latch peut être divisé en mutex (mutex) et rwlock (verrouillage en lecture-écriture). Son objectif est de garantir l'exactitude des threads simultanés exploitant des ressources critiques, et il n'existe généralement pas de mécanisme de détection de blocage.

L'objet de Lock est une transaction et il est utilisé pour verrouiller des objets dans la base de données, tels que des tables, des pages et des lignes. Et généralement, les objets de verrouillage ne sont libérés qu'après la validation ou l'annulation de la transaction (le délai de libération peut être différent selon les niveaux d'isolation des transactions).

Pour une explication plus détaillée de Latch, veuillez vous référer à : Concernant l'analyse approfondie et le jugement des conflits de latch MySQL, cet article se concentre principalement sur les verrous Lock.

Types de verrous

Il n'y a en fait que deux opérations sur les données, à savoir la lecture et l'écriture, et la base de données le fera également lors de l'implémentation des verrous. deux opérations utilisent des verrous différents ; InnoDB implémente des verrous standard au niveau des lignes, à savoir Shared Lock et Exclusive Lock.

• Verrou partagé (read lock), permettant à une transaction de lire une ligne de données.

• Verrouillage exclusif (verrouillage en écriture), permettant à une transaction de supprimer ou de mettre à jour une ligne de données.

Et leurs noms impliquent également une autre caractéristique de chacun. Les verrous partagés sont compatibles entre eux, tandis que les verrous mutex sont incompatibles avec tous les autres verrous :

Si vous réfléchissez un peu à leur utilisation, vous pouvez comprendre pourquoi ils sont conçus de cette façon. Parce que les verrous partagés représentent les opérations de lecture et les verrous mutex représentent les opérations d'écriture, nous pouvons les paralléliser dans la base de données. ne peut écrire qu'en série. Ce n'est qu'ainsi que nous pouvons garantir qu'il n'y aura pas de concurrence entre les threads et que la sécurité des threads pourra être assurée.

Granularité du verrouillage

Les verrous de verrouillage sont principalement divisés en verrous de table, verrous de page et verrous de ligne en fonction de la granularité. Différents moteurs de stockage ont des granularités de verrouillage différentes.

Verrouillage de table

Le verrouillage au niveau de la table est le mécanisme de verrouillage le plus granulaire parmi les moteurs de stockage MySQL. La plus grande caractéristique de ce mécanisme de verrouillage est que la logique de mise en œuvre est très simple et n’a qu’un impact négatif minimal sur le système. L’acquisition et la libération des verrous sont donc très rapides. Étant donné que les verrous au niveau de la table verrouillent la table entière en même temps, le problème de blocage qui nous tourmente peut être évité.

Bien sûr, le plus grand impact négatif d'une grande granularité de verrouillage est que la probabilité de conflit pour le verrouillage des ressources sera la plus élevée, ce qui réduira considérablement la concurrence.

Le verrouillage au niveau des tables est principalement utilisé par les moteurs de stockage non transactionnels tels que MyISAM, MEMORY et CSV.

La syntaxe du verrouillage de table est très simple :

# 获取表锁
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
 
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
 
# 释放表锁
UNLOCK TABLES

MyISAM effectuera automatiquement les opérations de verrouillage et de déverrouillage de la table avant d'exécuter la requête. Généralement, l'utilisateur n'a pas besoin d'ajouter et de déverrouiller manuellement la table. , mais il y en a. Il doit également être affiché lorsqu'il est verrouillé. Par exemple : récupérer le nombre de données dans la table à un certain instant t1 et t2.

LOCK TABLE t1 read, t2 read;
select count(t1.id1) as 'sum' from t1;
select count(t2.id1) as 'sum' from t2;
UNLOCK TABLES;

Verrouillage de page

Le verrouillage au niveau de la page est un niveau de verrouillage unique dans MySQL et n'est pas trop courant dans d'autres logiciels de gestion de bases de données. La caractéristique du verrouillage au niveau de la page est que la granularité du verrouillage se situe entre le verrouillage au niveau de la ligne et le verrouillage au niveau de la table, de sorte que la surcharge de ressources requise pour obtenir le verrou et la capacité de traitement simultané qu'il peut fournir se situent également entre les deux ci-dessus. De plus, le verrouillage au niveau de la page et au niveau de la ligne entraînera un blocage.

Dans le processus de verrouillage des ressources dans la base de données, à mesure que la granularité des ressources verrouillées diminue, de plus en plus de mémoire est consommée pour verrouiller la même quantité de données, et l'algorithme de mise en œuvre deviendra également de plus en plus complexe. . Plus c’est complexe. Cependant, à mesure que la granularité des ressources verrouillées diminue, la possibilité que les demandes d'accès aux applications rencontrent des attentes de verrouillage diminuera également, et la concurrence globale du système augmentera également.

La principale utilisation du verrouillage au niveau de la page est le moteur de stockage BerkeleyDB.

Verrouillage de ligne

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

总结

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

2、InnoDB中的锁

意向锁

上节提到InnoDB 支持多种粒度的锁，也就是行锁和表锁。为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock）。

那什么是意向锁呢？我们在这里可以举一个例子：如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

与上一节中提到的两种锁的种类相似的是，意向锁也分为两种：

• 意向共享锁（IS）：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁。

• 意向互斥锁（IX）：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

随着意向锁的加入，锁类型之间的兼容矩阵也变得愈加复杂：

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

行锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁。

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。例如有一个索引有10，11，13和20这4个值，那么该索引可能被Next-Key Locking的区间为：

除了Next-Key Locking，还有Previous-Key Locking技术。同样上述的值，使用Previous-Key Locking技术，那么可锁定的区间为：

但是不是所有索引都会加上Next-key Lock的，在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock。

接下来，我们来通过一个例子解释一下。

CREATE TABLE z (
    a INT,
    b INT,
    PRIMARY KEY(a),    // a是主键索引
    KEY(b)    // b是普通索引
);
INSERT INTO z select 1, 1;
INSERT INTO z select 3, 1;
INSERT INTO z select 5, 3;
INSERT INTO z select 7, 6;
INSERT INTO z select 10, 8;

这时候在会话A中执行 SELECT * FROM z WHERE b = 3 FOR UPDATE ，索引锁定如下：

这时候会话B执行的语句落在锁定范围内的都会进行waiting

SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4, 2;
INSERT INTO z SELECT 6, 5;

用户可以通过以下两种方式来显示的关闭Gap Lock：

• 将事务的隔离级别设为 READ COMMITED。

• 将参数innodb_locks_unsafe_for_binlog设置为1。

从上面的例子可以看出来，Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，设计它的目的是用来解决Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB就是使用它来解决幻读问题。

幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL可能会返回之前不存在的行，也就是第一次执行和第二次执行期间有其他事务往里插入了新的行。

一致性非锁定读

一致性非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过多版本控制（MVCC）的方式来读取当前执行时间数据库中行的数据。如果读取的这行正在执行DELETE或UPDATE操作，这时读取操作不会向XS锁一样去等待锁释放，而是会去读一个快照数据。MVCC相关的知识我已经在另外一篇文章中阐述了，这里就不做过多原理的分析了。地址：谈谈MySQL InnoDB存储引擎事务的ACID特性

在事务隔离级别RC和RR下，InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义却不同，在RC级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在RR级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

下面我们通过一个例子来看看大家是否对MVCC理解了。

可以看到，第1步和第2步是非常容易理解的，而在第3步事务B插入一条新的数据后，在第4步事务A还是查不到，也就是利用了MVCC的特性来实现。当事务B提交后，第5步的查询在RC和RR隔离级别下的输出是不同的，这个的原因在另一篇博客中也说到了，是因为他们创建ReadView的时机不同。

但是很诡异的是在第6步的时候，事务A更新了一条它看不见的记录，然后查询就能够查询出来了。这里很多人容易迷惑，不可见不代表记录不存在，它只是利用了可见性判断忽略了而已。更新成功之后，事务A顺其自然的记录了这条记录的Undo log，在随后的查询中，因为它能够看见自己的改动这一个可见性的判断，自然就能够查询出来了。这里很多名词需要去深入读一下此文：谈谈MySQL InnoDB存储引擎事务的ACID特性

一致性锁定读

前面说到，在默认隔离级别RR下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作。

• SELECT … FOR UPDATE （X锁）

• SELECT … LOCK IN SHARE MODE （S锁）

3、锁带来的问题

通过锁定机制可以实现事务隔离性要求，使得事务可以并发的工作。锁提高了并发，但是却会带来潜在的问题。不过好在有事务隔离性的要求，不同的隔离级别解决的锁的问题也不同，这里只进行简单的介绍，不进行举例分析了。

InnoDB存储引擎在RR级别就已经解决了所有问题，但是它和Serializable的区别在哪里呢？区别就在于RR级别还存在一个丢失更新问题，而SERIALIZABLE无论对于查询还是更新都会进行锁定操作。

Comme le montre la figure, le montant initial de l'utilisateur est de 100. Si le jugement de transfert et de dépôt dans le programme consiste à interroger d'abord puis à mettre à jour, il y aura un problème de mises à jour perdues, c'est-à-dire que les mises à jour ultérieures seront écraser les mises à jour précédentes. Si vous souhaitez éviter ce problème, vous pouvez uniquement baser le montant sur la dernière valeur du tableau à chaque fois que vous le mettez à jour. Si vous devez d'abord interroger puis mettre à jour, vous pouvez déterminer le montant dans les conditions de mise à jour (verrouillage optimiste) ou vous pouvez utiliser SERIALIZABLE avec le niveau d'isolement le plus élevé.

4. Deadlock

L'impasse signifie que deux transactions ou plus sont en concurrence pour les ressources de verrouillage pendant le processus d'exécution. Voici un problème de blocage rencontré dans un projet précédent et une analyse approfondie : analyse du problème de blocage MySQL causé par un problème en ligne. Je n'entrerai pas dans les détails ici.

Tutoriel recommandé : "Tutoriel 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!