The previous articles introduced the use of source code to debug lock-related information. Here we also use this tool to solve an online deadlock case, which is our first introduction to the deadlock caused by two SQL cases. Because the table structure on the line is quite complex, after some simplification, it is shown 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";
Copy the code

The deadlock caused by two statements is difficult to reproduce manually, so let’s analyze the lock process first

First statement (update records by unique index)

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

The serial number The index The lock type
1 uk_a X
2 PRIMARY X
3 idx_b X

Second statement

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

The serial number The index The lock type
1 idx_b X
2 PRIMARY X
3 idx_b X

The order in which the two statements are locked looks like they already have deadlock conditions

Manual is difficult to simulate, write a code to execute the two SQL statements concurrently, immediately appear deadlock

------------------------
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 logEntries 1 MySQL thread ID 35, OS Thread Handle 0x70000D885000, Query ID 548 localhost 127.0.0.1 root Updating update T3set 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)
Copy the code

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: the X lock that wants to acquire 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 the 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 acquire the X lock on the common index IDX_B

It’s exactly the same as our analysis, and it’s exactly the same as the deadlock log online