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

This article introduces the relevant knowledge of "the introduction of the case of deadlock caused by mysql insert". In the operation of the actual case, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Two cases where a deadlock occurred in an insert statement.

one。 Prepare data

CREATE TABLE `t1` (`a` int (11) NOT NULL,PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;insert into T1 values (1); mysql > insert into T1 values (1); Query OK, 1 row affected (0.20 sec) mysql > select * from T1 + | a | +-- + | 1 | +-+ 1 row in set (0.00 sec)

two。 Initiate the following transaction

Session 1

Session 2

Session 3

Begin

Delete from T1 where astat1

Begin

Insert into t1 select 1

Begin

Insert into t1 select 1

Commit

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

To put it simply, the session that deletes data holds an X lock, resulting in two insert statements having to wait, which is no problem.

But why there is a deadlock between the two competing relationships after the submission is deleted.

Some results of show engine innodb status:

-LATEST DETECTED DEADLOCK----2020-05-17 13:50:24 0x7f660c3f0700 MySQL thread id * (1) TRANSACTION:TRANSACTION 4377, ACTIVE 13 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 3, OS thread handle 140076399294208 Query id 59 localhost root executinginsert into T1 select 1 lock_mode X locks rec but not gap waitingRecord lock * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 99 page no 3 n bits 72 index PRIMARY of table `ming`.`t1` trx id 4377 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3 Compact format; info bits 320: len 4; hex 80000001; asc; 1: len 6; hex 0000001114; asc; 2: len 7; hex 3400000144129f; asc 4 D * (2) TRANSACTION:TRANSACTION 4378, ACTIVE 10 sec inserting, thread declared inside InnoDB 1mysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 4, OS thread handle 140076268848896, query id 61 localhost root executinginsert into T1 select 1mm * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 99 page no 3 n bits 72 index PRIMARY of table `ming`t1` trx id 4378 lock mode SRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 320: len 4; hex 80000001 Asc;; 1: len 6; hex 0000001114; asc; 2: len 7; hex 3400000144129f; asc 4 D; * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 99 page no 3 n bits 72 index PRIMARY of table `ming`.`t1` trx id 4378 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 320: len 4; hex 80000001; asc; 1: len 6; hex 0000001114; asc; 2: len 7 Hex 3400000144129f; asc 4 D; * * WE ROLL BACK TRANSACTION (2)

You can see that session 2 is waiting for an X row lock, and session 3 is also waiting for an X row lock, but it also holds an S lock.

How did this S lock come from?

When performing uniqueness conflict detection, it is necessary to add an S lock first.

Then the whole process is as follows

Session 1

Session 2

Session 3

Begin

Delete from T1 where astat1

Hold the line X lock of axiom 1

Begin

Insert into t1 select 1

In order to determine the uniqueness, the next-key lock S lock of request aquired 1 is blocked, wait

Begin

Insert into t1 select 1

In order to determine the uniqueness, the next-key lock S lock of request aquired 1 is blocked, wait

Commit

Release the lock on astat1

Get the next-key lock S lock of April1 and continue to try to get the X lock of Apati1, but it is blocked by the S lock of session 3.

Get the next-key lock S lock on April1, continue to try to get the X lock on Abig1, try to get the X lock on Apoli1, but be blocked by the S lock of session 2.

Trigger deadlock

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

This is the end of the introduction to the case of deadlock caused by mysql insert. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.