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

This article introduces the relevant knowledge of "detailed explanation of the case of MySQL deadlock". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A preface

Deadlock, in fact, is a very interesting and challenging technical problem, probably every DBA and some developers will encounter in the course of work. I will continue to write a series of case studies on deadlocks, hoping to be helpful to friends who want to know about deadlocks.

Two case studies

2.1 Environment description

MySQL 5.6 transaction isolation level is RR

CREATE TABLE `ty` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

`b` int (11) DEFAULT NULL

PRIMARY KEY (`id`)

KEY `idxa` (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

Insert into ty (arem b) values (2pr 3), (5pr 4), (6pr 7)

2.2 Test cases

T2

T1

Begin

Delete from ty where axi5

Begin

Delete from ty where axi5

Insert into ty (a ~ (b)) values (2 ~ (10))

Delete from ty where axi5

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.3 deadlock log

-

LATEST DETECTED DEADLOCK

-

2017-09-09 22:34:13 7f78eab82700

* * (1) TRANSACTION:

TRANSACTION 462308399, ACTIVE 33 sec starting index read

Mysql tables in use 1, locked 1

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

MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating

Delete from ty where axi5

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.ty` trx id 462308399 lock_mode X waiting

* * (2) TRANSACTION:

TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000

Mysql tables in use 1, locked 1

5 lock struct (s), heap size 1184, 4 row lock (s), undo log entries 2

MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update

Insert into ty (a ~ (b)) values (2 ~ (10))

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.ty` trx id 462308398 lock_mode X

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

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting

* WE ROLL BACK TRANSACTION (1)

2.3 analyze deadlock logs

The first thing to understand is that you need to queue up to apply a lock on the same field. S GAP in

Secondly, an in table ty is a normal index field, which is explained according to the time order in which the transaction is executed, which is easier to understand.

A shows transaction 2, that is, transactions executed by sess1, according to the deadlock log, and according to HOLDS THE LOCK (S)

Sess1 first executes delete from ty where adept 5, which holds the row lock lock_mode X of index adept 5. Because it is the RR isolation level, sess1 also holds two gap locks [1mem2]-[2mem5] and [2mem5]-[3mem6].

B the log of transaction 1, that is, the transaction executed by sess2, requests to lock APC5, one rec lock and two gap locks. Because the delete in sess1 has not been released, transaction 1 of sess2 waits for transaction 2 of sess1 to release the lock resources of APC5.

C then prompts transaction 2 insert statement to wait for lock_mode X locks gap before rec insert intention waiting according to WAITING FOR THIS LOCK TO BE GRANTED

Because the insert statement [4jue 2] is between the gap lock [1jue 2] and [2je 5], when prompted by "lock_mode X locks gap", the insert statement must wait for the delete in the previous sess2 to acquire the lock and release the lock. Therefore, sess2 (delete) waits for sess1 (delete), sess1 (insert) waits for sess2 (delete), and waits in a loop, resulting in a deadlock.

The question is if sess1 executes insert into ty (a _ r _ b) values (5p _ 10); will sess2 encounter a deadlock?

Three cases two

3.1 Index is the unique key

MySQL 5.6 transaction isolation level is RR

CREATE TABLE `t2` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

`b` int (11) DEFAULT NULL

PRIMARY KEY (`id`)

Unique KEY `idxa` (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

Insert into T2 (values b) values (2), (5) (4), (6)

3.2 Test cases

T2

T1

Begin

Delete from ty where axi5

Begin

Delete from ty where axi5

Insert into ty (a ~ (b)) values (2 ~ (10))

Delete from ty where axi5

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

3.3 deadlock log

-

LATEST DETECTED DEADLOCK

-

2017-09-10 00:03:31 7f78ea936700

* * (1) TRANSACTION:

TRANSACTION 462308445, ACTIVE 9 sec starting index read

Mysql tables in use 1, locked 1

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

MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating

Delete from T2 where astat5

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting

* * (2) TRANSACTION:

TRANSACTION 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB 5000

Mysql tables in use 1, locked 1

4 lock struct (s), heap size 1184, 3 row lock (s), undo log entries 2

MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update

Insert T2 (a meme b) values (5meme 10)

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock_mode X locks rec but not gap

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

RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting

* WE ROLL BACK TRANSACTION (1)

3.4 analyze deadlock log

First of all, we want to specifically explain the locking logic of delete.

A find the record that meets the condition, and the record is valid, then add X lock, No Gap lock (lock_mode X locks rec but not gap) to the record.

B find a record that meets the criteria, but the record is invalid (identified as a deleted record), then add a next key lock to the record (locking the record itself, as well as Gap:lock_mode X before the record)

C if no record satisfying the condition is found, a Gap lock is added to the first record that does not meet the condition to ensure that there is no record insertion that meets the condition (locks gap before rec)

Undefinedlock_mode X locks rec but not gapundefined lock mode S waitingundefinedInsert Intention Lock.

Undefined

A shows transaction 2, that is, transactions executed by sess1, according to the deadlock log, and according to HOLDS THE LOCK (S)

Sess1 first executes delete from ty where adept 5, which holds the row lock lock_mode X locks rec but not gap of index adept 5. Because an is the only key in this example, there is no gap lock.

B the log of transaction 1, that is, the transaction executed by sess2, requests a lock on delete 5 (X Next-key Lock), a rec lock, but because the execution of delete in sess1 has been completed, the record has not been deleted and the lock has not been released, so transaction 1 of sess2 waits for transaction 2 of sess1 to release the lock resources of sess1, and the log prompts lock_mode X waiting.

C then prompts transaction 2 insert statement to wait for lock mode S waiting based on WAITING FOR THIS LOCK TO BE GRANTED, why S lock this time? Because the a field is a unique index, the insert statement performs an duplicate key check before insertion, and you need to apply for an S lock to prevent repeated insertions of the a field by other transactions. The insert intention lock and T1 already insert statement must wait for the delete in the previous sess2 to acquire the row lock of await 5 and release the lock.

Undefinedsess2 (delete) waits for sess1 (delete), sess1 (insert) waits for sess2 (delete), and waits in a loop, resulting in deadlock undefined

This is the end of "detailed explanation of the case of MySQL deadlock". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.