Shulou(Shulou.com)06/01 Report--

A preface

Deadlock is actually a very interesting and challenging technical problem, probably every DBA and some developer friends 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.

Case study 2.1 Business scenario

The main logic of the business:

The insert data is performed first, and if the insert is successful, it is submitted. If a unique key conflict is reported during insertion, an update is performed. If three concurrent data initialization actions occur at the same time, the sess1 insert succeeds, the sess2 and sess3 insert encounters a unique key conflict, and the insert fails, the update is executed and a deadlock occurs.

2.2 Environmental preparation

MySQL 5.6.24 transaction isolation level is RR

Create table ty (id int not null primary key auto_increment, C1 int not null default 0, c2 int not null default 0, c3 int not null default 0, unique key uc1 (C1), unique key uc2 (c2)) engine=innodb; insert into ty (C1 int not null default c2) values

In order to facilitate the analysis of the deadlock log, the value of c3 inserted by the three sessions is 1.23, which is actually the same value in production.

Sess1

Sess2

Sess3

Begin

Begin

Begin

T1

Insert into ty (C1, c2, c3) values (4, 4, 4, 4)

T2

Insert into ty (C1, c2, c3) values (4, 4, 4, 4)

T3

Insert into ty (C1, c2, c3) values (4, 4, 4, 4)

T4

Commit

T5

Update ty set c3i 5 where c1m 4

T6

Update ty set c3i 5 where c1m 4

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

2018 deadlock log 2018-03-28 10:04:52 0x7f75bf2d9700log * (1) TRANSACTION:TRANSACTION 1870, ACTIVE 76 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 399265, OS thread handle 12 Query id 9 root updatingupdate ty set c3, 5 where C1 4, 4 bits 72 index uc1 of table `test`.`ty` trx id 1870 lock_mode X locks rec but not gap waiting*** (2) TRANSACTION:TRANSACTION 1871, ACTIVE 32 sec starting index read, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 399937, OS thread handle 16 Query id 3 root updatingupdate ty set c3 trx id 5 where c1mm 4 bits * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock mode slots * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock_mode X locks rec but not gap waiting*** WE ROLL BACK TRANSACTION (2)

In fact, just looking at the log, we can only see that the update of two transactions compete with each other. In the absence of business logic scenarios, it is difficult to get effective ideas.

2.5 analyze deadlock logs

T1 S1 performs the insert operation, checks for uniqueness and successful insertion, and holds the row lock of the c1x4 record row.

T2 S2 insert encountered a unique key conflict. The application for locking Lock S Next-key Lock log is displayed as index uc1 of table test.ty trx id 1870 lock mode S waiting

T3 is the same as S2. S3 insert encounters a unique key conflict. The application for locking Lock S Next-key Lock log is displayed as index uc1 of table test.ty trx id 1870 lock mode S waiting.

T4 sess1 performs commit operation, and sess2 and sess3 get Lock S Next-key Lock at the same time.

The T5 application receives a unique key conflict. For sess2 to perform update operation, you need to apply for the row lock of Lock 4, which is not compatible with Lock S Next-key Lock held by sess3. Wait for sess3 to release Lock S Next-key Lock.

T6 is similar to sess2 sess3 to perform update operation requires applying for a row lock of clock4, which is not compatible with Lock S Next-key Lock held by sess2, waiting for sess2 to release Lock S Next-key Lock. A loop wait occurs and a deadlock occurs.

2.6 solution

The solution of this case is actually the same as that of the previous deadlock case 7, using insert on duplicate key. Case 7 is very similar to the deadlock business logic in this article. Why? Because they were all written by the same group of developer brothers.

Three summaries

The root cause of deadlock is that different transactions apply for locks in different order. Developers should focus on this when designing highly concurrent business scenarios, and try their best to avoid deadlocks caused by unreasonable design of business scenarios.

In addition, the locking mechanism of insert is actually more complex than update, which requires more hands-on practice and a clear locking process.

Recommended reading

How to read deadlock logs

Talking about deadlock

One of the deadlock cases

Deadlock case 2

Deadlock case 3

Deadlock case 4

Deadlock case 5

The sixth deadlock case

Deadlock case 7

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.