Compréhension approfondie actuelle des types de verrous MySQL et des principes de verrouillage

Recommandations d'apprentissage gratuites associées : Tutoriel MySQL

Avant-propos

• Index MySQL en bas Structures de données et algorithmes de couche
• Principes d'optimisation des performances MySQL - Partie 1
• Optimisation des performances MySQL - Pratique 1
• Optimisation des performances MySQL - Pratique 2
• Verrous MySQL et les niveaux d'isolement des transactions

Plus tôt, nous avons parlé de la structure de données sous-jacente et de l'algorithme de la base de données MySQL ainsi que de certains contenus sur l'optimisation des performances de MySQL. Et l’article précédent parlait des verrous de lignes et des niveaux d’isolation des transactions de MySQL. Cet article se concentrera sur les types de verrous et les principes de verrouillage.

Divisez d'abord les verrous mysql :

1. Divisez selon la granularité du verrou : verrou de ligne, verrou de table, verrou de page
2. Divisez selon la façon dont le le verrou est utilisé : le verrou de partage, le verrou exclusif (une implémentation du verrou pessimiste)
3. Il existe également deux verrous idéologiques : le verrou pessimiste et le verrou optimiste.
4. Il existe plusieurs types de verrouillage au niveau de la ligne dans InnoDB : Record Lock, Gap Lock, Next-key Lock
5. Record Lock : verrouillez l'enregistrement d'index
6. Gap Lock : Gap Lock
7. Next-key Lock : Record Lock+Gap Lock

Table Lock

Le verrouillage au niveau de la table est le verrou le plus granulaire parmi les verrous MySQL, ce qui signifie L'opération actuelle verrouille la table entière. La surcharge de ressources est inférieure au verrouillage des lignes et aucun blocage ne se produira. Cependant, la probabilité d'un conflit de verrouillage est très élevée. Pris en charge par la plupart des moteurs MySQL, MyISAM et InnoDB prennent en charge les verrous au niveau de la table, mais InnoDB utilise par défaut les verrous au niveau des lignes.

Le verrouillage de table est implémenté par le serveur MySQL. Généralement, la table entière sera verrouillée lors de l'exécution d'instructions DDL, telles que ALTER TABLE et d'autres opérations. Lors de l'exécution d'une instruction SQL, vous pouvez également spécifier explicitement une table à verrouiller.

Le verrouillage des tables utilise la technologie de verrouillage unique, c'est-à-dire utiliser la commande lock au début de la session pour verrouiller toutes les tables qui seront utilisées ultérieurement avant que la table ne soit libérée, uniquement ces verrous ajoutés. sont accessibles. Les tables verrouillées ne peuvent pas accéder aux autres tables jusqu'à ce que tous les verrous de table soient finalement libérés via le déverrouillage des tables.

En plus d'utiliser les tables de déverrouillage pour afficher la libération du verrou, l'exécution de l'instruction lock table lorsque la session détient d'autres verrous de table libérera les verrous précédemment détenus par la session exécutant la transaction de démarrage ou commencera lorsque la session détient une autre table ; locks démarre la transaction, le verrou précédemment détenu sera également libéré.

Utilisation du verrouillage partagé :

LOCK TABLE table_name [ AS alias_name ] READ复制代码

Utilisation du verrouillage exclusif :

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码

Utilisation du déverrouillage :

unlock tables;复制代码

Verrouillage de ligne

Le verrouillage au niveau de la ligne est le verrouillage le plus granulaire de Mysql, ce qui signifie que seule la ligne de l'opération en cours est verrouillée. Les verrous au niveau des lignes peuvent réduire considérablement les conflits dans les opérations de base de données. Sa granularité de verrouillage est la plus petite, mais la surcharge de verrouillage est également la plus importante. Des situations de blocage peuvent survenir. Les verrous au niveau des lignes sont divisés en verrous partagés et verrous exclusifs en fonction de leur utilisation.

Différents moteurs de stockage ont différentes implémentations de verrouillage de ligne S'il n'y a pas d'explication particulière plus tard, le verrouillage de ligne fait spécifiquement référence au verrouillage de ligne implémenté par InnoDB.

