Recommandations d'apprentissage gratuites associées : Tutoriel MySQL
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 :
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;复制代码
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
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.
É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 (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 (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.
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.
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复制代码
测试环境：
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 */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;复制代码
从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。
示例表：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最后提交），分别执行下面的语句：
/* 开启事务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) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。
我们再进行以下测试，这里将数据还原成初始化那样
/* 开启事务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;复制代码
查看表中的数据；
这里有一个奇怪的现象：
这是为什么？我们来看看下面的图：
从图中库看出，当 number 相同时，会根据主键 id 来排序
临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 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，临键锁则也会失效。
插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。
插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。
插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。
插入意向锁的作用：
AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：
使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：
通过使用last_insert_id()函数可以获得最后一个插入的数字
select last_insert_id();复制代码
首先insert大致上可以分成三类：
如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：
MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。
show variables like 'innodb_autoinc_lock_mode';复制代码
innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。
innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。
innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。
如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。
由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。
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).
| IS | IX | S | X | AI | |
|---|---|---|---|---|---|
| IS | 兼容 | 兼容 | 兼容 | 兼容 | |
| IX | 兼容 | 兼容 | 兼容 | ||
| S | 兼容 | 兼容 | |||
| X | |||||
| AI | 兼容 | 兼容 |
Pour résumer, il y a les points suivants :
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.
| RECORD | GAP | NEXT-KEY | II GAP | |
|---|---|---|---|---|
| RECORD | 兼容 | 兼容 | ||
| GAP | 兼容 | 兼容 | 兼容 | 兼容 |
| NEXT-KEY | 兼容 | 兼容 | ||
| II GAP | 兼容 | 兼容 |
| ENREGISTREMENT | GAP | NEXT-KEYII 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!
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)
