线上遇到一起死锁问题,一条DELETE语句与一条UPDATE语句产生了死锁,经过和印风的讨论分析找到原因:DELETE语句通过二级索引删除记录,加锁顺序:二级索引(WHERE使用到二级索引)–>主键索引 –> 所有其它二级索引,UPDATE语句的加锁顺序:二级索引(WHERE条件使用二级索引)–>主键索引 –>包含更新字段的其它二级索引,由于DELETE操作更新了UPDATE语句WHERE条件使用到的索引,这导致DELETE与UPDATE加锁顺序相反,导致死锁
为了进一步研究InnoDB update操作加锁流程,进行了下列实验(update操作为inplace-update),关于mysql update的上层调用可以参考: MySQL update语法SQL解析源码分析 和 Oracle/PostgreSQL UPDATE…RETURNING…在MySQL中的实现
实验数据
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 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