记一次数据库死锁问题发现与解决过程

​在公司做saas商城中，用户下单中，遇跟新表状态导致数据库死锁产生,订单状态更新失败,结果导致用户支付成功,订单展示未支付,实际是用户支付成功,而订单状态未能跟新未支付成功状态.

graph TD
A[用户支付] --> B[微信支付]
B --> C[异步回调]
C --> D{通知支付是否成功}
D --> E[修改订单状态]

查询出现问题的原因,查找生产日志,

org.springframework.dao.DeadlockLoserDataAccessException:

Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

The error may exist in com/duobaoyu/dby/mall/databean/dal/mysql/order/OrderMapper.java (best guess)

The error may involve com.duobaoyu.dby.mall.databean.dal.mysql.order.OrderMapper.update-Inline

The error occurred while setting parameters

SQL: UPDATE dby_order SET status=?, payed=?, pay_time=?, transaction_id=?, update_time=?, updater=? WHERE deleted=0 AND (order_no = ?)

Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)atcom.sun.proxy.$Proxy145.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:64)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)atcom.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)atcom.sun.proxy.$Proxy171.update(Unknown Source)
at com.duobaoyu.dby.module.mall.service.order.impl.OrderServiceImpl.updateOrderByOrderNo(OrderServiceImpl.java:122)
at com.duobaoyu.dby.module.mall.service.order.impl.OrderServiceImpl$$FastClassBySpringCGLIB$$76329b9e.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)atorg.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
整理除简易关键信息

Deadlock found when trying to get lock; try restarting transaction

试图获取锁时发现死锁；尝试重新启动事务

尝试从数据库层面出发,找到数据库当时日志产生原因.

可以使用SHOW ENGINE INNODB STATUS命令来查看死锁信息，分析死锁产生的原因。

分析死锁日志，找到具体涉及到的表和事务，确定死锁产生的关键原因

---

LATEST DETECTED DEADLOCK

2023-04-18 12:06:03 139990029825792
*** (1) TRANSACTION:
TRANSACTION 156944871, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 16 row lock(s), undo log entries 2
MySQL thread id 7739481, OS thread handle 139988366042880, query id 108771341 113.125.2.227 root updating
UPDATE dby_order SET status=3,
payed=1,
pay_time=‘2023-04-18 12:06:02.479’,
transaction_id=‘4200001842202304185871292083’,
update_time=‘2023-04-18 12:06:02.479’,
WHERE deleted=0
AND (order_no = 1648175966147006464)
​
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 3151 page no 4 n bits 96 index PRIMARY of table dby_mall_demo_1.0.0.dby_order trx id 156944871 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 37; compact format; info bits 0
0: len 8; hex 8000000000000009; asc ;;
1: len 6; hex 0000095aa550; asc Z P;;
2: len 7; hex 020000020c1859; asc Y;;
3: len 19; hex 31363438313732383739383237313238333230; asc 1648172879827128320;;
4: len 22; hex 31343131363438313732383831383239343337343431; asc 1411648172881829437441;;
5: len 0; hex ; asc ;;
6: len 8; hex 8000000000000002; asc ;;
7: SQL NULL;
8: len 6; hex 636f75706f6e; asc coupon;;
9: len 4; hex 80000002; asc ;;
10: len 8; hex 8000000000000001; asc ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3151 page no 4 n bits 96 index PRIMARY of table dby_mall_demo_1.0.0.dby_order trx id 156944871 lock_mode X waiting
Record lock, heap no 24 PHYSICAL RECORD: n_fields 37; compact format; info bits 0
0: len 8; hex 8000000000000020; asc ;;
1: len 6; hex 0000095ac995; asc Z ;;
2: len 7; hex 81000008800121; asc !;;
3: len 19; hex 31363438313736303236353738353338343936; asc 1648176026578538496;;
4: SQL NULL;
5: len 0; hex ; asc ;;
6: len 8; hex 8000000000000009; asc ;;
7: SQL NULL;
8: len 6; hex 636f75706f6e; asc coupon;;
9: len 4; hex 80000001; asc ;;
10: len 8; hex 8000000000000001; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 1; hex 00; asc ;;
13: SQL NULL;
这个是事物一获取死锁失败,下面是事物二获取死锁失败,由于都是同一张表获取资源信息,导致死锁产生

