Partager l'enregistrement complet d'un processus de dépannage de blocage dans Mysql

Lors du test de l'envoi simultané de coupons aux utilisateurs dans l'environnement de test, un blocage s'est produit. J'ai résolu ce problème en recherchant des informations pertinentes, j'ai donc pensé à le résumer, donc l'article suivant concerne principalement un Mysql. Enregistrement complet du processus de dépannage des impasses. Les amis qui en ont besoin peuvent s'y référer. J'espère que vous pourrez le trouver utile.

Avant-propos

Les blocages de bases de données que j'ai rencontrés auparavant étaient tous des blocages causés par un ordre de verrouillage incohérent lors des mises à jour par lots, mais le Zhou ci-dessus a rencontré une impasse c'était difficile à comprendre. J'ai profité de cette occasion pour réapprendre les connaissances sur les blocages MySQL et les scénarios de blocage courants. Après de multiples enquêtes et discussions avec des collègues, j'ai finalement découvert la cause de ce problème de blocage et j'ai beaucoup gagné. Bien que nous soyons des programmeurs back-end, nous n'avons pas besoin d'analyser le code source lié au verrouillage aussi profondément que les administrateurs de base de données, mais si nous pouvons maîtriser les méthodes de base de dépannage des blocages, cela sera d'un grand bénéfice pour notre développement quotidien.

PS : Cet article ne présentera pas les connaissances de base du blocage. Pour le principe de verrouillage de MySQL, vous pouvez vous référer au lien fourni dans le matériel de référence de cet article.

Cause de l'impasse

Présentez d'abord la situation de la base de données et de la table. Parce qu'il s'agit de données réelles au sein de l'entreprise, ce qui suit a été simulé et n'affectera pas l'analyse spécifique. .

Nous utilisons la version 5.5 de la base de données mysql Le niveau d'isolation des transactions est le RR par défaut (Repeatable-Read) et utilise le moteur innodb. Supposons qu'il existe une table de test :

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

La structure de la table est très simple, avec un identifiant de clé primaire et un autre index a unique. Les données du tableau sont les suivantes :

mysql> select * from test;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
3 rows in set (0.00 sec)

L'opération qui provoque le blocage est la suivante :

步骤	事务1	事务2
1		begin
2		delete from test where a = 2;
3	begin
4	delete from test where a = 2; （事务1卡住）
5	提示出现死锁：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	insert into test (id, a) values (10, 2);

然后我们可以通过SHOW ENGINE INNODB STATUS;来查看死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)

分析

阅读死锁日志

遇到死锁，第一步就是阅读死锁日志。死锁日志通常分为两部分，上半部分说明了事务1在等待什么锁：

170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

从日志里我们可以看到事务1当前正在执行delete from test where a = 2，该条语句正在申请索引a的X锁，所以提示lock_mode X waiting。

然后日志的下半部分说明了事务2当前持有的锁以及等待的锁：

*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

从日志的HOLDS THE LOCKS(S)块中我们可以看到事务2持有索引a的X锁，并且是记录锁（Record Lock）。该锁是通过事务2在步骤2执行的delete语句申请的。由于是RR隔离模式下的基于唯一索引的等值查询（Where a = 2），所以会申请一个记录锁，而非next-key锁。

从日志的WAITING FOR THIS LOCK TO BE GRANTED块中我们可以看到事务2正在申请S锁，也就是共享锁。该锁是insert into test (id,a) values (10,2)语句申请的。insert语句在普通情况下是会申请排他锁，也就是X锁，但是这里出现了S锁。这是因为a字段是一个唯一索引，所以insert语句会在插入前进行一次duplicate key的检查，为了使这次检查成功，需要申请S锁防止其他事务对a字段进行修改。

那么为什么该S锁会失败呢？这是对同一个字段的锁的申请是需要排队的。S锁前面还有一个未申请成功的X锁，所以S锁必须等待，所以形成了循环等待，死锁出现了。

