通过唯一索引S锁与X锁来了解MySQL死锁套路

# 构造数据
CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10),
 `level` int(11),
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_name` (`name`)
);
INSERT INTO `t1` (`name`, `level`) VALUES ('A',0);

# 出现问题的sql语句如下，并发情况下就会出现死锁
INSERT ignore INTO `t1` (`name`, `level`) VALUES ('A',0);
update t1 set level = 1 where name = "A";

INSERT ignore INTO t1 (name, level) VALUES ('A',0);

update t1 set level = 1 where name = "A"; 

LATEST DETECTED DEADLOCK
------------------------
181208 23:00:52
*** (1) TRANSACTION:
TRANSACTION 53A7, ACTIVE 162 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 12, OS thread handle 0x700010522000, query id 1424 localhost root Updating
update t1 set level = 1 where name = "A"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A7 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 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 53A8, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updating
update t1 set level = 1 where name = "A"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 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 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (2)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock_mode X locks rec but not gap waiting