InnoDB delete-update加锁流程分析

死锁#

原因：并发事务在执行过程中，因争夺锁资源而造成互相等待。

加锁顺序导致死锁：不同表加锁顺序相反、相同表不同行加锁顺序相反，其中相同表不同行加锁顺序相反造成死锁有很多变种，其中容易忽略的是给辅助索引行加锁的时候，同时会给聚集索引行加锁；同时还可能出现在外键索引时，给父表加锁，同时隐含给子表加锁；触发器同样如此，这些都需要视情况分析；

解决办法：调整加锁顺序

锁冲突死锁：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加锁顺序相反，导致死锁。

实验数据#

Copy Highlighter-hljs
mysql> show create table t\G*************************** 1. row ***************************       Table: tCreate 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=InnoDB1 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语句：#

Copy Highlighter-hljs
update t set a=a+1 where b=93;

read阶段1：#

row_search_for_mysql，对找到的二级索引记录加 LOCK_X（LOCK_ORDINARY）锁（index->name=idx_b）

Copy Highlighter-hljs
#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）

Copy Highlighter-hljs
#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，更新主键索引记录

Copy Highlighter-hljs
#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）

Copy Highlighter-hljs
#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）

Copy Highlighter-hljs
#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）

Copy Highlighter-hljs
#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