Compréhension approfondie des verrous dans MySQL (verrous globaux, verrous au niveau des tables, verrous de ligne)

Cet article vous amènera à comprendre les verrous dans MySQL et à présenter les verrous globaux, les verrous au niveau de la table et les verrous de ligne de MySQL. J'espère qu'il vous sera utile !

Selon la portée du verrouillage, les verrous dans MySQL peuvent être grossièrement divisés en trois catégories : les verrous globaux, les verrous au niveau de la table et les verrous de ligne

1. Les verrous globaux

Les verrous globaux verrouillent l'ensemble de la base de données. exemple. MySQL fournit une méthode pour ajouter un verrou de lecture global. La commande est Flush tables with read lock. Lorsque vous devez mettre l'intégralité de la bibliothèque en lecture seule, vous pouvez utiliser cette commande. Après cela, les instructions suivantes des autres threads seront bloquées : les instructions de mise à jour des données (ajouter, supprimer et modifier des données), les instructions de définition des données. (y compris la création de tables, la modification des structures de table, etc.) et mettre à jour les instructions de validation de transaction. [Recommandations associées : tutoriel mysql (vidéo)] Flush tables with read lock。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。【相关推荐：mysql教程(视频)】

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本

但是让整个库都只读，可能出现以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

在可重复读隔离级别下开启一个事务能够拿到一致性视图

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。single-transaction只适用于所有的表使用事务引擎的库

1.既然要全库只读，为什么不使用set global readonly=true的方式？

• 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大
• 在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

二、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

表锁的语法是lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

如果在某个线程A中执行lock tables t1 read,t2 wirte;

Utilisation typique des verrous globaux Le scénario consiste à effectuer une sauvegarde logique de l’intégralité de la base de données. C'est-à-dire, sélectionnez chaque table de la base de données entière et enregistrez-la sous forme de texte

Mais si vous rendez la base de données entière en lecture seule, les problèmes suivants peuvent survenir :

Si vous sauvegardez sur la base de données principale, les mises à jour ne peuvent pas être effectué pendant la période de sauvegarde, et l'affaire est essentiellement Si vous sauvegardez sur la base de données esclave, la base de données esclave ne peut pas exécuter le binlog synchronisé à partir de la base de données maître pendant la période de sauvegarde, ce qui entraînera un retard maître-esclave

• Ouverture. une transaction sous le niveau d'isolement de lecture répétable peut obtenir une cohérence SexViewL'outil de sauvegarde logique officiel est mysqldump. Lorsque mysqldump utilise le paramètre --single-transaction, une transaction sera démarrée avant l'importation des données pour garantir l'obtention d'une vue cohérente. Grâce à la prise en charge de MVCC, les données peuvent être mises à jour normalement pendant ce processus. La transaction unique s'applique uniquement à toutes les tables utilisant les bibliothèques du moteur de transaction
• 1 Puisque la bibliothèque entière est en lecture seule, pourquoi ne pas utiliser set global readonly=true ?
Dans certains systèmes, la valeur en lecture seule sera utilisée pour une autre logique, comme déterminer si une bibliothèque est la bibliothèque principale ou la bibliothèque de secours. Par conséquent, la manière de modifier les variables globales a un plus grand impact. Il existe des différences dans le mécanisme de gestion des exceptions. Si le client est déconnecté anormalement après l'exécution de la commande Flush tables with read lock, MySQL libérera automatiquement le verrou global et la bibliothèque entière reviendra à un état où elle peut être mise à jour normalement. Après avoir défini l'ensemble de la bibliothèque en lecture seule, si une exception se produit sur le client, la base de données restera en lecture seule, ce qui entraînera le maintien de l'ensemble de la bibliothèque dans un état non inscriptible pendant une longue période, avec un risque plus élevé. 2. Tableau. -verrouillage de niveau

Il existe deux types de verrous au niveau de la table dans MySQL : l'un est le verrouillage de table et l'autre est le verrouillage des métadonnées (meta data lock, MDL)