Avant de comprendre le principe de verrouillage d'InnoDB, vous devez avoir une certaine compréhension de sa structure de stockage. InnoDB est un index clusterisé, c'est-à-dire que les nœuds feuilles de l'arborescence B+ stockent à la fois l'index de clé primaire et les lignes de données. Les nœuds feuilles de l'index secondaire d'InnoDB stockent les valeurs de clé primaire, donc lors de l'interrogation de données via l'index secondaire, vous devez récupérer la clé primaire correspondante dans l'index clusterisé et interroger à nouveau. Pour des connaissances détaillées sur les index MySQL, vous pouvez consulter « Structure et algorithme sous-jacents des données de l'index MySQL ».

Ce qui suit utilise l'exécution de deux SQL comme exemple pour expliquer le principe de verrouillage d'InnoDB pour les données à une seule ligne.

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';复制代码

Le premier SQL utilise l'index de clé primaire pour interroger, il vous suffit donc d'ajouter un verrou en écriture sur l'index de clé primaire id = 49

Le deuxième SQL utilise l'index secondaire pour query , puis ajoutez d'abord un verrou en écriture sur le nom de l'index = Tom, puis utilisez l'index secondaire InnoDB pour interroger à nouveau en fonction de l'index de clé primaire, vous devez donc également ajouter un verrou en écriture sur l'ID d'index de clé primaire = 49, comme le montre la figure ci-dessus.

C'est-à-dire que l'utilisation de l'index de clé primaire nécessite l'ajout d'un verrou, et l'utilisation de l'index secondaire nécessite l'ajout d'un verrou sur l'index secondaire et l'index de clé primaire.

Maintenant que vous comprenez le principe de verrouillage de la mise à jour d'une seule ligne de données basée sur l'index, que se passe-t-il si l'opération de mise à jour implique plusieurs lignes, comme dans le scénario d'exécution SQL suivant.

update user set age = 10 where id > 49;复制代码

Le déverrouillage dans ce scénario est plus compliqué. Il existe de nombreuses méthodes d'optimisation. Je ne comprends pas encore cela. laissez un message ci-dessous pour expliquer.

Verrouillage de page

Le verrouillage au niveau de la page est un verrou dans MySQL dont la granularité du verrouillage se situe entre le verrouillage au niveau de la ligne et le verrouillage au niveau de la table. Les verrous au niveau des tables sont rapides mais comportent de nombreux conflits. Les verrous au niveau des lignes présentent peu de conflits mais sont lents. Nous avons donc pris un niveau de page compromis et verrouillé un groupe d'enregistrements adjacents à la fois. BDB prend en charge les verrous au niveau de la page.

Verrou partagé/verrou exclusif

Verrou partagé (Share Lock)

Le verrou partagé, également appelé verrou de lecture, est un verrou créé par une opération de lecture. D'autres utilisateurs peuvent lire les données simultanément, mais aucune transaction ne peut modifier les données (acquérir un verrou exclusif sur les données) tant que tous les verrous partagés n'ont pas été libérés.

Si la transaction T ajoute un verrou partagé aux données A, les autres transactions ne peuvent ajouter que des verrous partagés à A et ne peuvent pas ajouter de verrous exclusifs. Les transactions bénéficiant de verrous partagés peuvent uniquement lire des données et ne peuvent pas les modifier.

Utilisation

SELECT ... LOCK IN SHARE MODE;

Ajoutez LOCK IN SHARE MODE après l'instruction de requête, et Mysql ajoutera un verrou partagé à chaque ligne du résultat de la requête lorsqu'il n'y a pas d'autre thread. verrous Lorsqu'une ligne du jeu de résultats de la requête utilise un verrou exclusif, elle peut demander avec succès un verrou partagé, sinon elle sera bloquée. D'autres threads peuvent également lire des tables qui utilisent des verrous partagés, et ces threads lisent la même version des données.

Verrouillage exclusif (eXclusive Lock)

Le verrouillage exclusif est également appelé verrouillage en écriture Si la transaction T ajoute un verrou exclusif aux données A, les autres transactions ne peuvent plus ajouter aucun type de blocage à A. . Les transactions bénéficiant de verrous exclusifs peuvent à la fois lire et modifier des données.

