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

Test environment: mysql 5.7.18 RR isolation level

Create a table and insert some test data

CREATE TABLE yhtest (

An INT (11) NOT NULL AUTO_INCREMENT

B INT (11) DEFAULT NULL

C INT (11) DEFAULT NULL

PRIMARY KEY (a)

Unique key (b)

) ENGINE = INNODB

INSERT INTO yhtest VALUE (1,1,1)

(2, 2, 2)

(3, 3, 3)

(4, 4, 4)

(5, 5, 5)

(6, 6, 6)

(7, 7, 7)

(8, 8, 8)

(9, 9, 9)

(10, 10, 10)

(11, 11, 11)

Action:

Things 1:begin

Things 2:begin

Things 1:delete from yhtest where axiom 2; can be executed

Things 2:delete from yhtest where await 3; can be executed

Things 1:delete from yhtest where await 4; can be executed

Things 2:delete from yhtest where await 5; can be executed

Things 1:insert into yhtest value (2jue 2jue 2); lock waiting, after thing 2 is rolled back, it can be executed

Thing 2: insert into yhtest value (3rec 3jol 3); deadlock is reported, rollback, as follows:

Deadlock log:

-

LATEST DETECTED DEADLOCK

-

2017-10-27 19:38:00 0x7f4b67932700

* * (1) TRANSACTION:

TRANSACTION 3914, ACTIVE 117 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 6 lock struct (s), heap size 1136, 7 row lock (s), undo log entries 3

MySQL thread id 4, OS thread handle 139961837504256, query id 53 localhost root update

Insert into yhtest value (2pm 2pm 2)

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

RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3914 lock mode S waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000003; asc

1: len 4; hex 80000003; asc

* * (2) TRANSACTION:

TRANSACTION 3919, ACTIVE 93 sec inserting

Mysql tables in use 1, locked 1

6 lock struct (s), heap size 1136, 6 row lock (s), undo log entries 3

MySQL thread id 5, OS thread handle 139961836971776, query id 54 localhost root update

Insert into yhtest value (3pm 3pm 3)

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3919 lock_mode X locks rec but not gap

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000003; asc

1: len 4; hex 80000003; asc

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

RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3919 lock mode S waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000004; asc

1: len 4; hex 80000004; asc

* WE ROLL BACK TRANSACTION (2)

As can be seen from the innodb log

When thing 1 executes insert into yhtest value, it waits for the S lock on the b-column index bread3 to be added, and the row bread3 is locked because it is executed by thing 2; the exclusive lock is locked, and the S lock waits normally.

When thing 2 executes insert into yhtest value, it can be seen that it holds the X lock of baggage 3, waiting for the S lock on bread4 to be added, and the line of bread4 is locked by thing 1 because it is delete from yhtest where axiom 4; exclusive lock, S lock waits, at this time, thing 1 and thing 2 wait for the lock resources held by each other, forming a loop, and deadlocks appear.

Here thing 1 insert (2mag2 Magne2) and thing 2 insert (3d3) because the unique key of column b exists, it needs to be checked by unique key, and because the column has been deleted before, you need to lock the next column to determine the uniqueness of bend2, the uniqueness of bend3, and the locks of these two columns have been held by each other, so there is a deadlock.

This kind of deadlock situation can also occur under the RC isolation level!

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.