MySQL笔记-死锁原理与分析及InnoDB中如何减少死锁

根据InnoDB的加锁规则（Record Lock、Gap Lock、meta data lock）可以写出不会发生死锁的SQL语句，也能定位出产生死锁的原因。

 

死锁产生的原因：

产生回路：两个或两个以上的事务在执行过程中，分别持有一把锁，然后再加一把锁（AB-BA）产生死锁。

加锁顺序不一致：两个或两个以上的事务并发执行（同一时刻），因争夺资源而造成的一种互相等待，产生死锁。

 

如下时序图为产生环路：

update操作会参数排他锁。

这里session1把id为1的行加了锁，session2，把ID为2的行加了锁，随后，session1拿id为2的数据，而session2拿id为1的数据，这样就产生了死锁。

下面用mysql来演示下：

表如下：

create table t1( id int not null default 0, name varchar(10), primary key(id) )engine=InnoDB;

查看下：

select * from t1;

这样有产生了死锁：

mysql有解锁机制，这里可以看到产生了Deadlock。然后将其释放了，这样session1就运行成功了！

使用

show engine innodb status \G

可以查看锁相关的信息；

 

在latest detected deadlock。如看到update t1 est name = 'ddddd' where id = 2这个地方产生了死锁。

innodb_print_all_deadlocks开启后可以将死锁信息添加到error.log里面

show variables like '%dead%';

目前是OFF状态。

 

第二种情况产生死锁是在同一时刻。如下时序图：

这里如果session1成功将检索到数据和session2的数据一样，但是顺序不同，就会参数死锁。

如session1获得的数据：

session2检索的数据：

其实就变成了和第一种差不多的形式。

下面来说明下InnoDB中如何减少死锁：

1. 自动死锁检测，优先回滚小事务；

2. 超时设置（参数innodb_lock_wait_timeout）;

3. 尽快提交事务，小事务不容易发生死锁；

4. 加for update，lock in share mode读锁时，最好降低事务隔离级别，例如使用RC（读已提交），降低死锁发生概率

5. 事务中涉及多个表，或多行时，每个事务操作顺序要保持一致，最好用存储过程/函数固化；

6. 通过索引等方式优化SQL效率，降低死锁发生概率（减少扫描/锁范围，降低概率；）