jdbc mysql innodb 死锁 deadlock_数据库技术之记一次排查DB死锁的分析与思考

文章摘要：在线上环境遇到数据库死锁问题该如何分析并解决问题呢？

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念，但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到的一个死锁Case说起，首先还原出现死锁的现场和条件，并结合排查业务应用工程日志、MySQL数据库状态信息等方式，同时给出MySQL锁的基本概念，再通过阅读日志深入定位并分析出现死锁的原因，最后讲下MySQL InnoDB的加锁原理以及如降低死锁发生的机率。

一、出现死锁的当前场景

在测试环境上做业务流程的联调验证自测，在跑自测用例时，突然发现在多线程并发情况下有数据未从业务表中删除完成，通过Spring Boot工程打印出的Log日志中可以看到出现了死锁问题。下面将先给大家还原下死锁的当前场景，然后逐步分析和使用正确方法排查死锁的原因。

1、数据库表结构

CREATETABLE `hw_band_width_bill_record` (

`ID` bigint(20) unsigned NOT NULLAUTO_INCREMENT COMMENT '主键id,自增',

`CUSTOMER_ID` varchar(50) NOT NULL COMMENT ……,

`USER_ID` varchar(50) NOT NULL COMMENT ……,

`CLOUD_SER_TYPE_CODE` varchar(50) NOT NULL COMMENT……,

`RES_TYPE_CODE` varchar(50) NOT NULL COMMENT ……,

`RES_SPEC_CODE` varchar(50) NOT NULL COMMENT ……,

`RES_INSTANCE_ID` varchar(50) DEFAULT NULL COMMENT……,

`RES_ATTR_VALUES` varchar(50) DEFAULT NULLCOMMENT ……,

(限于篇幅问题这里省略该数据表的其他字段)

PRIMARY KEY (`ID`),

KEY`custId_product_res_type_spec_index`(`CUSTOMER_ID`,`RES_TYPE_CODE`,`RES_SPEC_CODE`,`RES_ATTR_VALUES`) USING BTREE)

ENGINE=InnoDBAUTO_INCREMENT=54 DEFAULT CHARSET=utf8 COMMENT='……'

其中，`ID`为主键索引，`CUSTOMER_ID`,`RES_TYPE_CODE`,`RES_SPEC_CODE`,`RES_ATTR_VALUES`等字段组成了非唯一的普通BTREE索引。

2、业务库的事务隔离级别

可以通过“SELECT @@tx_isolation”的SQL来查询当前数据库的事务隔离级别。

mysql>SELECT @@tx_isolation;

+-----------------+

|@@tx_isolation|

+-----------------+

|REPEATABLE-READ |

+-----------------+

1row in set (0.00 sec)

3、业务应用工程的Log日志

当业务应用工程出现异常或者报错时，绝大部分童鞋的第一反应肯定都是去工程对应的Log日志里面去排查定位问题。对应于该死锁问题Case的工程Log日志如下：org.springframework.dao.DeadlockLoserDataAccessException:###Error updating database.Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to get lock; try restarting transaction###The error may involveHwBandWidthBillRecordMapper.deleteResBwBillsByIdAndSpecValues-Inline

###The error occurred while setting parameters

###SQL: delete from hw_band_width_bill_recordwhere CUSTOMER_ID = ?and CLOUD_SER_TYPE_CODE = ?and RES_TYPE_CODE = ?and RES_SPEC_CODE = ?and RES_ATTR_VALUES = ?

###Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to get lock; try restarting transaction; SQL []; Deadlock

found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock;try restarting transaction ………(限于篇幅问题这里省略了部分日志)Cause