Springboot：CannotAcquireLockException无法执行MSSQL语句

问：

我得到了一个.下面的方法在一个循环中被调用了 70 多次，它包含许多数据库调用，其中包括一个事务中的读取、插入、更新和删除。每当它进入方法内部时，我都会收到此错误。任何潜在客户将不胜感激。org.springframework.dao.CannotAcquireLockExceptiondeleteSnapshotIfExist

@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public void saveForecastAdjustments(ForecastingAdjustmentType forecastingAdjustmentType,
        String userId, FamilyTreeType familyTreeType, ImpactType impactType,
        DepartmentDivisionWeekKey departmentDivisionWeekKey,
        WeeklyForecastAdjustment weeklyForecastAdjustment,
        TopLineForecastDao snapShotTopLineForecast)
        throws InterruptedException
    {
        log.info("Before fetching forecastingAdjustmentKey ");
        ForecastingAdjustmentKey forecastingAdjustmentKey = forecastingAdjustmentKeyRepositoryWrapper
            .findOrSave(forecastingAdjustmentType, impactType, departmentDivisionWeekKey,
                familyTreeType);
        log.info("forecastingAdjustmentKey key id and departmentDivisionTimePeriod {}, {} : ",
            forecastingAdjustmentKey.getId(), forecastingAdjustmentKey
                .getDepartmentDivisionTimePeriod().getDeptDivisionTimePeriodId());
        TopLineForecastDao topLineForecastDao = toplineForecastService
            .getTopLineForecastDao(departmentDivisionWeekKey);

        if (!ObjectUtils.isEmpty(snapShotTopLineForecast))
        {
            deleteSnapshotIfExist(snapShotTopLineForecast);
        }

        List<ForecastingAdjustmentDao> forecastingAdjustmentDaos = archiveAndAdd(
            forecastingAdjustmentKey, weeklyForecastAdjustment, userId, topLineForecastDao);
        log.info("forecastingAdjustmentDao size which include archived record and new record {}",
            forecastingAdjustmentDaos.size());
        forecastingAdjustmentRepository.saveAll(forecastingAdjustmentDaos);
        // recalculate topline forecast using latest adjustment rate and save it regardless if
        // topline forecast already exist or not
        recomputeAndSaveToplineForcastService.saveRecalculatedToplineForecast(userId,
            departmentDivisionWeekKey, topLineForecastDao);
        log.info(
            "Resaved forecast for Department {}, Division {}, Year {}, Period {} , Week {} while updating adjustment ",
            departmentDivisionWeekKey.getDepartmentCode(),
            departmentDivisionWeekKey.getDivisionId(), departmentDivisionWeekKey.getFiscalYear(),
            departmentDivisionWeekKey.getPeriodNumber(), departmentDivisionWeekKey.getWeek());
    }

private void deleteSnapshotIfExist(TopLineForecastDao findSnapshotTopLineForecast)
    {
        if (findSnapshotTopLineForecast != null)
        {
            deleteAdjustmentsByToplineForecastId(findSnapshotTopLineForecast);
            forecastBreakDownService
                .deleteForecastBucketByToplineForecastId(findSnapshotTopLineForecast);
            impactService.deleteImpactsByToplineForecastId(findSnapshotTopLineForecast);
            toplineForecastService.deleteToplineForecastById(findSnapshotTopLineForecast);
        }
    }

堆栈跟踪：