*** (2) TRANSACTION:
TRANSACTION 156944789, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 6
MySQL thread id 7730543, OS thread handle 139988096612096, query id 108771441 113.125.2.227 root updating
UPDATE dby_order SET channel_order_no=‘1411648176031256813570’,
status=2,
update_time=‘2023-04-18 12:06:02.638’,
updater=‘9’
WHERE deleted=0
AND (order_no = 1648176026578538496)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3151 page no 4 n bits 96 index PRIMARY of table dby_mall_demo_1.0.0.dby_order trx id 156944789 lock_mode X locks rec but not gap
Record lock, heap no 24 PHYSICAL RECORD: n_fields 37; compact format; info bits 0
0: len 8; hex 8000000000000020; asc ;;
1: len 6; hex 0000095ac995; asc Z ;;
2: len 7; hex 81000008800121; asc !;;
3: len 19; hex 31363438313736303236353738353338343936; asc 1648176026578538496;;
4: SQL NULL;
5: len 0; hex ; asc ;;
6: len 8; hex 8000000000000009; asc ;;
7: SQL NULL;
8: len 6; hex 636f75706f6e; asc coupon;;
9: len 4; hex 80000001; asc ;;
10: len 8; hex 8000000000000001; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 1; hex 00; asc ;;
13: SQL NULL;
14: len 4; hex 80000001; asc ;;
15: SQL NULL;
16: SQL NULL;
17: SQL NULL;
18: len 1; hex 81; asc ;;
19: SQL NULL;
20: len 1; hex 39; asc 9;;
21: len 5; hex 99afe4c181; asc ;;
22: len 1; hex 39; asc 9;;
23: len 5; hex 99afe4c181; asc ;;
24: len 1; hex 00; asc ;;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: len 2; hex 7b7d; asc {};;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3151 page no 4 n bits 96 index PRIMARY of table dby_mall_demo_1.0.0.dby_order trx id 156944789 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 37; compact format; info bits 0
0: len 8; hex 8000000000000009; asc ;;
1: len 6; hex 0000095aa550; asc Z P;;
2: len 7; hex 020000020c1859; asc Y;;
3: len 19; hex 31363438313732383739383237313238333230; asc 1648172879827128320;;
4: len 22; hex 31343131363438313732383831383239343337343431; asc 1411648172881829437441;;
5: len 0; hex ; asc ;;
6: len 8; hex 8000000000000002; asc ;;
7: SQL NULL;
8: len 6; hex 636f75706f6e; asc coupon;;
9: len 4; hex 80000002; asc ;;
10: len 8; hex 8000000000000001; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 1; hex 00; asc ;;
13: SQL NULL;
14: len 4; hex 80000001; asc ;;
15: SQL NULL;
16: SQL NULL;
17: SQL NULL;
18: len 1; hex 81; asc ;;
19: SQL NULL;
20: len 1; hex 32; asc 2;;
21: len 5; hex 99afe4bd5f; asc _;;
22: len 1; hex 32; asc 2;;
23: len 5; hex 99afe4bd5f; asc _;;
24: len 1; hex 00; asc ;;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: len 2; hex 7b7d; asc {};;
​
*** WE ROLL BACK TRANSACTION (1)

TRANSACTIONS
解决方案,由于同一时间更新一个表,跟新的字段没有使用到索引的时候,会把默认锁住当前表,在更新的字段上加上索引,只会锁住当前行,也就是实现行锁.

预防措施

1 定期检查和优化数据库性能，例如索引优化，查询语句优化等。

2 避免长时间的事务操作，尽量将事务操作时间控制在较短的范围内。

3 对于高并发场景，考虑使用分布式锁等技术来避免死锁的发生。

4 对于异步回调等场景，确保回调接口的高可用性和稳定性，避免回调接口出现异常导致死锁的发生。

总结

在生产环境中，死锁是一个常见的问题，解决死锁需要综合考虑数据库和应用程序两个方面。在预防死锁的发生时，需要对数据库性能进行优化，

以及在应用程序中添加死锁检测和处理机制。同时，对于高并发场景，需要使用分布式锁等技术来避免死锁的发生。