Utilisation

SELECT ... FOR UPDATE;

Ajoutez FOR UPDATE après l'instruction de requête, et Mysql ajoutera un verrou exclusif à chaque ligne du résultat de la requête lorsqu'il n'y a aucun autre thread. verrous Lorsqu'une ligne du jeu de résultats de la requête utilise un verrou exclusif, elle peut demander avec succès un verrou exclusif, sinon elle sera bloquée.

Verrouillage optimiste et verrouillage pessimiste

Comme introduit dans le mécanisme de verrouillage de la base de données, la tâche du contrôle de concurrence dans le système de gestion de base de données (SGBD) est de garantir que plusieurs transactions accèdent à la même base de données en même temps, le traitement des données ne détruit pas l'isolement et l'unité des transactions ni l'unité de la base de données.

Le contrôle de concurrence optimiste (verrouillage optimiste) et le contrôle de concurrence pessimiste (verrouillage pessimiste) sont les principaux moyens techniques utilisés pour le contrôle de concurrence.

Qu'il s'agisse d'un verrouillage pessimiste ou d'un verrouillage optimiste, ce sont des concepts définis par les gens et peuvent être considérés comme une sorte de pensée. En fait, non seulement les concepts de verrouillage optimiste et pessimiste existent dans les systèmes de bases de données relationnelles, mais Memcache, hibernate, tair, etc. ont également des concepts similaires.

Pour différents scénarios commerciaux, différentes méthodes de contrôle de concurrence doivent être utilisées. Par conséquent, ne comprenez pas le contrôle de concurrence optimiste et le contrôle de concurrence pessimiste au sens étroit en tant que concepts du SGBD, et ne les confondez pas avec les mécanismes de verrouillage (verrous de ligne, verrous de table, verrous exclusifs et verrous partagés) fournis dans les données. En fait, dans les SGBD, le verrouillage pessimiste est implémenté en utilisant le mécanisme de verrouillage fourni par la base de données elle-même.

Verrouillage pessimiste

Dans les systèmes de gestion de bases de données relationnelles, le contrôle de concurrence pessimiste (également connu sous le nom de « verrouillage pessimiste », Pessimistic Concurrency Control, abréviation « PCC ») est une sorte de contrôle de concurrence méthode. Cela empêche une transaction de modifier les données d'une manière qui affecterait d'autres utilisateurs. Si une opération effectuée par une transaction applique un verrou à une ligne de données, ce n'est que lorsque la transaction libère le verrou que d'autres transactions peuvent effectuer des opérations en conflit avec le verrou. Le contrôle de concurrence pessimiste est principalement utilisé dans les environnements où les conflits de données sont intenses et dans les environnements où le coût de l'utilisation de verrous pour protéger les données lorsque des conflits de concurrence se produisent est inférieur au coût de l'annulation des transactions.

Le verrouillage pessimiste, comme son nom l'indique, fait référence à une attitude conservatrice (pessimiste) à l'égard des données modifiées par le monde extérieur (y compris d'autres transactions courantes de ce système et le traitement des transactions provenant de systèmes externes), donc , en gardant les données dans un état verrouillé pendant tout le processus de traitement des données. La mise en œuvre du verrouillage pessimiste repose souvent sur le mécanisme de verrouillage fourni par la base de données (seul le mécanisme de verrouillage fourni par la couche base de données peut véritablement garantir l'exclusivité de l'accès aux données. Sinon, même si le mécanisme de verrouillage est implémenté dans ce système, il n'y a pas garantir que le système externe ne le modifiera pas. Données)

Le processus spécifique de verrouillage pessimiste

• Avant de modifier un enregistrement, essayez d'ajouter un verrouillage exclusif à l'enregistrement
• Si le verrou échoue, cela signifie que l'enregistrement est en cours de modification et que la requête en cours devra peut-être attendre ou lever une exception. La méthode de réponse spécifique est décidée par le développeur en fonction des besoins réels ;
• Si le verrouillage réussit, l'enregistrement peut être modifié et il sera déverrouillé une fois la transaction terminée.
• S'il existe d'autres opérations pour modifier l'enregistrement ou ajouter un verrou exclusif, il attendra que nous le déverrouillions ou lançons directement une exception.

