死锁
原因：并发事务在执行过程中，因争夺锁资源而造成互相等待。
加锁顺序导致死锁：不同表加锁顺序相反、相同表不同行加锁顺序相反，其中相同表不同行加锁顺序相反造成死锁有很多变种，其中容易忽略的是给辅助索引行加锁的时候，同时会给聚集索引行加锁；同时还可能出现在外键索引时，给父表加锁，同时隐含给子表加锁；触发器同样如此，这些都需要视情况分析；
解决办法：调整加锁顺序
锁冲突死锁：RR级别下，插入意向锁与gap锁冲突。A事务加上gap锁，并获取插入意向锁防止其他事务插入数据，B事务对gap锁住的范围执行DML操作，事务B获取行锁前尝试获取对应行的插入意向锁。在这种情况下，事务A持有了 Gap锁并等待事务B释放行级锁，而事务B持有了行级锁并等待事务A释放插入意向锁。
解决办法：降级到RC，通常就没有gap锁了
线上遇到一起死锁问题，一条DELETE语句与一条UPDATE语句产生了死锁，原因：DELETE语句通过二级索引删除记录，加锁顺序：二级索引（WHERE使用到二级索引）–>主键索引 –> 所有其它二级索引，UPDATE语句的加锁顺序：二级索引（WHERE条件使用二级索引）–>主键索引 –>包含更新字段的其它二级索引，由于DELETE操作更新了UPDATE语句WHERE条件使用到的索引，这导致DELETE与UPDATE加锁顺序相反，导致死锁。
实验数据
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a_b` (`a`,`b`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB
1 row in set (12.27 sec)
mysql> select * from t;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 6546 | 6238 | 1551 |
| 2 | 9042 | 558 | 5664 |
| 3 | 6644 | 6230 | 1216 |
| 4 | 7391 | 3308 | 4365 |
| 5 | 1900 | 6408 | 6337 |
| 6 | 2461 | 3296 | 9096 |
| 7 | 5593 | 676 | 6600 |
| 8 | 972 | 5062 | 2391 |
| 9 | 6773 | 6688 | 3123 |
| 10 | 5550 | 8383 | 5266 |
| 11 | 1181 | 93 | 6932 |
| 12 | 4378 | 1097 | 2351 |
| 13 | 8461 | 5255 | 891 |
| 14 | 8690 | 775 | 7808 |
| 15 | 6712 | 137 | 549 |
| 16 | 2335 | 27 | 3128 |
+----+------+------+------+
16 rows in set (0.00 sec)
update语句：
update语句：
update t set a=a+1 where b=93;
read阶段1：
row_search_for_mysql，对找到的二级索引记录加 LOCK_X（LOCK_ORDINARY）锁（index->name=idx_b）
#0 lock_rec_lock (impl=0, mode=3, block=0x7f4543967d00, heap_no=3, index=0x7f4520023b68, thr=0x7f452000dd68)
at storage/innobase/lock/lock0lock.c:2118
#1 0x0000000000917397 in lock_sec_rec_read_check_and_lock (flags=<value optimized out>, block=0x7f4543967d00, rec=0x7f45c1af408c "\200", index=0x7f4520023b68,
offsets=0x7f452db6ff80, mode=<value optimized out>, gap_mode=0, thr=0x7f452000dd68)
at storage/innobase/lock/lock0lock.c:5477
#2 0x0000000000856137 in sel_set_rec_lock (block=0x7f4543967d00, rec=0x7f45c1af408c "\200", index=0x7f4520023b68, offsets=0x7f452db6ff80,
mode=<value optimized out>, type=<value optimized out>, thr=0x7f452000dd68)
at storage/innobase/row/row0sel.c:1007
#3 0x000000000085937d in row_search_for_mysql (buf=0x7f452000bf20 "\377", mode=2, prebuilt=<value optimized out>, match_mode=1, direction=<value optimized out>)
at storage/innobase/row/row0sel.c:4280
#4 0x00000000008306a5 in ha_innobase::index_read (this=0x7f45200085d0, buf=0x7f452000bf20 "\377", key_ptr=0x7f452002a198 "", key_len=5,
find_flag=<value optimized out>) at storage/innobase/handler/ha_innodb.cc:6477
#5 0x000000000068d69f in handler::read_range_first (this=0x7f45200085d0, start_key=<value optimized out>, end_key=<value optimized out>,
eq_range_arg=<value optimized out>, sorted=<value optimized out>) at sql/handler.cc:4527
#6 0x000000000068af26 in handler::read_multi_range_first (this=0x7f45200085d0, found_range_p=0x7f452db70418, ranges=<value optimized out>,
range_count=<value optimized out>, sorted=<value optimized out>, buffer=<value optimized out>)
at sql/handler.cc:4401
#7 0x00000000007451b6 in QUICK_RANGE_SELECT::get_next (this=0x7f4520023370) at sql/opt_range.cc:8711
#8 0x000000000075f12b in rr_quick (info=0x7f452db70700) at sql/records.cc:344
#9 0x00000000005f7b36 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>,
order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
at sql/sql_update.cc:644
read阶段2：
row_search_for_mysql，对找到的主键索引记录加 LOCK_X（LOCK_REC_NOT_GAP）锁（index->name=PRIMARY）
#0 lock_rec_lock (impl=0, mode=1027, block=0x7f454396c840, heap_no=12, index=0x7f452000d238, thr=0x7f452000dd68)
at storage/innobase/lock/lock0lock.c:2118
#1 0x0000000000917089 in lock_clust_rec_read_check_and_lock (flags=<value optimized out>, block=0x7f454396c840, rec=0x7f45c1ba01dc "\200", index=0x7f452000d238,
offsets=<value optimized out>, mode=<value optimized out>, gap_mode=1024, thr=0x7f452000dd68)
at storage/innobase/lock/lock0lock.c:5551
#2 0x000000000085a0f8 in row_sel_get_clust_rec_for_mysql (buf=0x7f452000bf20 "\377", mode=2, prebuilt=<value optimized out>, match_mode=1,
direction=<value optimized out>) at storage/innobase/row/row0sel.c:2976
#3 row_search_for_mysql (buf=0x7f452000bf20 "\377", mode=2, prebuilt=<value optimized out>, match_mode=1, direction=<value optimized out>)
at storage/innobase/row/row0sel.c:4478
#4 0x00000000008306a5 in ha_innobase::index_read (this=0x7f45200085d0, buf=0x7f452000bf20 "\377", key_ptr=0x7f452002a198 "", key_len=5,
find_flag=<value optimized out>) at storage/innobase/handler/ha_innodb.cc:6477
#5 0x000000000068d69f in handler::read_range_first (this=0x7f45200085d0, start_key=<value optimized out>, end_key=<value optimized out>,
eq_range_arg=<value optimized out>, sorted=<value optimized out>) at sql/handler.cc:4527
#6 0x000000000068af26 in handler::read_multi_range_first (this=0x7f45200085d0, found_range_p=0x7f452db70418, ranges=<value optimized out>,
range_count=<value optimized out>, sorted=<value optimized out>, buffer=<value optimized out>)
at sql/handler.cc:4401
#7 0x00000000007451b6 in QUICK_RANGE_SELECT::get_next (this=0x7f4520023370) at sql/opt_range.cc:8711
#8 0x000000000075f12b in rr_quick (info=0x7f452db70700) at sql/records.cc:344
#9 0x00000000005f7b36 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>,
order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
at sql/sql_update.cc:644
update阶段1：
row_upd_clust_step，更新主键索引记录
#0 row_upd_clust_step (node=0x7f4520022258, thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2127
#1 0x000000000086217e in row_upd (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2317
#2 row_upd_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2457
#3 0x000000000084ca71 in row_update_for_mysql (mysql_rec=<value optimized out>, prebuilt=0x7f452000b328)
at storage/innobase/row/row0mysql.c:1462
#4 0x00000000008328b1 in ha_innobase::update_row (this=0x7f45200085d0, old_row=0x7f452000bf38 "\361\v", new_row=0x7f452000bf20 "\361\v")
at storage/innobase/handler/ha_innodb.cc:6042
#5 0x000000000068e48a in handler::ha_update_row (this=0x7f45200085d0, old_data=0x7f452000bf38 "\361\v", new_data=0x7f452000bf20 "\361\v")
at sql/handler.cc:5031
#6 0x00000000005f8139 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>,
order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
at sql/sql_update.cc:713
update阶段2：
row_upd_sec_step，更新二级索引记录（node->index->name = idx_a_b）
#0 row_upd_sec_index_entry (node=0x7f4520022258, thr=0x7f4520022558) at storage/innobase/row/row0upd.c:1583
#1 0x0000000000862337 in row_upd_sec_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:1710
#2 row_upd (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2340
#3 row_upd_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2457
#4 0x000000000084ca71 in row_update_for_mysql (mysql_rec=<value optimized out>, prebuilt=0x7f452000b328)
at storage/innobase/row/row0mysql.c:1462
#5 0x00000000008328b1 in ha_innobase::update_row (this=0x7f45200085d0, old_row=0x7f452000bf38 "\361\v", new_row=0x7f452000bf20 "\361\v")
at storage/innobase/handler/ha_innodb.cc:6042
#6 0x000000000068e48a in handler::ha_update_row (this=0x7f45200085d0, old_data=0x7f452000bf38 "\361\v", new_data=0x7f452000bf20 "\361\v")
at sql/handler.cc:5031
#7 0x00000000005f8139 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>,
order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
at sql/sql_update.cc:713
接update阶段2
二级索引记录加锁LOCK_X(LOCK_REC_NOT_GAP)（index->name = idx_a_b）
#0 lock_rec_lock (impl=1, mode=1027, block=0x7f4543968080, heap_no=3, index=0x7f4520024a18, thr=0x7f4520022558)
at storage/innobase/lock/lock0lock.c:2118
#1 0x00000000009175c0 in lock_sec_rec_modify_check_and_lock (flags=<value optimized out>, block=0x7f4543968080, rec=<value optimized out>, index=0x7f4520024a18,
thr=0x7f4520022558, mtr=0x7f452db6fcb0) at storage/innobase/lock/lock0lock.c:5377
#2 0x000000000089e1bc in btr_cur_del_mark_set_sec_rec (flags=<value optimized out>, cursor=<value optimized out>, val=<value optimized out>,
thr=<value optimized out>, mtr=0x7f452db6fcb0) at storage/innobase/btr/btr0cur.c:2969
#3 0x0000000000861133 in row_upd_sec_index_entry (node=0x7f4520022258, thr=0x7f4520022558)
at storage/innobase/row/row0upd.c:1648
#4 0x0000000000862337 in row_upd_sec_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:1710
#5 row_upd (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2340
#6 row_upd_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2457
#7 0x000000000084ca71 in row_update_for_mysql (mysql_rec=<value optimized out>, prebuilt=0x7f452000b328)
at storage/innobase/row/row0mysql.c:1462
#8 0x00000000008328b1 in ha_innobase::update_row (this=0x7f45200085d0, old_row=0x7f452000bf38 "\361\v", new_row=0x7f452000bf20 "\361\v")
at storage/innobase/handler/ha_innodb.cc:6042
#9 0x000000000068e48a in handler::ha_update_row (this=0x7f45200085d0, old_data=0x7f452000bf38 "\361\v", new_data=0x7f452000bf20 "\361\v")
at sql/handler.cc:5031
#10 0x00000000005f8139 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>,
order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
at sql/sql_update.cc:713
select阶段结束
锁住最后一条记录的下一条记录的间隙LOCK_X（LOCK_GAP），防止select阶段有数据插入（index->name=idx_b）
#0 lock_rec_lock (impl=0, mode=515, block=0x7f4543967d00, heap_no=4, index=0x7f4520023b68, thr=0x7f452000dd68)
at storage/innobase/lock/lock0lock.c:2118
#1 0x0000000000917397 in lock_sec_rec_read_check_and_lock (flags=<value optimized out>, block=0x7f4543967d00, rec=0x7f45c1af409a "\200", index=0x7f4520023b68,
offsets=0x7f452db6ffc0, mode=<value optimized out>, gap_mode=512, thr=0x7f452000dd68)
at storage/innobase/lock/lock0lock.c:5477
#2 0x0000000000856137 in sel_set_rec_lock (block=0x7f4543967d00, rec=0x7f45c1af409a "\200", index=0x7f4520023b68, offsets=0x7f452db6ffc0,
mode=<value optimized out>, type=<value optimized out>, thr=0x7f452000dd68)
at storage/innobase/row/row0sel.c:1007
#3 0x000000000085a681 in row_search_for_mysql (buf=0x7f452000bf20 "\361\v", mode=2, prebuilt=<value optimized out>, match_mode=1, direction=<value optimized out>)
at storage/innobase/row/row0sel.c:4207
#4 0x000000000083211e in ha_innobase::general_fetch (this=0x7f45200085d0, buf=0x7f452000bf20 "\361\v", direction=1, match_mode=1)
at storage/innobase/handler/ha_innodb.cc:6730
#5 0x000000000068a68d in handler::read_multi_range_next (this=0x7f45200085d0, found_range_p=0x7f452db70418)
at sql/handler.cc:4443
#6 0x00000000007450a1 in QUICK_RANGE_SELECT::get_next (this=0x7f4520023370) at sql/opt_range.cc:8684
#7 0x000000000075f12b in rr_quick (info=0x7f452db70700) at sql/records.cc:344
#8 0x00000000005f7b36 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>,
order=0x7f452db70700, limit=18446744073709551614, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
at sql/sql_update.cc:644
总结
在InnoDB中，通过二级索引更新记录，首先会在WHERE条件使用到的二级索引上加Next-key类型的X锁，以防止查找记录期间的其它插入/删除记录，然后通过二级索引找到primary key并在primary key上加Record类型的X锁（之所以不是Next-key，是因为查询条件是二级索引，若WHERE条件使用到的是primary key，就会上Next-key类型的X锁），之后更新记录并检查更新字段是否是其它索引中的某列，如果存在这样的索引，通过update的旧值到二级索引中删除相应的entry，此时x锁类型为Record