一、前言
这一篇死锁特别有意思，涉及主键和辅助索引的更新以及删除的死锁分析
如果没有特别说明，隔离级别均为RR
二、死锁输出
2018-03-25 12:20:16 0x7f9a34469700
*** (1) TRANSACTION:
TRANSACTION 3044459, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 92021, OS thread handle 140299577640704, query id 3114328 127.0.0.1 root updating
update t set c2=8 where c1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 3 n bits 80 index PRIMARY of table `db01`.`t` trx id 3044459 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 3044456, ACTIVE 14 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 92050, OS thread handle 140300278732544, query id 3114329 127.0.0.1 root updating
delete from t where id=30
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 3 n bits 80 index PRIMARY of table `db01`.`t` trx id 3044456 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 4 n bits 80 index idx_c1 of table `db01`.`t` trx id 3044456 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
表中的记录
| id | c1 | c2 |
|---|---|---|
| 24 | 3 | 4 |
| 25 | 3 | 4 |
| 26 | 3 | 4 |
| 27 | 3 | 4 |
| 30 | 5 | 8 |
其中
id为主键，c1 为普通索引KEY idx_c1 (c1)
SQL 执行顺序
| Time | Sess 1 | Sess 2 |
|---|---|---|
| @t1 | begin | |
| @t2 | select * from t where id=30 for update | |
| @t3 | update t set c2=8 where c1=5 | |
| @t4 | delete from t where id=30 |
死锁分析
Sess2开启一个事务，在@t2时刻执行了根据主键的select for update，持有id=30的lock_mode X record lockSess1执行根据c1=5的更新，虽然定位到的也是id=30的记录，但是其加锁顺序是先在idx_c1索引上加锁，顺利加锁，然后到主键上加id=30的锁，发现Sess2已经持有X的锁，需要等待Sess2执行id=30的记录删除，由于事务本身已经持有了主键上的锁，删除记录同时要对索引idx_c1上的记录加上lock_mode X record lock，发现该锁已经被Sess1持有，形成了死锁条件，Sess1报错，发生回滚。
这个死锁的根本原因是因为一个是通过主键加锁，一个是通过辅助索引加锁。
三、拓展案例分析：
我们发散下思维，继续分析执行根据主键或者执行其他语句是否会造成死锁
拓展案例一：
| Time | Sess 1 | Sess 2 |
|---|---|---|
| @t1 | begin | |
| @t2 | select * from t where id=30 for update | |
| @t3 | update t set c2=8 where c1=5 | |
| @t4 | update t set c2=8 where id=30 |
分析：
@t1 到 @t3 的逻辑和上文分析的一样，不一样的是Sess2 在@t4 执行的SQL，这个案例是不会发生死锁的，因为Sess2执行的更新的字段是c2，不涉及到c1，因此不需要去申请idx_c1上的X锁，因此不需要等待Sess1在idx_c1上的加的X锁。
拓展案例二：
| Time | Sess 1 | Sess 2 |
|---|---|---|
| @t1 | begin | |
| @t2 | select * from t where id=30 for update | |
| @t3 | update t set c2=8 where c1=5 | |
| @t4 | update t set c1=6 where id=30 |
分析：
和案例一唯一不同的是Sess2在@t4时刻更新的字段是c1，导致需要等待Sess1在@t3时刻获取的idx_c1上的X锁，造成了死锁
拓展案例三：
| Time | Sess 1 | Sess 2 |
|---|---|---|
| @t1 | begin | |
| @t2 | select * from t where id=30 for update | |
| @t3 | update t set c2=8 where id=30 | |
| @t4 | update t set c1=6 where id=30 |
分析：
Sess2 在@t2时刻持有了id=30的X锁，Sess1在@t3时刻申请id=30的X锁，发生等待，Sess2 在@t3时刻根据id=30更新c1字段，虽然c1 字段在idx_c1索引里面，但是Sess1并没有持有对应的idx_c1上的锁，不需要发生等，Sess2执行成功
拓展案例四：
| Time | Sess 1 | Sess 2 |
|---|---|---|
| @t1 | begin | |
| @t2 | select * from t where id=30 for update | |
| @t3 | update t set c2=8 where id=30 | |
| @t4 | delete from t where id=30 |
分析：
和案例三基本一样，Sess2 也是顺利执行成功
四、小结
根据不同的索引加锁也很容易造成死锁，因此推荐的避免死锁方案是把Sess1原本使用辅助索引的更新改成基于主键进行更新，从而避免了idx_c1上的加锁，也就是使用方案四。