Avantages et inconvénients du verrouillage pessimiste

Le verrouillage pessimiste adopte en fait la stratégie « obtenir le verrou d'abord avant d'accéder », qui garantit la sécurité du traitement des données, mais en termes d'efficacité, car le mécanisme de verrouillage supplémentaire génère une surcharge supplémentaire et augmente le risque de blocage. Et cela réduit la concurrence ; lorsqu'un élément obtient une ligne de données, d'autres éléments doivent attendre que la transaction soit soumise avant de pouvoir opérer sur cette ligne de données.

Verrouillage optimiste

Dans les systèmes de gestion de bases de données relationnelles, le contrôle de concurrence optimiste (également connu sous le nom de « verrouillage optimiste », Optimistic Concurrency Control, abréviation « OCC ») est une méthode de contrôle de concurrence. Cela suppose que les transactions simultanées multi-utilisateurs ne s'affecteront pas pendant le traitement et que chaque transaction peut traiter la partie des données qu'elle affecte sans générer de verrous. Avant de valider les mises à jour des données, chaque transaction vérifiera d'abord si d'autres transactions ont modifié les données après que la transaction ait lu les données. Si d'autres transactions ont des mises à jour, la transaction soumise sera annulée.

Verrouillage optimiste (Verrouillage optimiste) Par rapport au verrouillage pessimiste, le verrouillage optimiste suppose que les données ne provoqueront pas de conflits dans des circonstances normales, de sorte que les données ne seront formellement en conflit que lorsque les données seront soumises pour mise à jour. pour détecter ou non, si un conflit est trouvé, renvoyer les informations d'erreur à l'utilisateur et laisser l'utilisateur décider quoi faire.

Par rapport au verrouillage pessimiste, le verrouillage optimiste n'utilise pas le mécanisme de verrouillage fourni par la base de données lors du traitement de la base de données. La manière générale d'implémenter le verrouillage optimiste consiste à enregistrer la version des données.

Version des données, un identifiant de version ajouté aux données. Lors de la lecture des données, la valeur de l'identifiant de version est lue ensemble. Chaque fois que les données sont mises à jour, l'identifiant de version est mis à jour en même temps. Lorsque nous soumettons une mise à jour, nous comparons les informations de version actuelle de l'enregistrement correspondant dans la table de base de données avec la valeur d'identification de version supprimée pour la première fois si le numéro de version actuelle de la table de base de données est égal à la valeur d'identification de version supprimée. pour la première fois, mettez-le à jour, sinon il est considéré comme une donnée expirée.

Les avantages et les inconvénients du verrouillage optimiste

Le contrôle de concurrence optimiste estime que la probabilité d'une course aux données entre les transactions est relativement faible, alors faites-le aussi directement que possible jusqu'à ce que le verrouillage soit seulement verrouillé lors de la soumission, donc aucun verrou ou blocage ne se produira. Cependant, si vous procédez simplement, vous risquez toujours de rencontrer des résultats inattendus. Par exemple, si les deux transactions lisent une certaine ligne de la base de données puis la réécrivent dans la base de données après modification, vous rencontrerez un problème.

Verrouillage partagé par intention/Verrouillage exclusif par intention

Étant donné que les verrous de table et les verrous de ligne ont des étendues de verrouillage différentes, ils entreront en conflit les uns avec les autres. Ainsi, lorsque vous souhaitez ajouter un verrou de table, vous devez d'abord parcourir tous les enregistrements de la table pour déterminer si un verrou exclusif est ajouté. Cette méthode de vérification de traversée est évidemment inefficace. MySQL introduit des verrous d'intention pour détecter les conflits entre les verrous de table et les verrous de ligne.

