线上报错了
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
分析
- 数据库有异常。查看MySQL在报错期间的指标,发现期间无异常。 排除
- 看了日志,发现报错接口所调用的更新数据库的api,有另外一个服务也在调用,且是报错服务依赖的前置服务。
如果下图所示:
这个服务这个执行时间比较,平均执行10min+
并且这个任务在入口service上加个事务,如果任务不全部执行完成,就不会将变更提交到MySQL
猜测这个报错与这个数据库大事务有关
复现 设计case
case:
- 需要模拟一个耗时的服务。
- 这个服务要执行更新某条记录的操作
- 这个服务要有数据库事务
- 在服务全部完成后,再提交事务
- 需要1.中服务未结束前,也要更新同一条记录。 期望在执行期间报错
复现
code:
package com.adapter;
import com.business.task.domin.entity.FileTask;
import com.business.task.service.FileTaskService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.concurrent.TimeUnit;
/**
* financial-center
*
* @Auther: cheng.tang
* @Date: 2022/7/3 8:50 PM
* @Description:
*/
@Slf4j
@RestController
public class TestApi {
@Autowired
private FileTaskService fileTaskService;
/**
* 一个要执行5分钟的服务。要先执行,且更新id=1的记录
*
* @throws InterruptedException
*/
@PutMapping("/tasks/5min")
@Transactional(rollbackFor = Exception.class)
public void updateNeed5Min() throws InterruptedException {
long taskId = 1L;
log.info("/tasks/5min 上一个修改人 {}", fileTaskService.getById(taskId).getModifiedBy());
FileTask fileTask = new FileTask();
fileTask.setId(taskId);
fileTask.setModifiedBy("/tasks/5min");
log.info("/tasks/5min 开始获取到数据行锁ById {} ", taskId);
fileTaskService.updateById(fileTask);
log.info("/tasks/5min 成功获取到数据行锁 新的修改人 {} ", fileTaskService.getById(taskId).getModifiedBy());
TimeUnit.MINUTES.sleep(5);
log.info("/tasks/5min 执行完成");
}
/**
* 在/tasks/5min执行未结束前,更新id=1的记录
*
* @param taskId 任务id
*/
@PutMapping("/tasks/{taskId}")
public void updateNeed5Min(@PathVariable Long taskId) {
log.info("/tasks/{taskId} 上一个修改人 {}", fileTaskService.getById(taskId).getModifiedBy());
FileTask fileTask = new FileTask();
fileTask.setId(taskId);
fileTask.setModifiedBy("/tasks/{taskId}");
log.info("/tasks/{taskId} 开始获取到数据行锁ById {} ", taskId);
fileTaskService.updateById(fileTask);
log.info("/tasks/{taskId} 成功获取到数据行锁 新的修改人 {} ", fileTaskService.getById(taskId).getModifiedBy());
}
}
1.一个要执行5分钟的服务。要先执行,且更新id=1的记录
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.selectById - ==> Preparing: SELECT id,task_type,task_status,task_params,complete_percent,file_path,file_type,original_file_name,failed_file_path,failed_reason,start_time,created_by,gmt_create,modified_by,gmt_modified FROM file_task WHERE id=?
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.selectById - ==> Parameters: 1(Long)
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.selectById - <== Total: 1
2022-07-03 21:36:11[http-nio-8080-exec-8] INFO com.test.TestApi - /tasks/5min 上一个修改人 null
2022-07-03 21:36:11[http-nio-8080-exec-8] INFO com.test.TestApi - /tasks/5min 开始获取到数据行锁ById 1
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.updateById - ==> Preparing: UPDATE file_task SET gmt_create=?, modified_by=?, gmt_modified=? WHERE id=?
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.updateById - ==> Parameters: 2022-07-03 21:36:11.798(Timestamp), /tasks/5min(String), 2022-07-03 21:36:11.803(Timestamp), 1(Long)
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.updateById - <== Updates: 1
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.selectById - ==> Preparing: SELECT id,task_type,task_status,task_params,complete_percent,file_path,file_type,original_file_name,failed_file_path,failed_reason,start_time,created_by,gmt_create,modified_by,gmt_modified FROM file_task WHERE id=?
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.selectById - ==> Parameters: 1(Long)
2022-07-03 21:36:11[http-nio-8080-exec-8] DEBUG c.z.f.r.m.FileTaskMapper.selectById - <== Total: 1
2022-07-03 21:36:11[http-nio-8080-exec-8] INFO com.test.TestApi - /tasks/5min 成功获取到数据行锁 新的修改人 /tasks/5min
- 在/tasks/5min执行未结束前,更新id=1的记录
2022-07-03 21:36:50[http-nio-8080-exec-3] DEBUG c.z.f.r.m.FileTaskMapper.selectById - ==> Preparing: SELECT id,task_type,task_status,task_params,complete_percent,file_path,file_type,original_file_name,failed_file_path,failed_reason,start_time,created_by,gmt_create,modified_by,gmt_modified FROM file_task WHERE id=?
2022-07-03 21:36:50[http-nio-8080-exec-3] DEBUG c.z.f.r.m.FileTaskMapper.selectById - ==> Parameters: 1(Long)
2022-07-03 21:36:50[http-nio-8080-exec-3] DEBUG c.z.f.r.m.FileTaskMapper.selectById - <== Total: 1
2022-07-03 21:36:50[http-nio-8080-exec-3] INFO com.test.TestApi - /tasks/{taskId} 上一个修改人 null
2022-07-03 21:36:50[http-nio-8080-exec-3] INFO com.test.TestApi - /tasks/{taskId} 开始获取到数据行锁ById 1
2022-07-03 21:36:50[http-nio-8080-exec-3] DEBUG c.z.f.r.m.FileTaskMapper.updateById - ==> Preparing: UPDATE file_task SET gmt_create=?, modified_by=?, gmt_modified=? WHERE id=?
2022-07-03 21:36:50[http-nio-8080-exec-3] DEBUG c.z.f.r.m.FileTaskMapper.updateById - ==> Parameters: 2022-07-03 21:36:50.154(Timestamp), /tasks/{taskId}(String), 2022-07-03 21:36:50.155(Timestamp), 1(Long)
2022-07-03 21:37:00[http-nio-8080-exec-3] ERROR druid.sql.Statement - {conn-10004, pstmt-20010} execute error. UPDATE file_task SET gmt_create=?,
modified_by=?,
gmt_modified=? WHERE id=?
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:620)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy207.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
at com.sun.proxy.$Proxy205.update(Unknown Source)
at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:54)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
at com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor.intercept(OptimisticLockerInterceptor.java:112)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy204.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy155.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:63)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:62)
at com.sun.proxy.$Proxy156.updateById(Unknown Source)
at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.updateById(ServiceImpl.java:207)
at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl$$FastClassBySpringCGLIB$$76535273.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)
at com.business.service.impl.FileTaskServiceImpl$$EnhancerBySpringCGLIB$$7d475ba9.updateById(<generated>)
at com.test.TestApi.updateNeed5Min(TestApi.java:53)
at com.test.TestApi$$FastClassBySpringCGLIB$$1824b9f9.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.test.TestApi$$EnhancerBySpringCGLIB$$8751a2e6.updateNeed5Min(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
at org.springframework.web.servlet.FrameworkServlet.doPut(FrameworkServlet.java:919)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:750)
2022-07-03 21:37:01[http-nio-8080-exec-3] ERROR c.z.zaf.web.error.DefaultErrorAdvice - 请求处理失败
org.springframework.dao.CannotAcquireLockException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
同样的报错出现了!!!
Fix : 在更新同一条记录时,要尽可能快速提交变更到数据库
就本次的情况,将耗时任务的事务去掉了。
当前场景,虽然一个原子任务会有两次更新数据库,但都是同一条记录按id进行更新。这种情况出错的可能性很小。
即使出错,再触发一次就可以解决,即具备自愈性,系统自身能保持最终一致。
小结
- 在更新操作时,非必要,不要使用事务。
一定要使用事务的场景:有多个操作,如果不同时提交变更,最终数据肯定会不一致。即没有人工干涉,不可能自愈。
- 事务粒度越小越好。
事务期间,会占用一个数据库连接,影响服务的并发。
事务不提交,非当前事务的服务都看不到已经做出的变更。 即同一个时间点,同一条数据在某个时间点有多个状态同时存在,是引发数据不一致的"坏味道"