一个MySQL死锁的问题分析
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前两篇文章有介绍