Sharing the full record of a deadlock troubleshooting process in Mysql-Mysql Tutorial-php.cn

When testing the concurrent sending of coupons to users in the test environment, a deadlock occurred. This was solved by searching for relevant information, so I wanted to summarize it, so the following article is mainly about a MysqlThe complete record of the deadlock investigation process, friends who need it can refer to it, I hope you can find it helpful.

Preface

The database deadlocks I encountered before were all deadlocks caused by inconsistent locking sequences during batch updates, but the above Zhou encountered a deadlock that was difficult to understand. I took this opportunity to re-learn mysql deadlock knowledge and common deadlock scenarios. After multiple investigations and discussions with colleagues, I finally discovered the cause of this deadlock problem, and gained a lot. Although we are back-end programmers, we do not need to analyze lock-related source code as deeply as DBAs, but if we can master basic deadlock troubleshooting methods, it will be of great benefit to our daily development.

PS: This article will not introduce the basic knowledge of deadlock. For the locking principle of mysql, you can refer to the link provided in the reference material of this article.

Cause of deadlock

First introduce the database and table situation, because it involves real data within the company, so the following has been simulated and will not affect the specific analyze.

We are using the 5.5 version of mysql database, the transaction isolation level is the default RR (Repeatable-Read), and the innodb engine is used. Assume that there is a test table:

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;

Copy after login

The structure of the table is very simple, with a primary key id and another unique indexa. The data in the table is as follows:

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

Copy after login

The operation where deadlock occurs is as follows:

Steps	Transaction 1	Transaction 2
1		begin
2		delete from test where a = 2;
3	begin
4	delete from test where a = 2; (Transaction 1 is stuck)
5	Prompt for deadlock: 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)

Copy after login

分析

阅读死锁日志

遇到死锁，第一步就是阅读死锁日志。死锁日志通常分为两部分，上半部分说明了事务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 ;;

Copy after login

从日志里我们可以看到事务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 ;;

Copy after login

从日志的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

During the process of troubleshooting deadlocks, a colleague also discovered that the above scenario would produce another kind of deadlock, which cannot be reproduced manually. , it is only possible to reproduce in high concurrency scenarios.

The log corresponding to the deadlock will not be posted here. The core difference from the previous deadlock is that the lock waiting for transaction 2 has been changed from S lock to X lock, that is, lock_mode X locks gap before rec insert intention waiting.

We still use the table to explain in detail the process of deadlock generation:

##4[insert phase 1]insert into test (id, a) values (10, 2); Transaction 2 applies for S lock for duplicate key checking. Check successful. 5delete from test where a = 2; Transaction 1 hopes to apply for an X lock under a=2, but transaction 2 has already applied for an X lock , the two X locks are mutually exclusive, so the X lock application enters the lock request queue. 6A deadlock occurs, and transaction 1 has a smaller weight, so it is selected to be rolled back (becomes a victim). 【insert phase 2】insert into test (id, a) values (10, 2); Transaction 2 starts

Steps	Transaction 1	Transaction 2
1		begin
2		delete from test where a = 2; The execution is successful. Transaction 2 occupies the X lock under a=2, which is a record lock.
3	begin


		Insert data, S lock is upgraded to X lock, type is insert intention . In the same way, the X lock enters the queue, forming a loop of waiting, and a deadlock occurs.

Summary

When troubleshooting deadlocks, you first need to analyze the cyclic waiting scenario based on the deadlock log, and then based on The SQL currently executed by each transaction analyzes the locking type and order, and reversely infers how to form a loop wait, so that the cause of the deadlock can be found.

The above is the detailed content of Sharing the full record of a deadlock troubleshooting process in Mysql. For more information, please follow other related articles on the PHP Chinese website!