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

实验数据

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

posted @ 2024-01-10 16:29 caibaotimes 阅读(185) 评论(0) 编辑收藏举报

刷新页面返回顶部

caibaotimes