MySQL Deadlock record

Test environment: MySQL 5.7.18 RR isolation level

650) this.width=650; "style=" width:632px;height:261px; "title=" QQ picture 20171027105421.png "Src=" https://s4.51cto.com/ Oss/201710/27/27fa6f262f376b65de32918df8266dfb.png "width=" 741 "height=" 323 "alt=" 27fa6f262f376b65de32918df8266dfb.png "/>

Create a table, insert some test data

CREATE TABLE Yhtest (
A INT (one) not NULL auto_increment,
b INT (one) DEFAULT NULL,
C INT (one) 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);

Operation:

The 1:begin of things;

The 2:begin of things;

Things 1:delete from yhtest where a=2; can perform

Things 2:delete from yhtest where a=3; can perform

Things 1:delete from yhtest where a=4; can perform

Things 2:delete from yhtest where a=5; can perform

Things 1:insert into Yhtest value (2,2,2); Lock wait, after the Thing 2 rollback, can execute

Things 2:insert into Yhtest value (3,3,3); To report a deadlock, roll back, as follows:

650) this.width=650; "style=" width:642px;height:62px; "title=" QQ picture 20171027114029.png "Src=" https://s4.51cto.com/ Oss/201710/27/3d79886f16bc751ed12b0ca345d8f85b.png "width=" 775 "height=" "alt=" 3d79886f16bc751ed12b0ca345d8f85b.png "/>

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 1399 61837504256, query ID. localhost root update
INSERT into Yhtest value (2,2,2)
* * * (1) Waiting for-this LOCK-to-be G ranted:
RECORD LOCKS Space ID page No 4 n bits index B of table ' test '. ' Yhtest ' TRX ID 3914 lock mode S waitingrecord Lock, Heap No 4 physical record:n_fields 2; Compact format; Info bits
0:len 4; hex 80000003; ASC    ;;
1:len 4; hex 80000003; ASC    ;;

* * * (2) TRANSACTION:
TRANSACTION 3919, ACTIVE tables sec inserting
MySQL locked in use 1, 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 si l Ocalhost Root update
INSERT into Yhtest value (3,3,3)
* * * (2) holds the LOCK (S):
RECORD LOCKS Space ID page no 4 N bits index B of table ' test '. ' Yhtest ' Trx ID 3919 lock_mode X locks Rec but not gap
Record lock, heap No 4 physi CAL Record:n_fields 2; Compact format; Info bits
0:len 4; hex 80000003; ASC    ;;
1:len 4; hex 80000003; ASC    ;;

(2) Waiting for this LOCK to be granted:
RECORD LOCKS Space ID page No 4 n bits 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 through the InnoDB log,

Thing 1 execution INSERT into Yhtest value (2,2,2);  , it waits for the S-lock on the B-column index b=3 to be added, b=3 this line because the delete from yhtest where a=3 is executed by the thing 2; Exclusive lock lock, s lock waiting for normal.

Thing 2 execution INSERT into Yhtest value (3,3,3); , it can be seen that it holds b=3 x lock, waits for S lock on b=4 to be added, b=4 this line is executed by thing 1 delete from yhtest where a=4; Exclusive lock lock, S lock wait, this time, things 1 and things 2 each other holding the lock resources, forming a loopback, deadlock appears

Here things 1 insert (2,2,2) and Thing 2 insert (3,3,3) because the unique key of column B exists, the unique key verification is required, and because the column has been deleted before, it is necessary to determine the uniqueness of the b=2 by locking the next column, the uniqueness of the b=3, and the locks of the two columns have been held by the other party, so there is a

This deadlock condition will also appear under the RC isolation level!

MySQL Deadlock record