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

I. Preface

Deadlock, in fact, is a very interesting and challenging technical problem, probably every DBA and some development friends will meet in the process of work. I will continue to write a series of case studies on deadlocks, hoping to help friends who want to understand deadlocks.

II Background knowledge

2.1 insert lock mechanism

Before examining deadlock cases, let's look at the locking strategy for insert statements as background knowledge. Let's look at the official definition:

sess1

sess2

begin;

delete from t8 where b = 1;

begin;

insert into t8 values (NULL,1);

commit;

update t8 set

T2

begin;

begin;

insert into t7(id,a) values(26,10);

insert into t7(id,a) values(30,10);

insert into t7(id,a) values(40,9);

3.3 deadlock log

------------------------

LATEST DETECTED DEADLOCK

------------------------

2017-09-17 15:15:03 7f78eac15700

*** (1) TRANSACTION:

TRANSACTION 462308661, ACTIVE 6 sec inserting

mysql tables in use 1, locked 1

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

MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update

insert into t7(id,a) values(30,10)

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

RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.` t7` trx id 462308661 lock mode S waiting

*** (2) TRANSACTION:

TRANSACTION 462308660, ACTIVE 43 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 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update

insert into t7(id,a) values(40,9)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.` t7` trx id 462308660 lock_mode X locks rec but not gap

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

RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.` t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (1) Log analysis

We analyze it from the timeline dimension:

T2 insert into t7(id,a) values(26,10) statement insert succeeds, X locks rec but not gap with a=10

T1 insert into t7(id,a) values(30,10), because the first insert of T2 has inserted a record with a=10, insert a=10 of T1 has a unique constraint conflict, you need to apply for the unique index of the conflict plus S Next-key Lock (i.e. lock mode S waiting ) This is a gap lock that will apply to lock the gap area between [4,10],[10,20]. From here, you can see that even at RC transaction isolation levels, Next-Key Lock locks still exist, blocking concurrency.

T2 insert into t7(id,a) values(40,9) The value of a=9 inserted by this statement is between the gap locks [4,10] applied by T1, so the second insert statement of T2 needs to wait for the S-Next-key Lock of T1 to be released, and the lock_mode X locks gap before rec insert intention waiting is displayed in the log.

IV Summary

First of all, I would like to thank Deng Bo and Jiang Chengyao for their selfless sharing of deadlock technical knowledge. On the one hand, the cases and knowledge points in this article are obtained from official documents, on the other hand, they are sorted out according to the sharing of two big cows, which is regarded as a learning summary standing on the shoulders of giants. In the process of studying and analyzing deadlock cases, insert intent locks and gap locks are relatively difficult to analyze. I believe that through the above analysis, we can learn the locking mechanism of insert, how to add locks, and how to carry out insert deadlock analysis.

If you feel that reading this article can gain something, welcome to reward a bottle of drink

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.