Exception org.springframework.dao.CannotAcquireLockException could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement
  at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:289)
  at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
  at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
  at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
  at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
  at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
  at com.sun.proxy.$Proxy326.deleteByTopLineForecatId(Unknown Source)
  at com.kroger.ngpp.service.ForecastBreakDownService.deleteForecastBucketByToplineForecastId(ForecastBreakDownService.java:129)
  at com.kroger.ngpp.service.ForecastBreakDownService$$FastClassBySpringCGLIB$$92170da8.invoke(<generated>)
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
  at com.kroger.ngpp.config.AspectLogConfig.executionTime(AspectLogConfig.java:27)
  at jdk.internal.reflect.GeneratedMethodAccessor283.invoke(Unknown Source)
  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.base/java.lang.reflect.Method.invoke(Unknown Source)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  at com.kroger.ngpp.service.ForecastBreakDownService$$EnhancerBySpringCGLIB$$cf1ee3a7.deleteForecastBucketByToplineForecastId(<generated>)
  at com.kroger.ngpp.service.ForecastingAdjustmentService.deleteSnapshotIfExist(ForecastingAdjustmentService.java:162)
  at com.kroger.ngpp.service.ForecastingAdjustmentService.saveForecastAdjustments(ForecastingAdjustmentService.java:135)
  at com.kroger.ngpp.service.ForecastingAdjustmentService$$FastClassBySpringCGLIB$$57c86b1a.invoke(<generated>)
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
  at com.kroger.ngpp.config.AspectLogConfig.executionTime(AspectLogConfig.java:27)
  at jdk.internal.reflect.GeneratedMethodAccessor283.invoke(Unknown Source)
  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.base/java.lang.reflect.Method.invoke(Unknown Source)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  at com.kroger.ngpp.service.ForecastingAdjustmentService$$EnhancerBySpringCGLIB$$bd431e88.saveForecastAdjustments(<generated>)
  at com.kroger.ngpp.service.ForecastingAdjustmentService.lambda$updateForecastingAdjustment$2(ForecastingAdjustmentService.java:94)
  at java.base/java.util.HashMap.forEach(Unknown Source)
  at com.kroger.ngpp.service.ForecastingAdjustmentService.updateForecastingAdjustment(ForecastingAdjustmentService.java:93)
  at com.kroger.ngpp.service.ForecastingAdjustmentService$$FastClassBySpringCGLIB$$57c86b1a.invoke(<generated>)
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
  at com.kroger.ngpp.config.AspectLogConfig.executionTime(AspectLogConfig.java:27)
  at jdk.internal.reflect.GeneratedMethodAccessor283.invoke(Unknown Source)
  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.base/java.lang.reflect.Method.invoke(Unknown Source)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  at com.kroger.ngpp.service.ForecastingAdjustmentService$$EnhancerBySpringCGLIB$$bd431e88.updateForecastingAdjustment(<generated>)
  at com.kroger.ngpp.bff.controller.AdjustmentController.upsertForecastAdjustmentsWithMultipleDivisions(AdjustmentController.java:115)
  at com.kroger.ngpp.bff.controller.AdjustmentController$$FastClassBySpringCGLIB$$2f03b7af.invoke(<generated>)
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:119)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
  at com.kroger.ngpp.config.AspectLogConfig.executionTime(AspectLogConfig.java:27)
  at jdk.internal.reflect.GeneratedMethodAccessor283.invoke(Unknown Source)
  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.base/java.lang.reflect.Method.invoke(Unknown Source)
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  at com.kroger.ngpp.bff.controller.AdjustmentController$$EnhancerBySpringCGLIB$$eb5fde1c.upsertForecastAdjustmentsWithMultipleDivisions(<generated>)
  at jdk.internal.reflect.GeneratedMethodAccessor579.invoke(Unknown Source)
  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.base/java.lang.reflect.Method.invoke(Unknown Source)
  at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
  at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
  at  org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at brave.servlet.TracingFilter.doFilter(TracingFilter.java:68)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at com.kroger.commons.gelf.GelfLoggingFilter.doFilter(GelfLoggingFilter.java:57)
  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.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:106)
  at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  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.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:106)
  at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at com.kroger.ngpp.util.LogRequestFilter.doFilterInternal(LogRequestFilter.java:48)
  at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  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:126)
  at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90)
  at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
  at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:118)
  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:158)
  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.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:92)
  at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
  at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
  at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92)
  at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77)
  at 
Caused by org.hibernate.exception.LockAcquisitionException could not execute statement
  at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:120)
  at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
  at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
  at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
  at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
  com.kroger.ngpp.service.ForecastBreakDownService.deleteForecastBucketByToplineForecastId(ForecastBreakDownService.java:129)
  at 

添加deleteSnapshotIfExist方法后，我开始收到此错误，该方法在4个不同的表中执行删除操作。我应该如何删除，这样我就不会得到CannotAcquireLockException异常。我尝试将deleteSnapshotIfExist放在Synchronized块中，但它超时了504错误