Comment résoudre le problème de blocage de MySQL (exemples détaillés)

Cet article vous apporte des connaissances pertinentes sur mysql Il présente principalement l'analyse et la discussion des cas de blocage courants, et donne quelques suggestions sur la façon d'éviter les blocages autant que possible. J'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel mysql

1. Qu'est-ce qu'un blocage

Le blocage est un problème courant dans les systèmes concurrents, et il apparaîtra également dans le scénario de requêtes de lecture et d'écriture simultanées dans la base de données MySQL. Un « blocage » se produit lorsque deux transactions ou plus s'attendent pour libérer les verrous qu'elles détiennent déjà ou attendent des ressources de verrouillage dans une boucle en raison de séquences de verrouillage incohérentes. Le message d'erreur courant est Deadlock trouvé lors de la tentative d'obtention du verrou.... Deadlock found when trying to get lock...。

举例来说 A 事务持有 X1 锁 ，申请 X2 锁，B事务持有 X2 锁，申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。

如上图，是右侧的四辆汽车资源请求产生了回路现象，即死循环，导致了死锁。

从死锁的定义来看，MySQL 出现死锁的几个要素为：

1. 两个或者两个以上事务

2. 每个事务都已经持有锁并且申请新的锁

3. 锁资源同时只能被同一个事务持有或者不兼容

4. 事务之间因为持有锁和申请锁导致彼此循环等待

2、InnoDB 锁类型

为了分析死锁，我们有必要对 InnoDB 的锁类型有一个了解。

MySQL InnoDB 引擎实现了标准的行级别锁：共享锁( S lock ) 和排他锁 ( X lock )

1. 不同事务可以同时对同一行记录加 S 锁。

2. 如果一个事务对某一行记录加 X 锁，其他事务就不能加 S 锁或者 X 锁，从而导致锁等待。

如果事务 T1 持有行 r 的 S 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理:

1. T2 请求 S 锁立即被允许，结果 T1 T2 都持有 r 行的 S 锁

2. T2 请求 X 锁不能被立即允许

如果 T1 持有 r 的 X 锁，那么 T2 请求 r 的 X、S 锁都不能被立即允许，T2 必须等待 T1 释放 X 锁才可以，因为 X 锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示：

2.1、间隙锁( gap lock ) 

间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值，如果对 4 加锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是，间隙锁有个例外:

1. 如果索引列是唯一索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。

2. 对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁。

2.2、next-key lock

next-key lock 实际上就是 行锁+这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:

(负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)

在 RR 隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读

Par exemple, la transaction A détient le verrou X1 et demande le verrou X2, la transaction B détient le verrou X2 et demande le verrou X1. Les transactions A et B détiennent des verrous, demandent les verrous détenus par l'autre partie et attendent en boucle, provoquant une impasse.

Comme le montre l'image ci-dessus, il est le quatre à droite. La demande de ressources du véhicule a créé un phénomène de boucle, c'est-à-dire une boucle infinie, entraînant une impasse.

D'après la définition du blocage, plusieurs facteurs de blocage dans MySQL sont :
Deux transactions ou plus
Chaque transaction détient déjà un verrou et demande un nouveau verrou

Les ressources de verrouillage ne peuvent être détenues que par la même transaction en même temps ou sont incompatibles