Les verrous d'intention sont également des verrous au niveau de la table et peuvent également être divisés en verrous d'intention de lecture (verrous IS) et de verrous d'intention d'écriture (verrous IX). Lorsqu'une transaction souhaite ajouter un verrou en lecture ou un verrou en écriture sur un enregistrement, elle doit d'abord ajouter un verrou d'intention sur la table. De cette façon, il est très simple de déterminer s'il y a des enregistrements dans la table qui sont verrouillés. Vérifiez simplement s'il y a un verrou intentionnel sur la table.

Les verrous d'intention n'entreront pas en conflit entre eux, ni avec les verrous de table AUTO_INC. Il bloquera uniquement les verrous de lecture au niveau de la table ou les verrous d'écriture au niveau de la table. De plus, les verrous d'intention ne seront pas en conflit avec les verrous de ligne. les verrous de ligne n'entreront en conflit qu'avec les verrous de ligne.

Les verrous d'intention sont automatiquement ajoutés par InnoDB et ne nécessitent aucune intervention de l'utilisateur.

Pour l'insertion, la mise à jour et la suppression, InnoDB ajoutera automatiquement des verrous exclusifs (X) aux données impliquées

Pour les instructions Select générales, InnoDB n'ajoutera aucun verrou ni transaction ; Vous pouvez ajouter des verrous partagés ou des verrous exclusifs à l'affichage via les instructions suivantes.

Verrou partagé d'intention

Verrou partagé d'intention (IS) : indique que la transaction se prépare à ajouter un verrou partagé à la ligne de données, ce qui signifie qu'une ligne de données doit être obtenue avant d'ajouter un verrou partagé. Verrouillage IS de la table

Verrouillage exclusif d'intention (Verrouillage exclusif)

Verrouillage exclusif d'intention (IX) : similaire à ce qui précède, indiquant que la transaction se prépare à ajouter un verrou exclusif. à la ligne de données, indiquant que la transaction est dans une ligne de données. Avant d'ajouter un verrou exclusif, vous devez d'abord obtenir le verrou IX de la table.

Verrouillage d'enregistrement

Le verrouillage d'enregistrement est le verrouillage de ligne le plus simple, et il n'y a rien à dire à ce sujet. Le verrou du principe de verrouillage InnoDB décrit ci-dessus est le verrou d'enregistrement, qui verrouille uniquement l'enregistrement avec id = 49 ou name = 'Tom'.

Lorsque l'instruction SQL ne peut pas utiliser l'index, une analyse complète de la table sera effectuée. À ce moment, MySQL ajoutera des verrous d'enregistrement à toutes les lignes de données de la table entière, puis la couche MySQL Server les filtrera. . Cependant, lors du filtrage au niveau de la couche MySQL Server, s'il s'avère que la condition WHERE n'est pas remplie, le verrou sur l'enregistrement correspondant sera libéré. Cela garantit que seuls les verrous sur les enregistrements qui remplissent les conditions seront finalement maintenus, mais l'opération de verrouillage de chaque enregistrement ne peut pas être omise.

Les opérations de mise à jour doivent donc être effectuées en fonction de l'index. Sans index, cela consommera non seulement beaucoup de ressources de verrouillage et augmentera la surcharge de la base de données, mais réduira également considérablement les performances de concurrence de la base de données.

Gap Lock

Lorsque nous utilisons des conditions de plage au lieu de conditions d'égalité pour récupérer des données et demander des verrous partagés ou exclusifs, InnoDB donnera l'index de l'enregistrement de données existant qui répond aux conditions L'élément est verrouillé ; pour les enregistrements dont les valeurs de clé sont dans la plage de conditions mais n'existent pas, InnoDB verrouillera également le "gap". Ce mécanisme de verrouillage est ce qu'on appelle le verrouillage de l'espace.

间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。

要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog

 show variables like 'innodb_locks_unsafe_for_binlog';复制代码

innodb_locks_unsafe_for_binlog：默认

值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。

# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1复制代码

案例1：唯一索引的间隙锁

测试环境：

MySQL5.7，InnoDB，默认的隔离级别（RR）

示例表：

CREATE TABLE `my_gap` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码

在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 5]
3. (5, 7]
4. (7, 11]
5. (11, +infinity]

只使用记录锁（行锁），不会产生间隙锁

/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。

产生间隙锁

恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：

/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。

恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？

/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

