Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to troubleshoot deadlocks in MySQL. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Cause of deadlock

First introduce the database and tables, because it involves the real data within the company, so the following are simulated, will not affect the specific analysis.

We use version 5.5 of the mysql database, the transaction isolation level is the default RR (Repeatable-Read), and uses the innodb engine. Suppose 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

The structure of the table is simple, with one primary key id and another * index a. The data in the table are as follows:

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

The actions that cause a deadlock are as follows:

Step transaction 1 transaction 21begin2delete from test where a = 2 transaction 3begin4delete from test where a = 2; (transaction 1 stuck) 5 prompts deadlock: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactioninsert into test (id, a) values (10, 2)

Then we can view the deadlock log through 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 923n 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 320: 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 page no 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 320: 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 320: len 4; hex 00000002; asc; 1: len 4; hex 000002; asc * WE ROLL BACK TRANSACTION (1)

Analysis.

Read the deadlock log

When you encounter a deadlock, the step is to read the deadlock log. The deadlock log is usually divided into two parts, and the top half shows what lock transaction 1 is waiting for:

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 923n 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 320: len 4; hex 00000002; asc; 1: len 4; hex 00000002; asc

From the log, we can see that transaction 1 is currently executing delete from test where a = 2, which is applying for an X lock on index a, so prompt lock_mode X waiting.

Then the bottom half of the log describes the locks currently held by transaction 2 and the locks that are waiting:

* * (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 page no 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 320: 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 320: len 4; hex 00000002; asc; 1: len 4; hex 000002; asc

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

From the WAITING FOR THIS LOCK TO BE GRANTED block of the log, we can see that transaction 2 is applying for an S lock, that is, a shared lock. The lock is requested by the insert into test (id,a) values (10jin2) statement. The insert statement normally applies for an exclusive lock, that is, an X lock, but there is an S lock. This is because the a field is a * index, so the insert statement performs a duplicate key check before insertion. In order to make this check successful, you need to apply for an S lock to prevent other transactions from modifying the a field.

So why did the S lock fail? This is the lock application for the same field that needs to be queued. There is an unsuccessfully applied X lock in front of the S lock, so the S lock must wait, so a loop wait is formed and the deadlock occurs.

By reading the deadlock log, we can clearly know what kind of loop wait the two transactions have formed, and then analyze it, we can infer the cause of the loop wait, that is, the cause of the deadlock.

Deadlock formation flow chart

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

Step 1 transaction 1 transaction 21begin2delete from test where axiom 2; executed successfully, transaction 2 occupies the X lock under axiom 2, and the type is record lock. Transaction 1 wants to apply for an X lock under 3begin4delete from test where 2, but since transaction 2 has applied for an X lock and the two X locks are mutually exclusive, the X lock application enters the lock request queue. 5 has a deadlock, transaction 1 has a small weight, so it is chosen to roll back (become a victim). Insert into test (id, a) values (10,2); because the a field has an index of *, you need to apply for an S lock to check the duplicate key, and since the value of the inserted an is still 2, it comes after the X lock. But the previous application for the X lock can only be successful after the transaction 2commit or rollback, where a loop wait is formed and a deadlock occurs.

Development

In the process of troubleshooting deadlocks, a colleague also found that the above scenario will produce another kind of deadlock, which cannot be reproduced manually, but only in high concurrency scenarios.

The log corresponding to the deadlock is not 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. Let's use a table to detail the process of generating the deadlock:

Step 1 transaction 1 transaction 21begin2delete from test where axiom 2; executed successfully, transaction 2 occupies the X lock under axiom 2, and the type is record lock. 3begin4 [insert Phase 1] insert into test (id, a) values (10, 2); transaction 2 applies for an S lock for duplicate key to check. Check was successful. Transaction 1 wants to apply for an X lock under 5delete from test where 2, but since transaction 2 has applied for an X lock and the two X locks are mutually exclusive, the X lock application enters the lock request queue. There is a deadlock in 6, and transaction 1 has less weight, so it is chosen to roll back (become a victim). [insert phase 2] insert into test (id, a) values (10, 2); transaction 2 starts inserting data, and S lock is upgraded to X lock with type insert intention. Similarly, the X lock enters the queue and forms a loop waiting, resulting in a deadlock. About how to troubleshoot deadlocks in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.