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

This article mainly introduces "MySQL deadlock example analysis". In daily operation, I believe many people have doubts about MySQL deadlock example analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL deadlock example analysis". Next, please follow the editor to study!

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. This paper introduces an example of a deadlock caused by three concurrent insert. The root cause is that the insert unique key applies for inserting an intention lock, a special GAP lock. In fact, it is more reasonable to call the insert intention lock Insert Intention Gap Lock.

Two case studies

2.1 Environmental preparation

Percona server 5.6RR mode

Sess1

Sess2

Sess3

Begin

Insert into T6 (id,a) values (6jie 15)

Begin

Insert into T6 (id,a) values (7pm 15)

Begin

Insert into T6 (id,a) values (8penny 15)

Rollback

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

2.2 deadlock log

-

LATEST DETECTED DEADLOCK

-

2017-09-18 10:03:50 7f78eae30700

* * (1) TRANSACTION:

TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB 1

Mysql tables in use 1, locked 1

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

MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update

Insert into T6 (id,a) values (7pm 15)

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

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308725 lock_mode X insert intention waiting

* * (2) TRANSACTION:

TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB 1

Mysql tables in use 1, locked 1

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

MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update

Insert into T6 (id,a) values (8penny 15)

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308726 lock mode S

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

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308726 lock_mode X insert intention waiting

* WE ROLL BACK TRANSACTION (2)

2.3 deadlock analysis

First of all, again emphasize the locking logic of the insert insert operation.

The first stage: uniqueness constraint check, apply for LOCK_S + LOCK_ORDINARY first

The second stage: after the lock of phase one is acquired and the insert is successful, the location inserted is the Gap lock: LOCK_INSERT_INTENTION, in order to prevent other insert unique key conflicts.

New data insertion: LOCK_X + LOCK_REC_NOT_GAP

For insert operations, if a unique constraint conflict occurs, the conflicting unique index needs to be appended with S Next-key Lock. From here, you will find that even at the RC transaction isolation level, there are also Next-Key Lock locks that block concurrency. However, what is not stated in the document is that for the only index that detects a conflict, the waiting thread needs to lock the next record after obtaining the S Lock, which is judged by the function row_ins_scan_sec_index_for_duplicate in the source code.

Secondly, we need to understand the compatibility matrix of the lock.

From the compatibility matrix, we can draw the following conclusions:

There is no conflict between INSERT operations.

GAP,Next-Key blocks Insert.

GAP and Record,Next-Key will not conflict

Record, Record and Next-Key conflict with each other.

Existing Insert locks do not block any locks that are ready to be added.

In this case, three sessions are executed concurrently, and I intend to analyze the transaction log after each step is executed step by step.

Step 1: sess1 performs the insert operation

Insert into T6 (id,a) values (6jie 15)

-TRANSACTION 462308737, ACTIVE 5 sec

1 lock struct (s), heap size 360,0 row lock (s), undo log entries 1

MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149440 localhost root init

Show engine innodb status

TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

Because of the first inserted statement, the uniqueness conflict check passed and was successfully inserted (6 ~ 15). At this point, the sess1 session holds the LOCK_X | LOCK_REC_NOT_GAP lock (6. 15). Refer to "INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."

Step 2 sess2 performs the insert operation

Insert into T6 (id,a) values (7pm 15)

-TRANSACTION 462308738, ACTIVE 4 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 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update

Insert into T6 (id,a) values (7pm 15)

-TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S waiting

-

TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S waiting

-TRANSACTION 462308737, ACTIVE 66 sec

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

MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149526 localhost root init

Show engine innodb status

TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap

First of all, sess2's insert applied for the IX lock, because the sess1 session has been successfully inserted and holds the X row lock with the unique key axi15, so sess2 insert carries out the uniqueness check, applies for LOCK_S + LOCK_ORDINARY first, and prompts lock mode S waiting in the transaction log list.

The third part sess3 performs the insert operation.

Insert into T6 (id,a) values (8penny 15)

-TRANSACTION 462308739, ACTIVE 3 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 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root update

Insert into T6 (id,a) values (8penny 15)

-TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308739 lock mode S waiting

-

TABLE LOCK table `test`.`t6` trx id 462308739 lock mode IX

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308739 lock mode S waiting

-TRANSACTION 462308738, ACTIVE 35 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 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update

Insert into T6 (id,a) values (7pm 15)

-TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S waiting

-

TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S waiting

-TRANSACTION 462308737, ACTIVE 97 sec

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

MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149560 localhost root init

Show engine innodb status

TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap

The lock application process of sess2 is the same, and all are waiting for sess1 to release lock resources.

Step 4: sess1 performs a rollback operation, but sess2 does not submit

Sess1 rollback

When the sess2 is inserted successfully, the sess3 is deadlocked, and the sess2 insert is inserted successfully and not yet committed. The transaction list is as follows:

-

TRANSACTIONS

-

Trx id counter 462308744

Purge done for trx s NRO < 462308744 undo NRO < 0 state: running but idle

History list length 1866

LIST OF TRANSACTIONS FOR EACH SESSION:

-TRANSACTION 462308737, not started

MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149626 localhost root init

Show engine innodb status

-TRANSACTION 462308739, not started

MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root cleaning up

-TRANSACTION 462308738, ACTIVE 75 sec

5 lock struct (s), heap size 1184, 3 row lock (s), undo log entries 1

MySQL thread id 3825768, OS thread handle 0x7f78eadce700, query id 781149608 localhost root cleaning up

TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock_mode X insert intention

RECORD LOCKS space id 227page no 4 n bits 80 index `idx_ a` of table `test`.`t6` trx id 462308738 lock mode S locks gap before rec

The cause of the deadlock

Sess1 insert succeeded and added an X lock to the unique key of axi15.

Sess2 performs insert insertion (615). Before insertion, it is found that the duplicate key of the inserted record of sess1 needs to apply for LOCK_S | LOCK_ORDINARY, but conflicts with sess1's (LOCK_X | LOCK_REC_NOT_GAP), joins the waiting queue and waits for sess1 to release the lock.

Sess3 performs insert insertion (715). Before insertion, it is found that the duplicate key of the inserted record of sess1 needs to apply for LOCK_S | LOCK_ORDINARY, but conflicts with sess1's (LOCK_X | LOCK_REC_NOT_GAP), joins the waiting queue and waits for sess1 to release the lock.

Sess1 executes rollback, and sess1 releases the exclusive record lock (LOCK_X | LOCK_REC_NOT_GAP) on index axiom 15. After that, sess2 and sess3 acquire S lock (LOCK_S | LOCK_ORDINARY) successfully. Both sess2 and sess3 request exclusive record lock (LOCK_X | LOCK_REC_NOT_GAP) on index axiom 15, and lock_mode X insert intention is prompted in the log. Because the X lock and the S lock are mutually exclusive, both sess2 and sess3 wait for the other to release the S lock, so a deadlock occurs, and MySQL chooses to roll back one of them.

At this point, the study on "MySQL deadlock example analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.