By

文章目录

最近在进行系统压测时，产生了mysql死锁的问题。本文就来分析产生死锁的原因。

要分析产生死锁的原因，需要查看mysql的相关日志信息。由于show engine innodb status输出的信息较多，在屏幕中看不到所有的输出信息，因此先设置输出日志到文档中，然后执行日志输出：

1
mysql:pager cat - >>  ~/tzwang/mysqllog.txt；
mysql:show engine innodb status;

在产生死锁的事务中，有两条SQL(下文SQL经过脱敏处理)：

1
2
3
4
5
6
7
语句1：
INSERT tbl_log(buss_seq,oper_usr_nm,crt_usr SELECT t.buss_seq, 'tony' as oper_usr_nm,
'tony' as crt_usr FROM tbl_form_dtl t where t.form_no = ? AND t.buss_st= ? AND t.itct_st= ?

语句2：
update tbl_form_dtl a set a.BUSS_ST= '1',a.UPDATE_TM=now(),a.UPDATE_USR = 'tony'  
where a.form_no= ?  AND a.buss_st= ? AND a.itct_st= ?

其中tbl_form_dtl表的主键为id,在form_no字段有二级索引。

通过explain命令查看语句1，发现fromtbl_form_dtl t走了二级索引(form_no)，如下图所示：
explain语句1结果
由于事务隔离级别是可重复读级别，且索引是二级索引，所以上述语句1在锁住二级索引的行锁、GAP LOCK外，还会锁住对应的聚簇索引。

通过explain命令查看语句2，发现语句2走的是聚簇索引,如下图所示：
explain 语句2结果
按理来说，语句2是可以走form_no字段的索引的，但是mysql会自动优化，由于该条sql的结果占表数据总量比较大，sql就将其优化成走聚簇索引（之所以优化成走聚簇索引，应该是因为二级索引在查询到后，还得再到聚簇索引中update数据）。经测试发现，当将表中的数据增大，使得语句2的覆盖范围占整个表的数据量比例较小时，就走了二级索引。

分析mysql的日志:

RECORD LOCKS space id 439657 page no 12 n bits 120 index PRIMARY of table databasename.tbl_form_dtl trx id 54562223 lock_mode X waiting

RECORD LOCKS space id 439657 page no 12 n bits 120 index PRIMARY of table databasename.tbl_form_dtl trx id 54562222 lock_mode X

RECORD LOCKS space id 439657 page no 2317 n bits 120 index PRIMARY of table databasename.tbl_form_dtl trx id 54562222 lock_mode X waiting

产生死锁的原因：
事务54562222在执行语句2时，占有了page no =12的X锁，要获取page no=2317的X锁，而该锁已被事务54562223锁住；
事务54562223在执行语句1时，在锁住二级索引时，也获取了page no = 2317的S锁，然后执行语句2，要获取page no = 12的锁，而该锁已被事务54562222锁住。

如下图所示，事务1在执行语句1时查找二级索引(form_no)，锁住绿色的部分，包括二级索引的Record lock和GAP LOCK，聚簇索引的Record Lock(因为聚簇索引是unique的，所以不用锁GAP LOCK)；此时事务2也在执行语句1，锁住了青色的部分，也包括Record和GAP LOCK,及聚簇索引的Record Lock。然后事务1和事务2各自执行语句2，由于语句2走了聚簇索引，所以要遍历聚簇索引；在mysql的innodb引擎中，由于where条件不是id，因此走聚簇索引需要锁住各个行，不过mysql有优化，在返回到服务器端后，mysql会根据where条件释放不必要的锁，不过在返回服务器端前还是要先加锁的。此时事务1需要等待id=4的锁，id=4已经被事务2锁住；而事务2需要等待id=1的锁，而id=1的锁已经被事务1锁住，因此就造成了死锁。
加锁索引图

mysql在检测到发生了死锁后，会释放一个事务的锁，另一个事务就能获得锁，此时就会报死锁异常，在mysql的相关日志中可以看到LATEST DETECTED DEADLOCK的相关记录，通过这部分记录就可以分析出死锁产生的原因。

造成死锁的原因一般是加锁的顺序不一致导致的问题。数据库大神何登成的mysql加锁处理分析一文分析了mysql加锁的方式，以及死锁的一些场景，写的简单易懂，值得一看。

文章目录