La syntaxe du verrouillage de table est le verrouillage des tables. .. lecture/écriture. Vous pouvez utiliser les tables de déverrouillage pour libérer activement le verrou, ou vous pouvez le libérer automatiquement lorsque le client se déconnecte. En plus de restreindre la lecture et l'écriture d'autres threads, la syntaxe des tables de verrouillage limite également les objets d'opération suivants de ce thread. Si vous exécutez lock tables t1 read, t2 wirte; dans un certain thread A, cette instruction, les instructions des autres threads écrivant t1 et lisant et écrivant t2 seront bloquées. Dans le même temps, le thread A ne peut effectuer que les opérations de lecture de t1 et de lecture et d'écriture de t2 avant d'exécuter les tables de déverrouillage. Même l'écriture sur t1 n'est pas autorisée🎜🎜Un autre type de verrou au niveau de la table est MDL. MDL n'a pas besoin d'être utilisé explicitement, il sera ajouté automatiquement lors de l'accès à une table. La fonction du MDL est de garantir l’exactitude de la lecture et de l’écriture. Si une requête parcourt les données d'une table et qu'un autre thread modifie la structure de la table pendant l'exécution et supprime une colonne, alors les résultats obtenus par le thread de requête ne correspondront pas à la structure de la table, ce qui ne fonctionnera certainement pas🎜🎜🎜 Dans MySQL5 version .5 introduit MDL Lors de l'ajout, de la suppression, de la modification et de l'interrogation d'une table, ajoutez des verrous de lecture MDL ; lorsque vous apportez des modifications structurelles à la table, ajoutez des verrous d'écriture MDL🎜🎜🎜🎜🎜Les verrous de lecture ne sont pas mutuellement exclusifs. Ainsi, plusieurs threads peuvent ajouter, supprimer, modifier et vérifier une table en même temps🎜🎜🎜🎜Les verrous en lecture-écriture et les verrous en écriture s'excluent mutuellement pour garantir la sécurité de la modification de la structure de la table. Par conséquent, si deux threads souhaitent ajouter des champs à une table en même temps, l'un d'eux doit attendre que l'autre ait fini de s'exécuter avant de pouvoir commencer à s'exécuter pour ajouter des champs à une table, ou modifier des champs, ou. ajoutez des index, vous devez analyser les données de la table entière. Lorsque vous utilisez de grandes tables, vous devez être particulièrement prudent pour éviter d'affecter les services en ligne🎜🎜🎜🎜 La session A est démarrée en premier. À ce moment, un verrou de lecture MDL sera ajouté à la table t. Puisque la session B nécessite également le verrou de lecture MDL, elle peut être exécutée normalement. Plus tard, la session C sera bloquée car le verrou de lecture MDL de la session A n'a pas été libéré et la session C a besoin du verrou d'écriture MDL, elle ne peut donc être bloquée. Peu importe si seule la session C elle-même est bloquée, mais toutes les futures demandes de demande de verrous de lecture MDL sur la table t seront également bloquées par la session C. Toutes les opérations d'ajout, de suppression, de modification et d'interrogation sur la table doivent d'abord demander le verrou de lecture MDL, puis elles sont toutes verrouillées, ce qui signifie que la table est désormais complètement illisible et accessible en écriture🎜

Le verrou MDL dans la transaction est appliqué au début de l'exécution de l'instruction, mais il ne sera pas libéré immédiatement après la fin de l'instruction, mais sera libéré après la soumission de l'intégralité de la transaction

1. à une petite table ?

Tout d'abord, les transactions longues doivent être résolues. Si la transaction n'est pas soumise, le verrou DML sera toujours occupé. Dans la table innodb_trx de la bibliothèque information_schema de MySQL, la transaction actuellement exécutée peut être trouvée. Si la table à modifier par DDL a une longue transaction en cours d'exécution, envisagez d'abord de suspendre la DDL ou de supprimer la longue transaction

2 Si la table à modifier est une table de point chaud, même si la quantité de données ne l'est pas. grande, la requête ci-dessus est très fréquente, mais je dois ajouter un champ. Que dois-je faire ?

Définissez le temps d'attente dans l'instruction alter table. Il est préférable que vous puissiez obtenir le verrou en écriture MDL dans le temps d'attente spécifié. Si vous ne parvenez pas à l'obtenir, ne bloquez pas les instructions commerciales suivantes et abandonnez d'abord. Répétez ensuite le processus en réessayant la commande

3. Verrouillage de ligne

Le verrouillage de ligne de MySQL est implémenté par chaque moteur au niveau de la couche moteur. Mais tous les moteurs ne prennent pas en charge les verrous de ligne. Par exemple, le moteur MyISAM ne prend pas en charge les verrous de ligne. Les verrous de ligne sont des verrous pour les enregistrements de ligne dans la table de données. Par exemple, la transaction A met à jour une ligne, et la transaction B doit également mettre à jour la même ligne à ce moment-là. Vous devez attendre la fin de l'opération de la transaction A avant de mettre à jour 1. Protocole de verrouillage en deux étapes

