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

Background: check the status log and find that two insert have deadlocks

RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_ quantity`of table `ecejservice`.`svc _ order_service_ item` trx id 596252578 lock_mode X insert intention waiting can determine that this x lock is not generated by INSERT, because the locks that may be generated by INSERT include the s lock when dup key is checked, and the implicit lock is converted to an explicit lock (not gap, to generate a LOCK_ORDINARY type lock with lock_mode X on the secondary index (including the record and the gap before the record) As far as I know, it is generally caused by record updates based on secondary index scans.

3. According to

LOCK WAIT 14 lock struct (s), heap size 2936, 7 row lock (s), undo log entries 7

There are seven undo entires, while a simple INSERT has only one undo entry per record, so it can be inferred that there must be other operations besides INSERT.

Based on the above, in addition to INSERT, transactions may also have DELETE/UPDATE, and these operations are secondary indexes to find update records.

A simple but not exactly the same reproduction step: DROP TABLE T1; CREATE TABLE `t1` (`a` int (11) NOT NULL AUTO_INCREMENT, `b` int (11) DEFAULT NULL, `c` int (11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ENGINE=InnoDB; insert into T1 (a, bMagazine c) values (1pas 2), (5p4), (8,7), (12J, 1219), (15J, 1511)

Session1:

Begin; delete from T1 where b = 12; / / lock_mode X, lock_mode X locks gap before rec on secondary index and lock_mode X locks rec but not gap secondary index on primary key: heap_no=5,type_mode= 3 (LOCK_ORDINARY type lock on 12, including GAP before recording and before recording) clustered index: heap_no=5,type_mode=1027 secondary index: heap_no=6,type_mode=547 (GAP lock on 15) session2: begin; delete from T1 where b = 7 / / lock_mode X, lock_mode X locks gap before rec on secondary index and lock_mode X locks rec but not gap secondary index on primary key: heap_no=4,type_mode=3 (LOCK_ORDINARY type lock on 7, including GAP before record and record) clustered index: heap_no=4,type_mode=1027 secondary index: heap_no=5,type_mode=547 (GAP lock on record 12) session1: insert into T1 values (NULL, 6jue 10) / / New insert record clustered index was inserted successfully without conflict. Secondary index waits for insertion intention lock (lock_mode X locks gap before rec insert intention waiting) secondary index, heap_no=4, type_mode=2819 (request record 7 above insert intention lock LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, need to wait for session2 session2: insert into T1 values (NULL, 7L10) Secondary index: heap_no=5, type_mode=2819 (request record 12 insert intention lock LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, need to wait for session1) wait for each other, resulting in deadlock. From the printed deadlock information, it is basically consistent with the online deadlock phenomenon.

Give me another example.

Mysql > select * from test01

+-+ +

| | id | app |

+-+ +

| | 1 | 01 | |

| | 2 | 02 | |

| | 5 | 03 |

| | 10 | 03 |

| | 6 | 04 |

| | 7 | 05 |

| | 8 | 06 |

| | 9 | 06 |

| | 11 | 06 | |

| | 12 | 07 | |

| | 13 | 08 | |

| | 14 | 09 |

| | 15 | 09 |

+-+ +

13 rows in set (0.00 sec)

Session1:mysql > select now (); start TRANSACTION

+-+

| | now () |

+-+

| | 2018-01-25 16:08:46 |

+-+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test01 where app='05' for update;-the first step is to lock the intervals of [6.04]-[7.05] and [7.05]-[8.06]

+-+ +

| | id | app |

+-+ +

| | 7 | 05 |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into test01 (app) values ('07');-the third step waits for the second step to release

Query OK, 1 row affected (23.24 sec)

Session2:

Mysql > select * from test01 where app='08' for update;-- the second step is to lock the intervals of [12j07b]-[13j08g] and [13j08g]-[14j09g]

+-+ +

| | id | app |

+-+ +

| | 13 | 08 | |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into test01 (app) values ('04');-the fourth step waits for the first step to be released, so deadlock

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

View the deadlock log:

LATEST DETECTED DEADLOCK

-

2018-01-25 16:09:54 0x7f07d23ff700

* * (1) TRANSACTION:

TRANSACTION 5375, ACTIVE 51 sec inserting

Mysql tables in use 1, locked 1

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

MySQL thread id 2294, OS thread handle 139671567841024, query id 42463 localhost root update

Insert into test01 (app) values ('07')

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

RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5375 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 2; hex 3038; asc 08

1: len 4; hex 0000000d; asc

* * (2) TRANSACTION:

TRANSACTION 5376, ACTIVE 38 sec inserting

Mysql tables in use 1, locked 1

5 lock struct (s), heap size 1136, 4 row lock (s), undo log entries 1

MySQL thread id 2293, OS thread handle 139671568905984, query id 42464 localhost root update

Insert into test01 (app) values ('04')

* * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X

Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 2; hex 3038; asc 08

1: len 4; hex 0000000d; asc

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

RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 2; hex 3035; asc 05

1: len 4; hex 00000007; asc

* WE ROLL BACK TRANSACTION (2)

-

Is the deadlock log the same as the one above?

Reference:

Http://blog.itpub.net/22664653/viewspace-2145068/-Yang Qilong

Http://www.sohu.com/a/169663059_610509-insert.. select statement produces a deadlock

Http://blog.itpub.net/7728585/viewspace-2146183/-insert.. select statement produces deadlock-- eight eccentrics

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.