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

This article is about how to use different index updates to solve MySQL deadlocks. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Examples are as follows

CREATE TABLE `t3` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` varchar (5), `b` varchar (5), PRIMARY KEY (`id`), UNIQUE KEY `uk_ a` (`a`), KEY `idx_ b` (`b`) INSERT INTO `t3` (`id`, `a`, `b`) VALUES

It is difficult to reproduce the deadlock caused by two statements manually. Let's first analyze the process of locking.

The first statement (to update the record through a unique index)

Update T3 set b =''where a = "1"

Tidy it up and add three X locks in the following order

Serial number index lock type 1uk_aX2PRIMARYX3idx_bX

The second sentence

Update T3 set b =''where b = "2"

Tidy it up and add three X locks in the following order

Serial number index lock type 1idx_bX2PRIMARYX3idx_bX

From the locking order, it seems that the two statements already have the conditions to constitute a deadlock.

Manual is more difficult to simulate. Write a code to execute those two SQL statements concurrently, and there will be a deadlock immediately.

-LATEST DETECTED DEADLOCK----181102 12 sec starting index readmysql tables in use 45 LATEST DETECTED DEADLOCK----181102 05 MySQL thread id * (1) TRANSACTION:TRANSACTION 50AF, locked 1LOCK WAIT 0 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 376,2 row lock (s) MySQL thread id 34, OS thread handle 0x70000d842000 Query id 549 localhost 127.0.0.1 root Searching rows for updateupdate T3 set b =''where b = "2" * * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 67 page no 3 n bits 72 index `PRIMAR Y`of table `d1`.`t3` trx id 50AF lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 5 Compact format; info bits 00: len 4; hex 80000001; asc; 1: len 6; hex 00000050ae; asc P; 2: len 7; hex 03000001341003; asc 4; 3: len 1; hex 31; asc 1; 4: len 0; hex; asc * (2) TRANSACTION:TRANSACTION 50AE, ACTIVE 0 sec updating or deletingmysql tables in use 1, locked 14 lock struct (s), heap size 1248, 3 row lock (s), undo log entries 1MySQL thread id 35, OS thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root Updatingupdate T3 set b =''where a = "1" * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 67 page no 3n bits 72 index `PRIMARY`of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gapRecord lock Heap no 2 PHYSICAL RECORD: n_fields 5 Compact format; info bits 00: len 4; hex 80000001; asc; 1: len 6; hex 00000050ae; asc P; 2: len 7; hex 03000001341003; asc 4; 3: len 1; hex 31; asc 1 mitt; 4: len 0; hex; asc; * * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 67 page no 5 n bits 72 index `idx_ b`idx_ b`d1`.`t3` trx id 50AE lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format Info bits 00: len 1; hex 32; asc 2; 1: len 4; hex 80000001; asc;; * * WE ROLL BACK TRANSACTION (1)

Analyze the deadlock log

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AF lock_mode X locks rec but not gap waiting

Transaction 2: want to acquire the X lock of the primary key index

* * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 67 page no 3 n bits 72 index PRIMARY of table d1.t3 trx id 50AE lock_mode X locks rec but not gap

Transaction 1: X lock that holds the primary key index

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 67 page no 5 n bits 72 index idx_b of table d1.t3 trx id 50AE lock_mode X locks rec but not gap waiting

Transaction 1: want to acquire the X lock of the normal index idx_b

It is completely consistent with our analysis, and it is exactly the same as the online deadlock log.

Thank you for reading! This is the end of this article on "how to use different index updates to solve MySQL deadlocks". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.