难得一见的数据库事务异常 Deadlock found when trying to get lock解决办法dao.DeadlockLoserDataAccessException怎么办

深碍√TFBOYSˉ_ 2022-12-28 04:17 172阅读 0赞

1、解决办法，参考下 Mysql的说明文档，都是英文的，可以在线翻译成中文的！

Mysql的说明文档关于DeadLock

截取的有价值的文档说明如下：

  • 如果由于死锁而失败，请始终准备重新发行事务。死锁并不危险。请再试一次。
  • 保持交易小巧且持续时间短，以使交易不易发生冲突。
  • 进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务就打开交互式 mysql会话。
  • 如果您使用锁定读取（SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED。
  • 修改事务中的多个表或同一表中的不同行集时，每次都要以一致的顺序执行这些操作。然后，事务形成定义明确的队列，并且不会死锁。例如，组织数据库操作到功能在应用程序中，或调用存储程序，而不是编码的多个相似序列 INSERT，UPDATE以及 DELETE在不同的地方语句。
  • 将选择好的索引添加到表中。然后，您的查询需要扫描更少的索引记录，并因此设置更少的锁。使用EXPLAIN SELECT以确定哪些索引MySQL认为最适合您的查询。
  • 使用较少的锁定。如果你能负担得起，以允许 SELECT从一个旧的快照返回数据，不要添加条款FOR UPDATE或LOCK IN SHARE MODE给它。在READ COMMITTED这里使用隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。
  • 如果没有其他帮助，请使用表级锁序列化事务。LOCK TABLES与事务表（例如InnoDB 表）一起使用的正确方法 是，以SET autocommit = 0（not START TRANSACTION）后跟来开始事务，直到明确提交事务后才LOCK TABLES调用 UNLOCK TABLES。例如，如果您需要写表 t1和从表中读取数据 t2，则可以执行以下操作：
  1. 表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。
  • 序列化事务的另一种方法是创建一个仅包含一行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有事务都以串行方式发生。请注意，InnoDB 即时死锁检测算法在这种情况下也适用，因为序列化锁是行级锁。对于MySQL表级锁，必须使用超时方法来解决死锁。

{“type”:”5”,”info”:[“”,””,””,””,”2020-12-14 15:26:53.622”,”tbm-compose-provider”,”10.6.11.98”,”1”,””,””,”com.gildata.threebody.tbm.api.facade.PopularFeelingsFacade.saveBatch”,”jres-svr”,””],”myPackage”:{“error_no”:”-1”,”error_info”:” ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve com.gildata.threebody.tbm.dao.mapper.middleground.TbmNewsPopFeeMapper.updateById-Inline ### The error occurred while setting parameters ### SQL: UPDATE tbm_news_popular_feelings SET news_id=?, handle_status=?, publish_status=?, priority=?, update_time=?, create_time=?, updater_id=?, task_group=?, operation_log=?, dup_news_id=?, content_words=?, title_md5=?, pub_sentiment_news_type=? WHERE id=? ### Cause: com.mysql.jdbc.exceptions.jdbc4.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.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction”,”requestBody”:””,”exceptionStack”:”org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve com.gildata.threebody.tbm.dao.mapper.middleground.TbmNewsPopFeeMapper.updateById-Inline ### The error occurred while setting parameters ### SQL: UPDATE tbm_news_popular_feelings SET news_id=?, handle_status=?, publish_status=?, priority=?, update_time=?, create_time=?, updater_id=?, task_group=?, operation_log=?, dup_news_id=?, content_words=?, title_md5=?, pub_sentiment_news_type=? WHERE id=? ### Cause: com.mysql.jdbc.exceptions.jdbc4.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.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) at com.sun.proxy.$Proxy118.update(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:73) at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64) at com.sun.proxy.$Proxy223.updateById(Unknown Source) at com.gildata.threebody.tbm.dao.impl.TbmNewsPopFeeDaoImpl.updateById(TbmNewsPopFeeDaoImpl.java:70) at com.gildata.threebody.tbm.dao.impl.TbmNewsPopFeeDaoImplFastClassBySpringCGLIB77e08cd9.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.gildata.threebody.tbm.dao.impl.TbmNewsPopFeeDaoImplEnhancerBySpringCGLIB737d093b.updateById() at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImpl.updateTbmNewsPopularFeelings(PopularFeelingsHandleServiceImpl.java:230) at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImpl.deletePopFeel(PopularFeelingsHandleServiceImpl.java:152) at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImplFastClassBySpringCGLIBb260d6ab.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImplEnhancerBySpringCGLIBb6e4434.deletePopFeel() at com.gildata.threebody.tbm.provider.impl.facade.PopularFeelingsFacadeImpl.saveBatch(PopularFeelingsFacadeImpl.java:560) at com.alibaba.dubbo.common.bytecode.Wrapper33.invokeMethod(Wrapper33.java) at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46) at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72) at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53) at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.manager.filter.DebugProviderFilter.invoke(DebugProviderFilter.java:58) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:84) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.monitor.filter.MonitorProviderFilter.invoke(MonitorProviderFilter.java:60) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.bodyinfo.BodyInfoProviderFilter.invoke(BodyInfoProviderFilter.java:56) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.trace.TraceProviderFilter.invoke(TraceProviderFilter.java:143) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:113) at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84) at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170) at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52) at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.GeneratedConstructorAccessor278.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409) 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 org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at sun.reflect.GeneratedMethodAccessor596.invoke(Unknown Source) 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:63) at com.sun.proxy.$Proxy330.update(Unknown Source) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) at sun.reflect.GeneratedMethodAccessor859.invoke(Unknown Source) 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.$Proxy118.update(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:73) at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64) at com.sun.proxy.$Proxy223.updateById(Unknown Source) at com.gildata.threebody.tbm.dao.impl.TbmNewsPopFeeDaoImpl.updateById(TbmNewsPopFeeDaoImpl.java:70) at com.gildata.threebody.tbm.dao.impl.TbmNewsPopFeeDaoImplFastClassBySpringCGLIB77e08cd9.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.gildata.threebody.tbm.dao.impl.TbmNewsPopFeeDaoImplEnhancerBySpringCGLIB737d093b.updateById() at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImpl.updateTbmNewsPopularFeelings(PopularFeelingsHandleServiceImpl.java:230) at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImpl.deletePopFeel(PopularFeelingsHandleServiceImpl.java:152) at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImplFastClassBySpringCGLIBb260d6ab.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.gildata.threebody.tbm.provider.impl.service.PopularFeelingsHandleServiceImplEnhancerBySpringCGLIBb6e4434.deletePopFeel() at com.gildata.threebody.tbm.provider.impl.facade.PopularFeelingsFacadeImpl.saveBatch(PopularFeelingsFacadeImpl.java:560) at com.alibaba.dubbo.common.bytecode.Wrapper33.invokeMethod(Wrapper33.java) at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46) at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72) at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53) at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.manager.filter.DebugProviderFilter.invoke(DebugProviderFilter.java:58) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:84) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.monitor.filter.MonitorProviderFilter.invoke(MonitorProviderFilter.java:60) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.bodyinfo.BodyInfoProviderFilter.invoke(BodyInfoProviderFilter.java:56) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.hundsun.jrescloud.rpc.def.trace.TraceProviderFilter.invoke(TraceProviderFilter.java:143) at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:111) at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:113) at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84) at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170) at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52) at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) “}}

发表评论

表情：
评论列表 （有 0 条评论，172人围观）

还没有评论，来说两句吧...

相关阅读