工作中遇到的数据库死锁问题 - 排查方案 CannotAcquireLockException

问题·重现

正常情况下，销售人员在使用我们的销售系统为客户创建订单时候，后台java代码就会开启事务，然后往数据库中添加订单信息和订单详情信息，以及一些其他业务操作。但由于某次异常操作(可能是网络或者其他的原因)，导出系统出现问题无法工作。

于是进行代码调试，发现在向数据库中插入数据的时候，一直卡在添加数据这个方法上，等待了很长一段时间，一直无法响应。在经过一段时间后，通过try…catch…捕获了 org.springframework.dao.CannotAcquireLockException异常，如图。

后台代码展示，就是在orderGoodsService.addOrderGoods这方法一直卡住。

后台打印处理的错误日志如下：

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in file [D:\Idea workspace\bussiness\target\classes\mapper\order\OrderGoodsMapper.xml]
### The error may involve com.dwnest.business.dao.order.OrderGoodsMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into order_goods (id, goods_id, consumer_id,       coupon_id, discount_scale,       product_name, sku_id, specification, sku_pic_url, item_number,       main_material, auxiliary_material, out_length,        out_wide, out_high, cycle_date1,        cycle_date2, nums, sale_price,        discount_price, total_price, status,        `explain`, store_id, create_time,       create_by, update_time, update_by,change_subtract       )     values (?, ?, ?,       ?, ?,       ?, ?, ?,       ?, ?, ?, ?,       ?,       ?, ?, ?,        ?, ?, ?,        ?, ?, ?,        ?, ?, ?,        ?, ?, ?,?       )
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

然后后台报出异常，前台提示订单创建失败：

问题·分析

虽然后台在try…catch的时候报出了org.springframework.dao.CannotAcquireLockException异常，但是在打印出日志的时候，显示的是com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction。提示的是Lock wait timeout exceeded(锁等待超时)；try restarting transaction (尝试开启新事务)；

就是说，在我们开启事务插入数据的时候一直未获取到锁，等待超时了。所有现在的问题就是查看为什么无法获取到锁。

问题·排除

这里可以确定问题是出现于数据库中，要从数据库中进行排查。

由于当前请求获取不到锁，可以能有以下情况：

1. 数据库是否发生表锁，整张表被锁定，也就无法往里插入数据了。通过以下命令查询是否表锁，然后得到的结果为空，表示没有发生表锁，这个情况排除；

show open table where in_use > 0; //查询是否表锁

1. 数据库中该表是否有事务抢占了该锁，导致该请求无法获取锁，然后一直等待，等待时间超过数据库设置的默认时间就会失败。通过以下命令查询是否有事务被锁住，果然，查到了有一条记录，从2020-05-09 15:03开始的，状态时(RUNNING)运行，线程id为428847。

select * from information_schema.innodb_trx // 查询运行的事务，是否被锁住

虽然查询出一条记录，但是无法确定是否时该条数据导致的，于是再次发生插入订单数据请求，在orderGoodsService.addOrderGoods这个方法卡住的时候，查看数据库的select * from information_schema.innodb_trx这条命令，得到两条结果。第一条结果是刚添加的，状态时Lock WAIT锁等待，接下来的情况就是一直无法获取锁，最后会执行失败。原因就是第二条结果是导致的。所以可以确定就是线程id为428847，这个结果导致的。

好家伙，从下午3点，一直卡到5点，还在运行，一直持有锁不放，导致当前插入数据失败。

问题·解决

由于之前的某种原因，导致事务一直卡住，所以只需要把之前的事务杀死就可以了，

kill id; // id为线程id，使用该命令就可以杀死某条线程

然后查看数据库的select * from information_schema.innodb_trx就会发现结果为空，在调用插入订单的时候，就成功了。这个问题就解决了。

其他命令

select * from information_schema.innodb_locks // 查看当前锁定的事务
select * from information_schema.innodb_locks_waits //查看当前等待锁的事务

记录一下开发中存在的问题。