一个MySQL死锁的问题分析

两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。一般不需要人为介入,这种方法简单有效,在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。
表结构

create table `t` (`ID` int(11) not null AUTO_INCREMENT,`NAME` varchar(100) DEFAULT NULL,`AGE` int(11) DEFAULT NULL,`email` varchar(256) DEFAULT NULL,PRIMARY KEY (`ID`),KEY `ind_name` (`NAME`),KEY `ind_email` (`email`)) ENGINE=InnoDB;

数据

Insert into t(name,age,email) values('aaa',20,'aaa@mail.com');Insert into t(name,age,email) values('bbb',20,'bbb@mail.com');Insert into t(name,age,email) values('ccc',20,'ccc@mail.com');

两个并发sql

线程1: update t set age=30 where name='aaa';

线程2: delete t where email='bbb@mail.com';

死锁case

线程1等ind_name,线程2等Primary

Question

1.update/delete操作的流程?

答:update/delete操作,在数据库中,会被拆分为两步。第一步是当前读,读取满足条件的记录,并加锁;第二步真正的进行update/delete,根据读取到的记录,进行相应的更新或者是删除。

注意:

一般情况下,读取与更新/删除是交替进行的,先读取满足条件的一条记录,加锁,更新这条记录,然后再读取下一条满足条件的记录,加锁,并更新,直至读取到第一条不满足条件的记录为止。

2. update操作会加那些锁?

答:针对以上的update操作,采用的是读取一条,更新一条的处理流程。读取走的是ind_name索引,更新记录需要锁住记录。因此更新一条记录的加锁流程为:Ind_name索引加数据锁—>Primary索引加数据锁,若是RR隔离级别,ind_name索引记录前还需要加Gap锁(但Gap锁在此处不是造成死锁的原因)。

3. delete操作会加哪些锁?

答:针对以上的delete操作,scan(扫描)走的是ind_email索引。scan过程需要加上ind_email索引上的数据锁(RR隔离级别下需要加Gap锁),Primary索引上的数据锁;delete过程,还需要操作ind_name索引,因此还需要加上ind_name索引上的数据锁。

Ind_email索引加数据锁—>Primary索引加数据锁—>ind_name索引加数据锁

4. 死锁case产生的原因是什么?

答:分析2,3可以发现update操作的加锁顺序为ind_name—>primary,delete操作的加锁顺序为ind_email—>primary—>ind_name,加锁的顺序正好相反,在并发执行下,极有可能产生死锁。

5. 以上两条并发sql,会有多少种死锁可能性?

答:除了示例中的死锁外,以上update/delete并发操作,还有可能产生primary索引上的死锁:因为通过两个索引访问主键的顺序是不一致的,对于两条记录,如果恰好以相反的顺序范围primary索引,即可能产生主键上的死锁。

6. 如何尽可能避免死锁?

  • 以固定的顺序访问表和行。简单方法是对id列表先排序,后执行,这样就避免了交叉等待锁的情形
  • 大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
  • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
  • 降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
  • 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。

关于INNODB LOCK前两篇文章有介绍

MySQL InnoDB Lock(一)

MySQL InnoDB Lock(二)

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-10-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

扫码关注云+社区