Deux. Transactions détenues par la transaction A Les verrous de ligne de chaque enregistrement ne sont libérés qu'au moment de la validation. L'instruction de mise à jour de la transaction B est bloquée. Jusqu'à ce que la transaction A exécute la validation, la transaction B peut continuer à s'exécuter. est ajouté à ce moment-là, mais il n'est pas libéré immédiatement lorsqu'il n'est plus nécessaire, mais il est libéré jusqu'à la fin de la transaction. Il s'agit du protocole de verrouillage en deux phases

Si plusieurs lignes doivent être verrouillées dans une transaction, les verrous les plus susceptibles de provoquer des conflits de verrouillage et d'affecter la concurrence doivent être placés aussi loin que possible

Supposons que vous souhaitiez pour mettre en œuvre une activité de transaction en ligne pour les billets de cinéma, le client A souhaite acheter des billets de cinéma au cinéma B. L'entreprise doit impliquer les opérations suivantes : 1. Déduire le prix du billet de cinéma du solde du compte du client A

2 Ajouter le prix du billet de cinéma au solde du compte du cinéma B 3.

Afin d'assurer la transaction Pour atteindre l'atomicité, ces trois opérations doivent être placées en une seule transaction. Comment organiser l'ordre de ces trois relevés dans la transaction ?

S'il y a un autre client C qui souhaite acheter des billets au théâtre B en même temps, alors la partie conflictuelle entre ces deux transactions est l'état 2. Puisqu’ils souhaitent mettre à jour le solde du même compte de cinéma, ils doivent modifier la même ligne de données. Selon le protocole de verrouillage en deux phases, tous les verrous de ligne requis pour les opérations sont libérés lorsque la transaction est validée. Par conséquent, si l'état 2 est disposé à la fin, par exemple dans l'ordre 3, 1, 2, alors le temps de verrouillage de la ligne de solde du compte du théâtre sera le plus court. Cela minimise l'attente de verrouillage entre les transactions et améliore la concurrence

2. Détection des blocages et des blocages

Dans un système simultané, des dépendances de ressources circulaires se produisent dans différents threads, et tous les threads impliqués attendent Lorsque d'autres threads libèrent des ressources, ces threads le feront. entrez dans un état d'attente infini, appelé blocage

La transaction A attend que la transaction B libère le verrou de ligne avec id=2, tandis que la transaction B attend que la transaction A libère le verrou de ligne avec id=1. La transaction A et la transaction B attendent que les ressources de l'autre soient libérées, ce qui signifie qu'elles sont entrées dans un état de blocage. Lorsqu'un blocage se produit, il existe deux stratégies :

Une stratégie consiste à attendre directement jusqu'à l'expiration du délai. Ce délai d'attente peut être défini via le paramètre innodb_lock_wait_timeout. Une autre stratégie consiste à lancer la détection d'un interblocage, en annulant activement une transaction dans la chaîne d'interblocage pour permettre aux autres transactions de continuer à s'exécuter. Définissez le paramètre innodb_deadlock_detect sur on, ce qui signifie activer cette logique

Dans InnoDB, la valeur par défaut de innodb_lock_wait_timeout est de 50s, ce qui signifie que si la première stratégie est adoptée, lorsqu'un blocage se produit, le premier thread verrouillé devra attendre for Il expirera et se terminera après 50 secondes, puis d'autres threads pourront continuer à s'exécuter. Pour les services en ligne, ce temps d'attente est souvent inacceptable. Dans des circonstances normales, une stratégie de vérification active des blocages doit être adoptée et la valeur par défaut de innodb_deadlock_detect elle-même est activée. La surveillance active des blocages peut détecter et gérer rapidement les blocages lorsqu'ils se produisent, mais elle comporte des charges supplémentaires. Chaque fois qu'une transaction est verrouillée, il est nécessaire de vérifier si le thread dont elle dépend est verrouillé par d'autres, et ainsi de suite, et enfin de déterminer s'il y a une attente circulaire, ce qui est une impasse si toutes les transactions doivent mettre à jour la même ligne. Dans ce scénario, chaque thread nouvellement bloqué doit déterminer s'il provoquera un blocage en raison de son propre ajout. Il s'agit d'une opération avec une complexité temporelle de O(n)

