Solve MySQL deadlock routine by updating different indexes

The second statement

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

To sort it out, 3 X locks were added, in the following order:

The previous articles introduced how to debug lock-related information using source code. Here, we also use this tool to solve an actual online deadlock case, which is also the first two SQL statements we introduced that caused a deadlock. Because the online table structure is relatively complex, 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 where the two statements cause a deadlock is difficult to reproduce manually. Let's first analyze the locking process.

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

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

After finishing, 3 X locks were added, in the following order:

Serial number	index	Lock Type
1	uk_a	X
2	PRIMARY	X
3	idx_b	X

Serial number	index	Lock Type
1	idx_b	X
2	PRIMARY	X
3	idx_b	X

The two statements seem to have the conditions for deadlock from the order of locking.

It is difficult to simulate manually. If you write a code to execute the two SQL statements concurrently, a deadlock will occur 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)

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 obtain 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: holds an X lock on 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: Wants to obtain the X lock of the common index idx_b

It is exactly the same as our analysis and the online deadlock log.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in: