问题描述

批量操作的时候，遇到一个数据库死锁的问题：

  1. [31m2020-09-22 02:08:35 [gentleTaskExecutor4] INFO  n.x.g.m.c.b.i.MaterialTranscodeBizImpl - 转码插入数据库异常
  2. org.springframework.dao.DeadlockLoserDataAccessException: 
  3. ### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  4. ### The error may exist in class path resource [mapper/MaterialPersonalTranscodeMapper.xml]
  5. ### The error may involve defaultParameterMap
  6. ### The error occurred while setting parameters
  7. ### SQL: insert into `material_personal_transcode`(`material_personal_id`, `transcode_task_id`, `status`, `url`, `full_url`, `material_size`, `quality`, `used`, `deleted`, `source_type`, `subscribe_id`, `tenant_id`, `create_time`, `modify_time`)         values                        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  8. ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  9. ; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  10.     at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
  11.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  12.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
  13.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
  14.     at com.sun.proxy.$Proxy117.insert(Unknown Source)
  15.     at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
  16.     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
  17.     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93)
  18.     at com.sun.proxy.$Proxy148.insertBatch(Unknown Source)
  19.     at net.xinhuamm.gusteau.media.service.service.impl.MaterialPersonalTranscodeServiceImpl.insertBatch(MaterialPersonalTranscodeServiceImpl.java:38)
  20.     at net.xinhuamm.gusteau.media.cms.biz.impl.MaterialTranscodeBizImpl.transcodePersonal(MaterialTranscodeBizImpl.java:119)
  21.     at net.xinhuamm.gusteau.media.cms.biz.impl.MaterialTranscodeBizImpl$$FastClassBySpringCGLIB$$b0cc2cff.invoke(<generated>)
  22.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  23.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)
  24.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  25.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)<==    Updates: 1
  27.     at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:353)
  28.     at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@73205a91]
  30.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  31.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
  32.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
  33.     at net.xinhuamm.gusteau.media.cms.biz.impl.MaterialTranscodeBizImpl$$EnhancerBySpringCGLIB$$eb3862ee.transcodePersonal(<generated>)
  34.     at net.xinhuamm.gusteau.media.cms.biz.impl.MaterialPersonalBizImpl.lambda$null$4(MaterialPersonalBizImpl.java:261)
  35.     at java.lang.Iterable.forEach(Iterable.java:75)
  36. <==    Updates: 1    at net.xinhuamm.gusteau.media.cms.biz.impl.MaterialPersonalBizImpl.lambda$executeTranscode$5(MaterialPersonalBizImpl.java:259)
  38.     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
  39. Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@20ee3019]
  40.     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
  41.     at java.lang.Thread.run(Thread.java:748)
  42. Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  43.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
  44.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  45.     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  46.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  47.     at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
  48.     at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
  49.     at sun.reflect.GeneratedMethodAccessor242.invoke(Unknown Source)
  50.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  51.     at java.lang.reflect.Method.invoke(Method.java:498)
  52.     at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
  53.     at com.sun.proxy.$Proxy228.execute(Unknown Source)
  54.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
  55.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  56.     at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
  57.     at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  58.     at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
  59.     at sun.reflect.GeneratedMethodAccessor247.invoke(Unknown Source)
  60.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  61.     at java.lang.reflect.Method.invoke(Method.java:498)
  62.     at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
  63.     at com.sun.proxy.$Proxy226.update(Unknown Source)
  64.     at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
  65.     at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
  66.     at sun.reflect.GeneratedMethodAccessor254.invoke(Unknown Source)
  67.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  68.     at java.lang.reflect.Method.invoke(Method.java:498)
  69.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
  70.     ... 24 more

相关问题：https://stackoverflow.com/questions/7947582/spring-transactional-deadlock