Comment résoudre ces problèmes de performances de mise à jour de ligne chaude ?

1. Si vous vous assurez que cette entreprise ne se bloquera pas, vous pouvez désactiver temporairement la détection des blocages

• 2.

  3. Transformez une ligne en plusieurs lignes logiques pour réduire les conflits de verrouillage. En prenant comme exemple le compte du théâtre, vous pouvez envisager de le placer sur plusieurs enregistrements, par exemple 10 enregistrements. Le montant total du compte du théâtre est égal à la somme des valeurs de ces 10 enregistrements. De cette façon, chaque fois que vous souhaitez ajouter de l'argent au compte du cinéma, vous pouvez sélectionner au hasard l'un des enregistrements à ajouter. De cette façon, la probabilité de chaque conflit devient 1/10 du membre d'origine, ce qui peut réduire le nombre d'attentes de verrouillage et réduire la consommation CPU de la détection de blocage

  4. Pourquoi l'exécution de l'instruction est-elle si lente quand je vérifier seulement une ligne ?

  Construisez une table avec deux champs id et c et insérez-y 100 000 lignes d'enregistrements

  CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
  BEGIN
  	declare i int;
    set i=1;
    while(i<=100000) do
      insert into t values(i,i);
      set i=i+1;
    end while;
  END

  1 Catégorie 1 : La requête ne renvoie pas pendant longtemps

  select * from t3 where id=1;

  Le résultat de la requête ne revient pas pendant. un long moment Revenez, utilisez la commande show processlist pour vérifier l'état de l'instruction actuelle

  1), En attente du verrouillage MDL

  Comme le montre la figure ci-dessous, utilisez la commande show processlist; pour afficher le diagramme de la table En attente verrouillage des métadonnées

  
  Cet état représente le courant. Il y a un thread qui demande ou maintient le verrou en écriture MDL sur la table t, bloquant l'instruction select

  Récurrence de la scène :

  
  sessionA détient l'écriture MDL verrouiller la table t via la commande lock table et sessionB La requête doit acquérir le verrou de lecture MDL. Par conséquent, la sessionB entre dans l'état d'attente

  La façon de résoudre ce genre de problème est de découvrir qui détient le verrou en écriture MDL, puis de le tuer. Cependant, dans le résultat de show processlist, la colonne Command de sessionA est Sleep, ce qui rend la recherche peu pratique. Vous pouvez trouver directement l'ID du processus à l'origine du blocage en interrogeant la table sys.schema_table_lock_waits et déconnecter la connexion avec le kill. commande (vous devez définir performance_schema=on lors du démarrage de MySQL. Par rapport à sa désactivation, il y aura environ 10 % de perte de performances)

  select blocking_pid from sys.schema_table_lock_waits;

  2), attendez flush

  et exécutez l'instruction SQL suivante sur la table t:

  select * from information_schema.processlist where id=1;

  Check Le statut d'un certain thread est En attente de vidage de la table
  
  Ce statut signifie qu'il y a maintenant un thread qui effectue une opération de vidage sur la table t. Il existe généralement deux utilisations des opérations de vidage sur les tables dans MySQL :

  flush tables t with read lock;flush tables with read lock;

  Ces deux instructions de vidage, si la table t est spécifiée, signifient que seule la table t est fermée si aucun nom de table spécifique n'est spécifié, cela signifie fermer toutes les tables ouvertes dans ; MySQL

  Mais dans des circonstances normales, ces deux instructions s'exécutent très rapidement, à moins qu'elles ne soient bloquées par d'autres threads

  Donc, la situation possible dans laquelle l'état En attente de vidage de la table apparaît est la suivante : il y a une table de vidage La commande est bloquée par d'autres instructions, puis il bloque l'instruction select

  Récurrence de la scène :

  
  Dans sessionA, sleep(1) est appelé une fois par ligne, donc cette instruction sera exécutée pendant 100 000 secondes par défaut, pendant cette période du tableau t a été ouvert par la sessionA. Ensuite, lorsque sessionB vide les tables t puis ferme la table t, elle doit attendre la fin de la requête de sessionA. De cette façon, si sessionC souhaite interroger à nouveau, elle sera bloquée par la commande flush
  

  3), en attente du verrouillage de ligne

  select * from t where id=1 lock in share mode;

  Puisqu'un verrou en lecture est requis lors de l'accès à l'enregistrement avec id=1, s'il y a déjà une transaction à ce moment-là. Si un verrou en écriture est maintenu sur cette ligne d'enregistrements, l'instruction select sera bloquée

  Récurrence du scénario :

  
  sessionA a démarré la transaction, occupé le verrou en écriture et ne l'a pas soumis, ce qui a provoqué le blocage de la sessionB Les raisons

  2 La deuxième catégorie : requête lente

  
  sessionA utilise d'abord la commande de démarrage de transaction avec une commande d'instantané cohérente pour ouvrir une transaction et établir une lecture cohérente du transaction (également appelée lecture d'instantané. Le mécanisme MVCC est utilisé pour lire Obtenez les données soumises dans le journal d'annulation. Sa lecture n'est donc pas bloquante), puis sessionB exécute l'instruction de mise à jour

  Après que sessionB ait exécuté 1 million d'instructions de mise à jour, elle génère 1 million de journaux de restauration

  

  带lock in share mode的语句是当前读，因此会直接读到1000001这个结果，速度很快；而select * from t where id=1这个语句是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回
  

  五、间隙锁

  建表和初始化语句如下：

  CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `c` (`c`)
  ) ENGINE=InnoDB;
  
  insert into t values(0,0,0),(5,5,5),
  (10,10,10),(15,15,15),(20,20,20),(25,25,25);

  这个表除了主键id外，还有一个索引c

  为了解决幻读问题，InnoDB引入了间隙锁，锁的就是两个值之间的空隙
  
  当执行select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录

  行锁分成读锁和写锁
  
  跟间隙锁存在冲突关系的是往这个间隙中插入一个记录这个操作。间隙锁之间不存在冲突关系
  
  这里sessionB并不会被堵住。因为表t里面并没有c=7会这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们用共同的目标，保护这个间隙，不允许插入值。但它们之间是不冲突的

  间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因为+∞是开区间，在实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合都是前开后闭区间

  间隙锁和next-key lock的引入，解决了幻读的问题，但同时也带来了一些困扰

  间隙锁导致的死锁：
  
  1.sessionA执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)

  2.sessionB执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突

  3.sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待

  4.sessionA试图插入一行(9,9,9)，被sessionB的间隙锁挡住了

  两个session进入互相等待状态，形成了死锁

  间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响并发度的

  在读提交隔离级别下，不存在间隙锁

  六、next-key lock

  表t的建表语句和初始化语句如下：

  CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `c` (`c`)
  ) ENGINE=InnoDB;
  
  insert into t values(0,0,0),(5,5,5),
  (10,10,10),(15,15,15),(20,20,20),(25,25,25);

  1、next-key lock加锁规则

  • 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间
  • 原则2：查找过程中访问到的对象才会加锁
  • 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
  • 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
  • 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

  这个规则只限于MySQL5.x系列

  2、案例一：等值查询间隙锁

  
  1.由于表t中没有id=7的记录，根据原则1，加锁单位是next-key lock，sessionA加锁范围就是(5,10]

  2.根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)

  所以，sessionB要往这个间隙里面插入id=8的记录会被锁住，但是sessionC修改id=10这行是可以的

  3、案例二：非唯一索引等值锁

  
  1.根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock

  2.c是普通索引，因此访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock

  3.根据优化2，等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)

  4.根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有任何锁，这就是为什么sessionB的update语句可以执行完成

  锁是加在索引上的，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁，这样的话sessionB的update语句会被阻塞住。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段

  4、案例三：主键索引范围锁

  
  1.开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁

  2.范围查询就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]

  所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]

  5、案例四：非唯一索引范围锁

  
  这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加上(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，因此最终sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock

  6、案例五：唯一索引范围锁bug

  
  sessionA是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了

  但是实现上，InnoDB会扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上

  所以，sessionB要更新id=20这一行是会被锁住的。同样地，sessionC要插入id=16的一行，也会被锁住

  7、案例六：非唯一索引上存在等值的例子

  insert into t values(30,10,30);

  新插入的这一行c=10，现在表里有两个c=10的行。虽然有两个c=10，但是它们的主键值id是不同的，因此这两个c=10的记录之间也是有间隙的
  
  sessionA在遍历的时候，先访问第一个c=10的记录。根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。然后sessionA向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10)到(c=15,id=15)的间隙锁

  也就是说，这个delete语句在索引c上的加锁范围，就是下图中蓝色区域覆盖的部分，这个蓝色区域左右两边都是虚线，表示开区间
  

  8、案例七：limit语句加锁

  
  加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：

  
  再删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

  9、案例八：一个死锁的例子

  
  1.sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)

  2.sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待

  3.然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚

  sessionB的加next-key lock(5,10]操作，实际上分成了两步，先是加(5,10)间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的

  七、用动态的观点看加锁

  表t的建表语句和初始化语句如下：

  CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `c` (`c`)
  ) ENGINE=InnoDB;
  
  insert into t values(0,0,0),(5,5,5),
  (10,10,10),(15,15,15),(20,20,20),(25,25,25);

  1、不等号条件里的等值查询

  begin;
  select * from t where id>9 and id<12 order by id desc for update;

  利用上面的加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]和(10,15)。加锁单位是next-key lock，这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁(10,15)

  

  1.首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个id

  2.这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙

  3.然后根据order by id desc，再向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]

  在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法

  2、等值查询的过程

  begin;
  select id from t where c in(5,20,10) lock in share mode;

  
  这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的

  在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。执行c=10会这个逻辑的时候，加锁的范围是(5,10]和(10,15)，执行c=20这个逻辑的时候，加锁的范围是(15,20]和(20,25)

  这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁

  select id from t where c in(5,20,10) order by c desc for update;

  由于语句里面是order by c desc，这三个记录锁的加锁顺序是先锁c=20，然后c=10，最后是c=5。这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁

  八、insert语句的锁为什么这么多？

  1、insert … select语句

  表t和t2的表结构、初始化数据语句如下：

  CREATE TABLE `t` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `c` (`c`)
  ) ENGINE=InnoDB;
  
  insert into t values(null, 1,1);
  insert into t values(null, 2,2);
  insert into t values(null, 3,3);
  insert into t values(null, 4,4);
  
  create table t2 like t;

  在可重复读隔离级别下，binlog_format=statement时执行下面这个语句时，需要对表t的所有行和间隙加锁

  insert into t2(c,d) select c,d from t;

  2、insert循环写入

  要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1，SQL语句如下：

  insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

  这个语句的加锁范围，就是表t索引c上的(3,4]和(4,supermum]这两个next-key lock，以及主键索引上id=4这一行

  执行流程是从表t中按照索引c倒序吗，扫描第一行，拿到结果写入到表t2中，因此整条语句的扫描行数是1

  但如果要把这一行的数据插入到表t中的话：

  insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

  
  explain结果中的Extra字段中Using temporary字段，表示这个语句用到了临时表

  执行流程如下：

  1.创建临时表，表里有两个字段c和d

  2.按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表

  3.由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中

  这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据

  需要临时表是因为这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符

  3、insert唯一键冲突

  
  sessionA执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁，sessionA持有索引c上的(5,10]共享next-key lock（读锁）

  
  在sessionA执行rollback语句回滚的时候，sessionC几乎同时发现死锁并返回

  1.在T1时刻，启动sessionA，并执行insert语句，此时在索引c的c=5上加了记录锁。这个索引是唯一索引，因此退化为记录锁

  2.在T2时刻，sessionA回滚。这时候，sessionB和sessionC都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁

  

  4、insert into … on duplicate key update

  上面这个例子是主键冲突后直接报错，如果改写成

  insert into t values(11,10,10) on duplicate key update d=100;

  就会给索引c上(5,10]加一个排他的next-key lock（写锁）

  insert into … on duplicate key update的语义逻辑是，插入一行数据，如果碰到唯一键约束，就继续执行后面的更新语句。如果有多个列违反了唯一性索引，就会按照索引的顺序，修改跟第一个索引冲突的行

  表t里面已经有了(1,1,1)和(2,2,2)这两行，执行这个语句效果如下：

  
  主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行

  思考题：

  1、如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：

  • 第一种，直接执行delete from T limit 10000;
  • 第二种，在一个连接中循环执行20次delete from T limit 500；
  • 第三种，在20个连接中同时执行delete from T limit 500；

  选择哪一种方式比较好？

  参考答案：

  第一种方式，单个语句占用时间长，锁的时间也比较长，而且大事务还会导致主从延迟

  第三种方式，会人为造成锁冲突

  第二种方式相对较好

  更多编程相关知识，请访问：编程入门！！

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!