🎜Les transactions s'attendent en boucle en raison du maintien des verrous et de la demande de verrous🎜🎜🎜🎜2 Types de verrous InnoDB🎜🎜Afin d'analyser les blocages, c'est le cas. nécessaire pour que nous comprenions les types de verrous d'InnoDB. 🎜🎜🎜🎜Le moteur MySQL InnoDB implémente le Verrous au niveau de la ligne : verrouillage partagé (verrouillage S) et verrouillage exclusif (verrouillage X)🎜🎜🎜🎜🎜 Différentes transactions peuvent ajouter un verrouillage S au même enregistrement de ligne en même temps. 🎜🎜🎜🎜Si une transaction ajoute un verrou X à une certaine ligne d'enregistrements, les autres transactions ne peuvent pas ajouter un verrou S ou un verrou X, ce qui entraîne une attente de verrouillage. 🎜🎜🎜🎜🎜Si la transaction T1 détient le verrou S de la ligne r, alors lorsqu'une autre transaction T2 demande le verrou de r, le traitement suivant sera effectué : 🎜🎜🎜🎜🎜T2 demande le verrou S immédiatement et est autorisé As. par conséquent, T1 et T2 sont maintenus. Il y a un verrou S pour r lignes🎜🎜🎜🎜La demande de T2 pour un verrou X ne peut pas être accordée immédiatement🎜🎜🎜🎜🎜Si T1 détient un verrou X, T1 ne peut libérer que le verrou X, car le X le verrou est incompatible avec n’importe quel verrou. La compatibilité des verrous partagés et des verrous exclusifs est la suivante : 🎜🎜🎜🎜2.1. Gap lock (gap lock) 🎜🎜Gap lock verrouille un espace pour empêcher l'insertion. Supposons que la colonne d'index ait trois valeurs 2, 4 et 8. Si 4 est verrouillé, les deux espaces (2,4) et (4,8) seront également verrouillés en même temps. Les autres transactions ne peuvent pas insérer d'enregistrements avec des valeurs d'index entre ces deux espaces. Cependant, il existe une exception au verrouillage des espaces : 🎜🎜🎜🎜🎜Si la colonne d'index est un index unique, alors seul cet enregistrement sera verrouillé (seuls les verrous de lignes seront ajoutés) et l'espace ne sera pas verrouillé. 🎜🎜🎜🎜Pour un index conjoint et qu'il s'agit d'un index unique, si la condition Where n'inclut qu'une partie de l'index conjoint, des verrous d'espacement seront quand même ajoutés. 🎜🎜🎜🎜🎜2.2, verrouillage à clé suivante🎜🎜Le verrouillage à clé suivante est en fait une combinaison de verrouillage de rangée + verrouillage d'espace devant cet enregistrement. En supposant qu'il existe des valeurs d'index 10, 11, 13 et 20, les verrous de clé suivants possibles incluent : 🎜🎜🎜(infini négatif, 10], (10, 11], (11, 13], (13, 20], ( 20, infini positif)🎜🎜🎜Sous le niveau d'isolement RR, InnoDB utilise le verrouillage de la touche suivante principalement pour éviter les problèmes de lecture fantôme 🎜🎜2.3, Verrouillage d'intention (Verrouillage d'intention)🎜🎜. Prise en charge d'InnoDB. Le verrouillage multi-granularité permet aux verrous de ligne et de table d'exister en même temps. Afin de prendre en charge les opérations de verrouillage à différentes granularités, InnoDB prend en charge une méthode de verrouillage supplémentaire appelée verrouillage d'intention. Les objets verrouillés sont divisés en plusieurs niveaux. signifie que les transactions veulent se verrouiller avec une granularité plus fine. Les verrous d'intention sont divisés en deux types : 🎜🎜🎜🎜🎜Verrouillage partagé d'intention (IS) : les transactions visent à verrouiller des objets dans la table. 🎜Verrous exclusifs intentionnels (IX) : La transaction a l'intention d'ajouter des verrous exclusifs à certaines lignes du tableau🎜🎜🎜🎜🎜Étant donné que le moteur de stockage InnoDB prend en charge les verrous au niveau des lignes, les verrous d'intention En fait, il ne bloquera aucune requête sauf complète analyses de table. La compatibilité des verrous d'intention au niveau de la table et des verrous au niveau des lignes est la suivante : 🎜🎜🎜🎜

2.4. Verrou d'intention d'insertion (Verrou d'intention d'insertion)

Le verrou d'intention d'insertion est un verrou d'espace défini avant l'insertion d'une ligne d'enregistrements. Ce verrou libère un signal de mode d'insertion, c'est-à-dire que plusieurs transactions se trouvent dans le même espace d'index lors de l'insertion. , s'ils ne sont pas insérés dans la même position dans l'espace, il n'est pas nécessaire de s'attendre. Supposons qu'une colonne ait les valeurs d'index 2 et 6. Tant que les positions d'insertion des deux transactions sont différentes (par exemple, la transaction A insère 3 et la transaction B insère 4), elles peuvent alors être insérées en même temps.

2.5. Matrice de compatibilité du mode de verrouillage

Horizontalement, le verrou est maintenu et verticalement, le verrou demandé :

3. Lisez le journal des blocages

Avant d'analyser le cas spécifique, comprenons d'abord comment le faire. Lisez le journal des blocages et utilisez autant que possible les informations contenues dans le journal des blocages pour nous aider à résoudre le problème de blocage.

Le scénario de base de données du scénario de test suivant est le suivant : Le niveau d'isolation des transactions MySQL 5.7 est RRMySQL 5.7 事务隔离级别为 RR

表结构和数据如下:

测试用例如下:

 通过执行show engine innodb status 可以查看到最近一次死锁的日志。

3.1、日志分析如下:

1.***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

 事务号为2322，活跃 6秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态有:

mysql tables in use 1 说明当前的事务使用一个表。

locked 1 表示表上有一个表锁，对于 DML 语句为 LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

LOCK WAIT 表示正在等待锁，2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock)

