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

原创

天将降大任于斯人也 2022-11-06 10:56:48 博主文章分类：java ©著作权

文章标签 数据库锁 mysql 锁超时 文章分类 MySQL 数据库 指尖人生

©著作权归作者所有：来自51CTO博客作者天将降大任于斯人也的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

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? 按理说一般的查询是不需要锁的吧

活久见 MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction_锁超时