ITPub博客

首页 > 数据库 > MySQL > 死锁案例分析

死锁案例分析

原创 MySQL 作者:贺子_DBA时代 时间:2021-01-16 19:04:51 0 删除 编辑
错误日志中关于此次死锁的记录如下,显示的是两个update语句产生了死锁!
LATEST DETECTED DEADLOCK
------------------------
2021-01-15 18:12:40 7f9c01daf700
*** (1) TRANSACTION:
TRANSACTION 16175364313, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 149648015, OS thread handle 0x7fa255658700, query id 24131125938 10.1.1.1 app_test  updating
update image_tmp_liu
SET handle_status = '0'
handle_describe = '系统异常'
where file_name = '2021011110194000010000000000170020723568.htm'
and file_type = 'LOAN_AUTHZ'
and ins_date = '20210115'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6966 page no 1747854 n bits 472 index `idx_ins_date` of table `my_jiebei`.`image_tmp` trx id 16175364313 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 16175364307, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
9 lock struct(s), heap size 1184, 17 row lock(s), undo log entries 1
MySQL thread id 149645724, OS thread handle 0x7f9c01daf700, query id 24131125920 10.1.1.1 app_test  updating
update image_tmp_liu
SET handle_status = '1',
handle_describe = '成功'
where file_name = '2021010710194000010000000000950019899845.htm'
and file_type = 'LOAN_AUTHZ'
and ins_date = '20210115'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6966 page no 1747854 n bits 472 index `idx_ins_date` of table `my_jiebei`.`image_tmp` trx id 16175364307 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6966 page no 1345962 n bits 144 index `PRIMARY` of table `my_jiebei`.`image_tmp` trx id 16175364307 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
image_tmp_liu表的 ins_date列上是索引
一:产生死锁的原因:
可以参考实战45讲的第21讲
加锁的规则如下:
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
根据原则2 查询过程中访问到的对象才会加锁,(给索引加锁是给索引列上加锁,不是给整行数据加锁,也就是只有访问到的对象才会加锁)。事务1更新的时候,通过索引 ins_date来查到主键值,然后回表通过主键来找到 handle_status和handle_describe的值,访问到主键了,所以会给主键加锁,然而主键也就是整行数据了(mysql innodb表是聚簇表,特别像oracle索引组织表),会尝试给所有 ins_date = '20210115'的主键加x锁,但是这个加锁过程是一步一步的加的,并不是一次性加上的,又因为ins_date = '20210115'这个有多行数据(15行)。然后事务2也是会加这几行的主键,同时又因为这两个事务加锁的步骤不通,也就是事务1加了部分的ins_date = '20210115'(例如主键为1 和2 的加上了x锁)事务2加上部分ins_date = '20210115'(例如主键为3 和4加上x锁 ),这样当事务1又想去加主键为3 和4的数据,就需要等待,同理事务2也需要等待!
二: 可以借助mysql 5.6的MRR特性来缓解此类的死锁问题:
2.1 mrr缓解此案例死锁的原理:
这里的关键点在于回表访问主键是一行一行的去回表的,所以给那需要加锁的行加主键x锁也不是原子的,而是一步一步的加的,如果能把这个回表的速度提升,那锁定主键的时间便会降低,那产生死锁的概率就会降低了,
MRR的优化在于, 并不是每次通过辅助索引就回表去取记录而是将其rowid主键给缓存起来,然后对rowid主键进行排序后,再去访问记录,这样就能将随机I/O转化为顺序I/O,从而大幅地提升性能。这个过程如下所示:
2.2 开启MRR的新特性
然而,在MySQL当前版本中,基于成本的算法过于保守,导致大部分情况下优化器都不会选择MRR特性。为了确保优化器使用mrr特性,请执行下面的SQL语句:
set optimizer_switch='mrr=on,mrr_cost_based=off';



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2750536/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
记录工作中遇到的问题,积少成多,坚持就是胜利,工作经历:曾就职于国美、中国采购与招标网、目前就职于一家正规消费金融公司

注册时间:2014-05-12

  • 博文量
    255
  • 访问量
    1810971