1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )

delete from student where stuno=5 表示事务1正在执行的 sql，比较难受的事情是 show engine innodb status 是查看不到完整的 sql 的，通常显示当前正在等待锁的 sql。

 ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS 表示记录锁， 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁，本案例中其实是 Next-Key Lock 。

事务2的 log 和上面分析类似:

2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X

 LOCK_gap，不过我们从日志里面看不到事务2执行的 delete from student where stuno=5;

这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。

3.***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )

4、经典案例分析

4.1、事务并发 insert 唯一键冲突

表结构和数据如下所示:

测试用例如下:

 日志分析如下:

1. 事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功，持有 a=10 的 排他行锁( Xlocks rec but no gap )

2. 事务 T1 insert into t7(id,a) values (30,10), 因为T2的第一条 insert 已经插入 a=10 的记录,事务 T1 insert a=10 则发生唯一键冲突,需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 这是一个间隙锁

   La structure de la table et les données sont les suivantes : 🎜🎜🎜🎜Les exemples de tests sont les suivants :🎜🎜🎜🎜 Vous pouvez afficher le journal du dernier blocage en exécutant show engine innodb status. 🎜🎜3.1. L'analyse du journal est la suivante : 🎜🎜1.***** (1) TRANSACTION : TRANSACTION 2322, ACTIVE 6 secondes de lecture de l'index🎜🎜 Le numéro de transaction est 2322, actif pendant 6 secondes, lecture de l'index de départ indique que l'état de la transaction est basé sur les données de lecture d'index. Les autres statuts courants sont : 🎜🎜🎜🎜 tables mysql utilisées 1 indique que la transaction en cours utilise une table. 🎜🎜locked 1 signifie qu'il y a un verrou de table sur la table, pour les instructions DML, c'est LOCK_IX🎜rrreee🎜LOCK WAIT signifie attendre le verrou, 2 lock struct(s) Indique que la longueur de la liste de verrouillage trx->trx_locks est de 2. Chaque nœud de liste chaînée représente une structure de verrouillage détenue par la transaction, y compris les verrous de table, les verrous d'enregistrement et les verrous à incrémentation automatique. Dans ce cas d'utilisation, 2locks représente les verrous IX et lock_mode X (Next-key lock)🎜🎜1 verrou(s) de ligne représente le nombre de verrous d'enregistrement de ligne/verrous d'espacement détenus par la transaction en cours. 🎜rrreee🎜ID de thread MySQL 37 signifie que l'ID de thread qui exécute la transaction est 37 (c'est-à-dire afficher la liste de processus ; l'ID affiché) 🎜🎜supprimer de l'étudiant où Stuno=5 signifie la transaction 1. La chose inconfortable à propos de l'exécution du SQL est que show engine innodb status ne peut pas afficher le SQL complet. Il affiche généralement le SQL en attente de verrouillage. 🎜rrreee🎜RECORD LOCKS représente des verrous d'enregistrement. Ce contenu indique que la transaction 1 attend le verrou X de idx_stuno sur la table student. Dans ce cas, il s'agit en fait de Next-Key Lock. 🎜🎜Le journal de la transaction 2 est similaire à l'analyse ci-dessus : 🎜rrreee🎜montre que l'insertion dans les valeurs étudiantes (stuno,score) (2,10) de la transaction 2 contient un mode de verrouillage = 5 X🎜

   🎜 LOCK_gap , mais nous ne pouvons pas voir la suppression de student où stuno=5;🎜

   🎜 exécutée par la transaction 2 à partir du journal. C'est aussi la raison fondamentale pour laquelle il est difficile pour le DBA d'analyser le problème de blocage uniquement sur la base du fichier. enregistrer. 🎜🎜3.***** (2) EN ATTENTE QUE CE VERROU SOIT ACCORDÉ :🎜🎜ENREGISTREMENT DES VERROUS espace id 11 page no 5 n bits 72 index idx_stuno de la table cw****.****étudiant trx id 2321 lock_mode insérer un conflit de clé unique🎜🎜La structure et les données de la table sont les suivantes :🎜🎜🎜🎜🎜🎜Le les exemples de tests sont les suivants : 🎜🎜🎜🎜 Le journal L'analyse est la suivante :🎜
   1. 🎜Transaction T2 insérée dans les valeurs t7(id,a) (26,10) l'insertion de l'instruction est réussie, en maintenant a=10 Verrouillage de ligne exclusif (Xlocks rec mais pas d'espace)🎜 li>
   2. 🎜Transaction T1 insérée dans les valeurs t7(id,a) (30,10), car la première insertion de T2 a déjà inséré un enregistrement = 10, transaction T1 insert a=10 provoquera un conflit de clé unique. Vous devez demander l'index unique du conflit plus S Next-key Lock (c'est-à-dire le mode de verrouillage S en attente). Il s'agit d'un gap lock). > qui s'appliquera pour un verrou entre (,10], (10,20] zone d'écart. 🎜

   3. Transaction T2 insérée dans les valeurs t7(id,a)​​(40, 9) La valeur de a=9 insérée par cette instruction se situe entre le gap lock 4-10 demandé par la transaction T1, il est donc requis. La deuxième instruction d'insertion de la transaction T2 doit attendre que le S-Next-key Lock de la transaction T1 soit libéré, et lock_mode X verrouille l'espace avant que l'intention d'insertion de rec attende est affiché dans le journal. gap 锁4-10之间， 故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。

    4.2、先 update 再 insert 的并发死锁问题

    表结构如下，无数据:

   

   测试用例如下:

   

    死锁分析:
   可以看到两个事务 update 不存在的记录，先后获得间隙锁( gap 锁)，gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。

   5、如何尽可能避免死锁

   1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。

   2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。

   3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。

   4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。

   5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。

   6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。

   7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解

   4.2. Problème de blocage de concurrence lors de la mise à jour d'abord, puis de l'insertion

   La structure du tableau est la suivante, aucune donnée :

   L'exemple de test est le suivant :

   🎜🎜 Analyse des blocages :
   Oui En voyant deux enregistrements inexistants dans la mise à jour de la transaction, nous avons obtenu le gap lock (gap lock) l'un après l'autre. Les gap locks sont compatibles, ils ne se bloqueront donc pas pendant le processus de mise à jour. . Les deux détiennent des verrous d'espacement, puis rivalisent pour insérer des verrous d'intention. Lorsque d'autres sessions détiennent des verrous d'espacement, la session en cours ne peut pas demander le verrou d'intention d'insertion, ce qui entraîne un blocage. 🎜

   5. Comment éviter les blocages autant que possible

   🎜🎜Concevez l'index de manière raisonnable et placez les colonnes avec une haute distinction devant l'index combiné, afin que le SQL métier puisse être positionné comme moins que possible via les lignes d'index pour réduire les conflits de verrouillage. 🎜🎜🎜🎜Ajustez l'ordre d'exécution de la logique métier SQL pour éviter de mettre à jour/supprimer SQL qui détient des verrous pendant une longue période avant la transaction. 🎜🎜🎜🎜Évitez les Transactions volumineuses et essayez de diviser les transactions volumineuses en plusieurs petites transactions pour le traitement. La probabilité de conflits de verrouillage dans les petites transactions est également plus faible. 🎜🎜🎜🎜Accédez aux tables et aux lignes dans ordre fixe. Par exemple, pour deux transactions qui mettent à jour des données, la transaction A met à jour les données dans l'ordre 1, 2 ; la transaction B met à jour les données dans l'ordre 2, 1. Cela est plus susceptible de provoquer une impasse. 🎜🎜🎜🎜Dans les systèmes à concurrence relativement élevée, ne verrouillez pas explicitement, en particulier dans les transactions. Par exemple, si l'instruction select... for update se trouve dans une transaction (la transaction de démarrage est exécutée ou la validation automatique est définie sur 0), alors l'enregistrement trouvé sera verrouillé. 🎜🎜🎜🎜Essayez de rechercher des enregistrements par clé primaire/index. La recherche par plage augmente le risque de conflits de verrouillage. N'utilisez pas la base de données pour effectuer des calculs de quota supplémentaires. Par exemple, certains programmes utiliseront des instructions telles que "select ...where ... order by rand();". Puisque des instructions comme celle-ci n'utilisent pas d'index, les données de la table entière seront verrouillées. 🎜🎜🎜🎜Optimisez la conception SQL et des tables pour réduire la situation d'occupation trop de ressources en même temps. Par exemple, réduisez le nombre de tables connectées et décomposez le SQL complexe en plusieurs SQL simples. 🎜🎜🎜🎜Apprentissage recommandé : 🎜Tutoriel d'apprentissage 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!