Full records of Mysql deadlock troubleshooting and mysql lock troubleshooting

Full records of Mysql deadlock troubleshooting and mysql lock troubleshooting

Preface

Previously, database deadlocks were caused by inconsistent lock sequence during batch update, but encountered a hard-to-understand deadlock last week. With this opportunity, I learned about mysql deadlocks and common deadlocks. I found out the cause of the deadlock through multiple surveys and discussions with my colleagues, and gained a lot. Although we are backend programmers, we do not need to analyze the lock-related source code in depth like DBA, but if we can master the basic deadlock troubleshooting methods, it is of great benefit to our daily development.

PS: This article will not introduce the basic knowledge of deadlocks. For the Locking Principle of mysql, refer to the links provided by references in this article.

Cause of deadlock

First, we will introduce the database and table information, because it involves the real data in the company, so the following simulation will not affect the specific analysis.

We use the mysql database of Version 5.5, the transaction isolation level is the default RR (Repeatable-Read), and the innodb engine is used. Assume that the test table exists:

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;

The table structure is very simple. One primary key id and the other unique index. 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)

The deadlock occurs as follows:

Procedure	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	The system prompts a Deadlock: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.	Insert into test (id, a) values (10, 2 );

Then we can useSHOW ENGINE INNODB STATUS;To view the deadlock log:

------------------------LATEST DETECTED DEADLOCK------------------------170219 13:31:31*** (1) TRANSACTION:TRANSACTION 2A8BD, ACTIVE 11 sec starting index readmysql tables in use 1, locked 1LOCK 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 updatingdelete 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 waitingRecord 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 insertingmysql tables in use 1, locked 14 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root updateinsert 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 gapRecord 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 waitingRecord 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)

Analysis

Read deadlock logs

When a deadlock occurs, the first step is to read the deadlock log. Deadlock logs are usually divided into two parts. The upper part shows what locks transaction 1 is waiting:

170219 13:31:31*** (1) TRANSACTION:TRANSACTION 2A8BD, ACTIVE 11 sec starting index readmysql tables in use 1, locked 1LOCK 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 updatingdelete 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 waitingRecord 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 ;;

From the log, we can see that transaction 1 is currently being executed.delete from test where a = 2The statement is applying for the X lock of index.lock_mode X waiting.

Then the lower part of the log shows the lock currently held by transaction 2 and the lock waiting for it:

*** (2) TRANSACTION:TRANSACTION 2A8BC, ACTIVE 18 sec insertingmysql tables in use 1, locked 14 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root updateinsert 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 gapRecord 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 waitingRecord 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 ;;

From the logHOLDS THE LOCKS(S)In the block, we can see that transaction 2 holds the X Lock of index a and is a Record Lock ). The lock is applied through the delete statement executed in step 2 of transaction 2. Because it is a unique index-based equi-query (Where a = 2) in RR isolation mode, a record lock is applied instead of the next-key lock.

From the logWAITING FOR THIS LOCK TO BE GRANTEDIn the block, we can see that transaction 2 is applying for the S lock, that is, the shared lock. The lock is applied by the insert into test (id, a) values () statement. In general, the insert statement applies for exclusive lock, that is, the X lock, but the S lock appears here. This is because field a is a unique index, so the insert statement will be performed once before the insert.duplicate keyTo make this check successful, apply for S lock to prevent other transactions from modifying field.

So why does the S lock fail? This is the lock application for the same field and requires queuing. There is an unapplied X lock in front of the S lock, so the S lock must wait, so a loop wait is formed, and a deadlock occurs.

By reading the deadlock log, we can clearly know what kind of cyclic waits are formed by the two transactions, and then analyze them to find out the causes of the cyclic waits, that is, the causes of the deadlock.

Deadlock formation Flowchart

In order to better understand the cause of deadlock, we will explain the process of deadlock formation in the form of a table:

Procedure	Transaction 1	Transaction 2
1		Begin
2		Delete from test where a = 2; Execution successful. Transaction 2 occupies the X lock under a = 2 and the type is record lock.
3	Begin
4	Delete from test where a = 2; transaction 1 wants to apply for the X lock under a = 2, but because transaction 2 has applied for an X lock, the two X locks are mutually exclusive, therefore, the lock X application enters the lock Request queue.
5	When a deadlock occurs, transaction 1 has a low weight and therefore is selected for rollback (as a victim ).	Insert into test (id, a) values (10, 2); because field a creates a unique index, you need to apply for the S lock to check the duplicate key, because the inserted value of a is still 2, it is placed behind the X lock. However, the application for the preceding X lock can be successful only after the transaction 2commit or rollback. At this time, a loop wait occurs and a deadlock occurs.

Expansion

During the deadlock check process, a colleague also discovered that the above scenario would generate another deadlock. This scenario cannot be reproduced manually, and can only be reproduced in high concurrency scenarios.

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

We still use the table to describe the process of the damn lock:

Procedure	Transaction 1	Transaction 2
1		Begin
2		Delete from test where a = 2; Execution successful. Transaction 2 occupies the X lock under a = 2 and the type is record lock.
3	Begin
4		[Insert 1st stage] insert into test (id, a) values (10, 2); transaction 2 applies for the S lock and checks the duplicate key. Check successful.
5	Delete from test where a = 2; transaction 1 wants to apply for the X lock under a = 2, but because transaction 2 has applied for an X lock, the two X locks are mutually exclusive, therefore, the lock X application enters the lock Request queue.
6	When a deadlock occurs, transaction 1 has a low weight and therefore is selected for rollback (as a victim ).	[Insert 2nd stage] insert into test (id, a) values (10, 2); transaction 2 starts to insert data, S lock is upgraded to X lock, and the type is insert intention. Likewise, the X lock enters the queue to form a cyclic wait, resulting in deadlocks.

Summary

When checking deadlocks, you must first analyze the loop wait scenario based on the deadlock log, and then analyze the locking type and Sequence Based on the SQL statements executed by each transaction, and infer how to form a loop wait in reverse order, in this way, we can find the cause of the deadlock.

Well, the above is all the content of this article. I hope the content of this article will help you in your study or work. The above analysis is based on experience, I hope other friends can point out the mistakes and shortcomings. Thank you for your support to the customer's house.