活久见 MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

竟然遇到了 数据库的死锁: 

2022-11-05 23:51:18.298 | ERROR 30116 | http-nio-48080-exec-9 [TID: N/A] c.i.y.f.w.c.h.GlobalExceptionHandler     | [defaultExceptionHandler]

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in cn/iocoder/yudao/module/bpm/dal/mysql/task/BpmProcessInstanceExtMapper.java (best guess)
### The error may involve cn.iocoder.yudao.module.bpm.dal.mysql.task.BpmProcessInstanceExtMapper.update-Inline
### The error occurred while setting parameters
### SQL: UPDATE bpm_process_instance_ext SET process_instance_id = ?, form_variables = ?, update_time = ?, updater = ? WHERE bpm_process_instance_ext.tenant_id = 1 AND deleted = 0 AND (process_instance_id = ?)
### 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
  at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
  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)
  at com.sun.proxy.$Proxy136.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)
  at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
  at com.sun.proxy.$Proxy339.update(Unknown Source)
  at cn.iocoder.yudao.module.bpm.dal.mysql.task.BpmProcessInstanceExtMapper.updateByProcessInstanceId(BpmProcessInstanceExtMapper.java:30)
  at java.lang.invoke.MethodHandle.invokeWithArguments(MethodHandle.java:627)
  at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$DefaultMethodInvoker.invoke(MybatisMapperProxy.java:162)
  at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
  at com.sun.proxy.$Proxy339.updateByProcessInstanceId(Unknown Source)
  at cn.iocoder.yudao.module.bpm.service.task.BpmProcessInstanceServiceImpl.createProcessInstance0(BpmProcessInstanceServiceImpl.java:397)
  at cn.iocoder.yudao.module.bpm.service.task.BpmProcessInstanceServiceImpl.createProcessInstance(BpmProcessInstanceServiceImpl.java:128)
  at cn.iocoder.yudao.module.bpm.service.task.BpmProcessInstanceServiceImpl$$FastClassBySpringCGLIB$$670b387.invoke(<generated>)
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
  at cn.iocoder.yudao.module.bpm.service.task.BpmProcessInstanceServiceImpl$$EnhancerBySpringCGLIB$$d35ab061.createProcessInstance(<generated>)
  at cn.iocoder.yudao.module.bpm.controller.admin.task.BpmProcessInstanceController.createProcessInstance(BpmProcessInstanceController.java:46)

哦, 仔细看, 其实并不是死锁, 而是获取锁超时, 为什么呢? 

难道是我对 bpm_process_instance_ext表还有其他的锁操作, 导致了 我无法再次对他update ?

show full processlist -- where db like 'ruoyi-vue-pro';
kill 2555;
show open tables where in_use>0;
select * from information_schema.innodb_locks; 
select * from information_schema.innodb_lock_waits;

发现有一个进程2555 指向了一个query sql , 然后一直是starting状态, 为什么呢? 难道无法执行query? 按理说一般的查询是不需要锁的吧

kill 2555 之后, 还是不行啊.. why ?

参考 

​​https://www.cnblogs.com/christopherchan/p/12390819.html​​

​​https://blog.csdn.net/yulang1992514/article/details/117756550​​

难道是我的db editor 中存在未提交的事务? 

难道是sql中有中文?  难道是网络不太好的原因?  好像有点道理, 手机打开头条的视频图片感觉确实很慢啊.. 有一些卡啊..

不知道, 搞了很久搞不定,  睡觉了先.

第二天10点才起床, 洗漱完毕后打开电脑重试, 发现竟然可以了...  神奇吧?