结论

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁（行锁）和间隙锁，如果记录存在，则只会产生记录锁（行锁）；
2. 对于查找某一范围内的查询语句，会产生间隙锁。

案例2：普通索引的间隙锁

示例表：id 是主键，在 number 上，建立了一个普通索引。

# 注意：number 不是唯一值CREATE TABLE `my_gap1` (  `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 `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码

在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 3]
3. (3, 8]
4. (8, 12]
5. (12, +infinity]

测试1

我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码

我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：

这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。

测试2

我们再进行以下测试，这里将数据还原成初始化那样

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码

查看表中的数据；

这里有一个奇怪的现象：

• 事务3 添加 id = 6，number = 8 的数据，阻塞了；
• 事务4 添加 id = 8，number = 8 的数据，正常执行了；
• 事务7 将 id = 11，number = 12 的数据修改为 id = 11， number = 5 的操作，给阻塞了。

这是为什么？我们来看看下面的图：

从图中库看出，当 number 相同时，会根据主键 id 来排序

1. 事务 3 添加的 id = 6，number = 8，这条数据是在 (3,8) 的区间里边，所以会阻塞；
2. 事务 4 添加的 id = 8，number = 8，这条数据实在 (8,12) 区间里边，所以不会阻塞；
3. 事务 7 的修改语句相当于 在 (3,8) 的区间里边插入一条数据，所以也被阻塞了。

结论

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通普通索引排序，再根据唯一索引排序。

临键锁（Next-key Locks）

临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)

此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。

插入意向锁（Insert Intention Locks）

插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

插入意向锁的作用：

1. 为来唤起等待。由于该间隙已经有锁，插入时必须阻塞，插入意向锁的作用具有阻塞功能；
2. 插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？间隙锁直接不相互排斥。不可以阻塞即唤起等待，会造成幻读。
3. 为什么不实用记录锁（行锁）或 临键锁？申请了记录锁或临键锁，临键锁之间可能相互排斥，即影响 insert 的并发性。

自增锁（Auto-inc Locks）

AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

自增操作

使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：

• auto_increment_offset 表示起始数字
• auto_increment_increment 表示调动幅度（即每次增加n个数字，2就代表每次+2）

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();复制代码

自增锁

首先insert大致上可以分成三类：

1. simple insert 如insert into t(name) values('test')
2. bulk insert 如load data | insert into ... select .... from ....
3. mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：

• 0 ：traditonal (每次都会产生表锁)
• 1 ：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）
• 2 ：interleaved （不会锁表，来一个处理一个，并发最高）

  MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。

 show variables like 'innodb_autoinc_lock_mode';复制代码

traditonal

innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。

• 它提供来一个向后兼容的能力
• 在这一模式下，所有的 insert 语句（“insert like”）都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁。注意，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句；
• 它能保证值分配的可预见性、可连续性、可重复性，这个也就是保证了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值（它保证了基于语句复制的安全）；
• 由于在这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响了并发的插入。

consecutive

innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。

• 这一模式下对 simple insert 做了优化，由于 simple insert 一次性插入的值的个数可以立马得到确定，所以 MyQL 可以一次生成几个连续的值，用于这个 insert 语句。总得来说这个对复制也是安全的（它保证了基于语句复制的安全）；
• 这一模式也是MySQL的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值就可以提前释放锁。

interleaved

innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。

• 由于这个模式下已经没有了 auto_inc 锁，所以这个模式下的性能是最好的，但是也有一个问题，就是对于同一个语句来说它所得到的 auto_incremant 值可能不是连续的。

如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。

Résumé

Caractéristiques des verrous InnoDB

