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

Preface

The essence of deadlock is resource competition, and batch insertion can easily lead to deadlock if the order is inconsistent. Let's analyze this situation. To facilitate the demonstration, batch inserts are rewritten into multiple insert.

Let's do a few small experiments first. the simplified table structure is as follows.

CREATE TABLE `t1` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` varchar (5), `b` varchar (5), PRIMARY KEY (`id`), UNIQUE KEY `uk_ name` (`a`, `b`))

Lab 1:

In the case where the record does not exist, two batch insert of the same order are executed at the same time, and the second one performs a lock wait state.

T1 T2 beginscape insert ignore into T1 (a, b) values ("1", "1"); successful insert ignore into T1 (a, b) values ("1", "1"); lock wait status

You can see the status of the current lock

Mysql > select * from information_schema.innodb_locks +- -+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +-+- -+ | 31AE:54:4:2 | 31AE | S | RECORD | `d1`.`t1` | `uk_ name` | 54 | 4 | 2 |'1' '1' | | 31AD:54:4:2 | 31AD | X | RECORD | `d1`.`t1` | `uk_ name` | 54 | 4 | 2 |' 1' '1' | +-+

When we execute the insert of transaction T1, it does not appear at the breakpoint of any lock, which has something to do with the principle of MySQL insertion.

Insert adds an implicit lock. What is an implicit lock? Implicit lock means there is no lock.

When T1 inserts a record, it is unlocked. At this time, when transaction T1 has not yet committed, when transaction T2 tries to insert, it is found that there is this record. T2 attempts to acquire S lock, which will determine whether the transaction id on the record is active. If active, it means that the transaction has not ended and will help T1 upgrade its implicit lock to explicit lock (X lock).

The source code is as follows

T2 gets the result of S lock: DB_LOCK_WAIT

Lab 2:

Deadlock caused by inconsistent order of batch insertion

T1t2begininsert into T1 (a, b) values ("1", "1"); successful insert into T1 (a, b) values ("2", "2"); successful insert into T1 (a, b) values ("2", "2"); T1 attempts to acquire S lock, upgrading T2's implicit lock to explicit X lock, entering DB_LOCK_WAITinsert into T1 (a, b) values ("1", "1") T2 attempts to acquire the S lock, upgrading the implicit lock of T1 to an explicit X lock Deadlock-LATEST DETECTED DEADLOCK----181101 948 sec insertingmysql tables in use 36 localhost root updateinsert into * (1) TRANSACTION:TRANSACTION 3309, ACTIVE 215 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 376,2 row lock (s), undo log entries 2MySQL thread id 2, OS thread handle 0x70000a845000, query id 58 localhost root updateinsert into T1 (a) B) values ("2", "2") * * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_ name` of table `d1`.`t1` trx id 3309 lock mode S waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: len 1; hex 32; asc 2; 1: len 1; hex 32; asc 2; len 4; hex 80000002; asc * (2) TRANSACTION:TRANSACTION 330A, ACTIVE 163sec insertingmysql tables in use 1, locked 13 lock struct (s), heap size 376,2 row lock (s), undo log entries 2MySQL thread id 3, OS thread handle 0x70000a888000, query id 59 localhost root updateinsert into T1 (a, b) values ("1", "1") * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_ name`of table `d1`.`t1` trx id 330A lock_mode X locks rec but not gapRecord lock Heap no 3 PHYSICAL RECORD: n_fields 3 Compact format; info bits 00: len 1; hex 32; asc 2; 1: len 1; hex 32; asc 2; 2: len 4; hex 80000002; asc; * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_ name`of table `d1`.`t1`trx id 330A lock mode S waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 1; hex 31; asc 1; 1: len 1; hex 31; asc 1 X; 2: len 4 Hex 80000001; asc;; * * WE ROLL BACK TRANSACTION (2)

How to solve such a problem?

One possible way is to insert it after the application layer is sorted.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.