通过阅读死锁日志，我们可以清楚地知道两个事务形成了怎样的循环等待，再加以分析，就可以逆向推断出循环等待的成因，也就是死锁形成的原因。

死锁形成流程图

为了让大家更好地理解死锁形成的原因，我们再通过表格的形式阐述死锁形成的流程：

步骤	事务1	事务2
1		begin
2		delete from test where a = 2; 执行成功，事务2占有a=2下的X锁，类型为记录锁。
3	begin
4	delete from test where a = 2; 事务1希望申请a=2下的X锁，但是由于事务2已经申请了一把X锁，两把X锁互斥，所以X锁申请进入锁请求队列。
5	出现死锁，事务1权重较小，所以被选择回滚（成为牺牲品）。	insert into test (id, a) values (10, 2); 由于a字段建立了唯一索引，所以需要申请S锁以便检查duplicate key，由于插入的a的值还是2，所以排在X锁后面。但是前面的X锁的申请只有在事务2commit或者rollback之后才能成功，此时形成了循环等待，死锁产生。

Expansion

Au cours du processus de dépannage des blocages, un collègue a également découvert que le scénario ci-dessus produirait un autre type de blocage, qui ne peut pas être reproduit manuellement, c'est-à-dire. uniquement possible à reproduire dans des scénarios à forte concurrence.

Le journal correspondant au blocage ne sera pas publié ici. La principale différence par rapport au blocage précédent est que le verrou attendu par la transaction 2 passe du verrou S au verrou X, qui est lock_mode X locks gap <a href="http://www.php.cn/java/java-Before.html" target="_blank">before</a> rec insert intention waiting.

Nous utilisons toujours un tableau pour expliquer en détail le processus de génération de blocage :

Étapes

Transaction 1

Transaction 2

1

步骤	事务1	事务2
1		begin
2		delete from test where a = 2; 执行成功，事务2占有a=2下的X锁，类型为记录锁。
3	begin
4		【insert第1阶段】insert into test (id, a) values (10, 2); 事务2申请S锁进行duplicate key进行检查。检查成功。
5	delete from test where a = 2; 事务1希望申请a=2下的X锁，但是由于事务2已经申请了一把X锁，两把X锁互斥，所以X锁申请进入锁请求队列。
6	出现死锁，事务1权重较小，所以被选择回滚（成为牺牲品）。	【insert第2阶段】insert into test (id, a) values (10, 2); 事务2开始插入数据，S锁升级为X锁，类型为insert intention。同理，X锁进入队列排队，形成循环等待，死锁产生。

début

2

supprimer du test où a = 2 l'exécution est réussie, la transaction 2 occupe a=2 ; Verrou X, le type est verrouillage d'enregistrement.

3

commencer

4 td>

[insérer la phase 1] insérer dans le test (id, a) les valeurs (10, 2) ; la transaction 2 s'applique au verrouillage S pour la vérification des clés en double. Vérification réussie.

5

supprimer du test où a = 2 ; la transaction 1 espère demander le verrou X sous a=2, mais comme la transaction 2 l'a déjà fait appliquée pour un verrou X, les deux verrous X s'excluent mutuellement, de sorte que l'application de verrouillage X entre dans la file d'attente des demandes de verrouillage.

6

Un blocage se produit et la transaction 1 a un poids plus petit, elle est donc sélectionnée pour être annulé (devient une victime) ).

[insérer la phase 2]insérer dans le test (id, a) les valeurs (10, 2 démarrentInsérer des données, le verrou S est mis à niveau vers le verrou X, le type est l'intention d'insertion. De la même manière, le verrou X entre dans la file d'attente, formant une boucle d'attente, et un blocage se produit.

RésuméLors du dépannage d'un blocage, vous devez d'abord analyser l'attente de la boucle en fonction de le scénario du journal de blocage, puis analysez le type et la séquence de verrouillage en fonction du SQL actuellement exécuté par chaque transaction, et déduisez inversement comment former une boucle d'attente, afin que la cause du blocage puisse être trouvée.

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!