1. Lors d'une requête sans conditions d'index, InnoDB utilise des verrous de table !
2. Étant donné que le verrouillage de ligne de MySQL est destiné à l'index et non à l'enregistrement, bien que l'on accède aux enregistrements de lignes différentes, si la même clé d'index est utilisée, un conflit de verrouillage se produira.
3. Lorsqu'une table a plusieurs index, différentes transactions peuvent utiliser différents index pour verrouiller différentes lignes. De plus, que ce soit en utilisant des index de clé primaire, des index uniques ou des index ordinaires, InnoDB utilisera des verrous de ligne pour verrouiller les données.
4. Même si un champ d'index est utilisé dans la condition, l'utilisation ou non de l'index pour récupérer des données est déterminée par MySQL en jugeant le coût des différents plans d'exécution. Si MySQL estime que l'analyse complète de la table est plus efficace, comme pour certaines très grandes tables, il n'utilisera pas d'index, auquel cas InnoDB utilisera des verrous de table au lieu de verrous de ligne. Par conséquent, lors de l'analyse des conflits de verrouillage, n'oubliez pas de vérifier le plan d'exécution SQL (vue expliquer) pour confirmer si l'index est réellement utilisé.

Mode de verrouillage

Les modes de verrouillage sont : le verrouillage d'intention de lecture, le verrouillage d'intention d'écriture, le verrouillage de lecture, le verrouillage d'écriture et le verrouillage par incrémentation automatique (auto_inc).

Matrice de compatibilité des différents verrous de mode

	IS	IX	S	X	AI
IS	兼容	兼容	兼容		兼容
IX	兼容	兼容			兼容
S	兼容		兼容
X
AI	兼容	兼容

Pour résumer, il y a les points suivants :

• Les verrous d'intention ne sont pas en conflit entre eux
• 🎜>
Le verrouillage S est uniquement compatible avec le verrouillage S/IS et entre en conflit avec d'autres verrouillages ;
• Le verrouillage X est en conflit avec tous les autres verrouillages
• Le verrouillage AI est uniquement compatible avec le verrouillage intentionnel ;

Types de verrous

Selon la granularité du verrou, les verrous peuvent être subdivisés en verrous de table et verrous de rangée. Les verrous peuvent également être utilisés selon différents scénarios. Ils sont ensuite subdivisés en Next-Key Lock, Gap Lock gap lock, Record Lock record lock et Insert. Verrouillage GAP d'intention

.

Différents verrous verrouillent différentes positions. Par exemple, le verrouillage d'enregistrement verrouille uniquement l'enregistrement correspondant, tandis que le verrouillage d'espace verrouille l'intervalle entre les enregistrements et le verrouillage de la touche suivante verrouille l'enregistrement et le précédent. Les plages de verrouillage des différents types de serrures sont à peu près celles indiquées dans la figure ci-dessous.

Matrice de compatibilité des différents types de serrures

	RECORD	GAP	NEXT-KEY	II GAP
RECORD		兼容		兼容
GAP	兼容	兼容	兼容	兼容
NEXT-KEY		兼容		兼容
II GAP	兼容			兼容

NEXT-KEY GAP II GAP tableau >

Parmi eux, la première ligne représente le verrou existant, et la première colonne représente le verrou à ajouter. L'insertion de verrous d'intention est spéciale, nous faisons donc d'abord un résumé de l'insertion de verrous d'intention, comme suit :

• L'insertion de verrous d'intention n'affecte pas les autres transactions ni les autres verrous. En d'autres termes, une transaction a acquis le verrou d'intention d'insertion, ce qui n'a aucun impact sur les autres transactions
• Le verrou d'intention d'insertion entre en conflit avec le verrou d'espacement et le verrou Next-key ; C'est-à-dire que si une transaction souhaite acquérir le verrou d'intention d'insertion, si une autre transaction a déjà ajouté un verrou d'espacement ou un verrou à clé suivante, elle sera bloquée.

Les règles pour les autres types de verrous sont relativement simples :

• Les verrous d'espacement n'entrent pas en conflit avec d'autres verrous (à l'exclusion des verrous d'intention d'insertion
• ) ;

  Le verrouillage de l'enregistrement est en conflit avec le verrouillage de l'enregistrement, le verrouillage de la touche suivante est en conflit avec le verrouillage de la clé suivante, le verrouillage de l'enregistrement est en conflit avec le verrouillage de la clé suivante

	ENREGISTREMENT	GAP	II GAP
ENREGISTREMENT		Compatible		Compatible
Compatible	Compatible	Compatible	Compatible
NEXT-KEY		Compatible		Compatible
Compatible			Compatible

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!