一个MySQL死锁的问题分析

MySQL轻松学

发布于 2018-03-09 15:19:14

1.8K0

发布于 2018-03-09 15:19:14

文章被收录于专栏：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. 如何尽可能避免死锁？