版本5.6，隔离级别为rc
表结构：
CREATE TABLE `uk_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_a_b` (`a`,`b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
表中数据：
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
| 1 | 1 | 1 | 2 |
| 6 | 1 | 2 | 1 |
+----+---+---+---+
sql:执行顺序
session1:begin;
session2:begin;
session1:select * from uk_test where a = 1 and b = 1 for update;
session2:select * from uk_test where a = 1 and b = 1 for update;
session1:insert into uk_test (a,b,c) values(1,1,2) on duplicate key update c = 2;
session2:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁信息：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-02-23 13:33:12 2b5795081700
*** (1) TRANSACTION:
TRANSACTION 2088212, ACTIVE 17.150 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 453394049 block 436450404
MySQL thread id 436450404, OS thread handle 0x2b5794040700, query id 16524108 10.151.203.57 hkadmin statistics
select * from uk_test where a = 1 and b = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 149 page no 4 n bits 72 index `uk_a_b` of table `aaaaa`.`uk_test` trx id 2088212 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 8; hex 8000000000000007; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2088208, ACTIVE 21.411 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 453394049, OS thread handle 0x2b5795081700, query id 16524256 10.151.203.57 hkadmin update
insert into uk_test (a,b,c) values(1,1,2) on duplicate key update c = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 149 page no 4 n bits 72 index `uk_a_b` of table `aaaaa`.`uk_test` trx id 2088208 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 8; hex 8000000000000007; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 149 page no 4 n bits 72 index `uk_a_b` of table `aaaaa`.`uk_test` trx id 2088208 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 8; hex 8000000000000007; asc ;;
*** WE ROLL BACK TRANSACTION (1)
分析：
如果a+b是主键，那么update的时候要获取a+b上的X锁，那么事务B就会继续等待，事务A已经持有了a+b的X锁，会继续执行，执行后释放掉，然后事务B继续运行
如果a+b是唯一索引，那么
insert into uk_test (a,b,c) values(1,1,2) on duplicate key update c = 2
其实要先获取a+b的S锁，但是此时事务B在等待a+b的X锁，所以事务A拿不到S锁，事务A和事务B都等待a+b的锁，并且这两个锁是互斥的，所以B被选为牺牲品
unique key 对 重复值比较的时候，需要先拿到 uk 上的 S 锁的
