Solving MySQL deadlock routine by different index updates

Time:2019-8-31

Previous articles have introduced the use of source code to debug lock-related information. Here we also use this tool to solve an online real deadlock case. It is also the first two cases of deadlock caused by SQL that we introduced. Because of the complexity of the online table structure, some simplifications are made 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 
 (1,'1','2');
# The SQL statement is as follows

# Transaction 1:t1
update t3 set b = '' where a = "1";

# Transaction 2:t2
update t3 set b = '' where b = "2";

The situation of deadlock caused by two statements is difficult to reproduce manually. Let’s first analyze the process of locking.

The first statement (updating records by unique index)


update t3 set b = '' where a = "1";

 

Tidy up and add three X locks in the order of

Serial number Indexes Lock type
1 uk_a X
2 PRIMARY X
3 idx_b X

The second statement


update t3 set b = '' where b = "2";

Tidy up and add three X locks in the order of

Serial number Indexes Lock type
1 idx_b X
2 PRIMARY X
3 idx_b X

Two statements appear to have deadlock conditions from the lock order

Manual is more difficult to simulate, write a code concurrently to execute the two SQL statements at the same time, there will be a deadlock immediately.


------------------------
LATEST DETECTED DEADLOCK
------------------------
181102 12:45:05
*** (1) TRANSACTION:
TRANSACTION 50AF, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK 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 update
update 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 `PRIMARY` of table `d1`.`t3` trx id 50AF lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; 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 deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 35, OS thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root Updating
update t3 set b = '' where a = "1"
*** (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
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc P ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 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` of table `d1`.`t3` trx id 50AE lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

Analyse deadlock logs


*** (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: X locks that you want to get 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 locks with 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 get the X lock of the normal index idx_b

It’s exactly the same as our analysis, and it’s exactly the same as the deadlock log on-line.

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.

Recommended Today

Redis (1)

Redis About redis Remote dictionary server (redis) is a key value storage system. Redis is an open source log type, key value database written in ANSI C language, complying with BSD protocol, supporting network, memory based and persistent, and providing API in multiple languages. It is often referred to as a data structure server, because […]