Atomikos跨库全局事务插入大表时超时问题求助

问题背景 #

使用Atomikos处理两个数据库间的全局事务，此前处理超10亿条记录无异常，近期因表数据量增大出现事务超时。已将超时配置从1000调整至10000但无效，相关日志如下：

{"timestamp":"2022-11-29 18:16:09.691","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"insert.job.BagInsertQuartzJob","msg":">>>>> START JOB: bagInsertingJob - CMJobId -28979"}
{"timestamp":"2022-11-29 18:16:09.726","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"o.s.b.c.l.support.SimpleJobLauncher","msg":"Job: [SimpleJob: [name=bagInsertingJob]] launched with the following parameters: [{Time=1669720569691, JobCMId=-28979}]"}
{"timestamp":"2022-11-29 18:16:09.728","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"i.j.l.BagInsertPartitionerAndJobListener","msg":">>> Just Picked a Job: -28979"}
{"timestamp":"2022-11-29 18:16:09.732","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"o.s.batch.core.job.SimpleStepHandler","msg":"Executing step: [bagInsertMasterStep]"}
{"timestamp":"2022-11-29 18:16:09.787","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"common.util.PartitionUtil","msg":">>>> Partition: 0- Start: 0 - End: 10"}
{"timestamp":"2022-11-29 18:16:09.787","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"common.util.PartitionUtil","msg":">>>> Partition: 1- Start: 11 - End: 21"}
{"timestamp":"2022-11-29 18:16:09.787","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"common.util.PartitionUtil","msg":">>>> Partition: 2- Start: 22 - End: 32"}
{"timestamp":"2022-11-29 18:16:09.788","level":"INFO","thread":"[BagInsertionScheduler_Worker-1] ","logger":"common.util.PartitionUtil","msg":">>>> Partition: 3- Start: 33 - End: 39"}
{"timestamp":"2022-11-29 18:16:19.866","level":"WARN","thread":"[Atomikos:6] ","logger":"c.a.icatch.imp.ActiveStateHandler","msg":"Transaction 10.234.73.199.tm166972056980900063 has timed out and will rollback."}
{"timestamp":"2022-11-29 18:16:19.866","level":"WARN","thread":"[Atomikos:7] ","logger":"c.a.icatch.imp.ActiveStateHandler","msg":"Transaction 10.234.73.199.tm166972056981000065 has timed out and will rollback."}
{"timestamp":"2022-11-29 18:16:19.866","level":"WARN","thread":"[Atomikos:5] ","logger":"c.a.icatch.imp.ActiveStateHandler","msg":"Transaction 10.234.73.199.tm166972056980900064 has timed out and will rollback."}
{"timestamp":"2022-11-29 18:16:19.866","level":"WARN","thread":"[Atomikos:4] ","logger":"c.a.icatch.imp.ActiveStateHandler","msg":"Transaction 10.234.73.199.tm166972056980900062 has timed out and will rollback."}
{"timestamp":"2022-11-29 18:17:11.209","level":"ERROR","thread":"[SimpleAsyncTaskExecutor-43] ","logger":"o.s.batch.core.step.AbstractStep","msg":"Encountered an error executing step bagInsertingSlaveStep in job bagInsertingJob"}
org.springframework.transaction.UnexpectedRollbackException: JTA transaction unexpectedly rolled back (maybe due to a timeout); nested exception is javax.transaction.RollbackException: One or more resources refused to commit (possibly because of a timeout in the resource - see the log for details). This transaction has been rolled back instead.
    at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:1038)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
    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 insert.job.step.BagInsertUpdater$$EnhancerBySpringCGLIB$$d6e05d87.write(<generated>)
    at insert.job.step.BagInsertUpdater$$FastClassBySpringCGLIB$$e835cd44.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.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
    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 insert.job.step.BagInsertUpdater$$EnhancerBySpringCGLIB$$efdca957.write(<generated>)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:193)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:159)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:294)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:217)
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:77)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:407)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:331)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:208)
    at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:138)
    at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:135)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.lang.Thread.run(Thread.java:831)
Caused by: javax.transaction.RollbackException: One or more resources refused to commit (possibly because of a timeout in the resource - see the log for details). This transaction has been rolled back instead.
    at com.atomikos.icatch.jta.TransactionImp.rethrowAsJtaRollbackException(TransactionImp.java:48)
    at com.atomikos.icatch.jta.TransactionImp.commit(TransactionImp.java:188)
    at com.atomikos.icatch.jta.TransactionManagerImp.commit(TransactionManagerImp.java:414)
    at com.atomikos.icatch.jta.UserTransactionImp.commit(UserTransactionImp.java:86)
    at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:1035)
    ... 39 common frames omitted
Caused by: com.atomikos.icatch.RollbackException: One or more resources refused to commit (possibly because of a timeout in the resource - see the log for details). This transaction has been rolled back instead.
    at com.atomikos.icatch.imp.ActiveStateHandler.prepare(ActiveStateHandler.java:202)
    at com.atomikos.icatch.imp.CoordinatorImp.prepare(CoordinatorImp.java:523)
    at com.atomikos.icatch.imp.CoordinatorImp.terminate(CoordinatorImp.java:687)
    at com.atomikos.icatch.imp.CompositeTransactionImp.commit(CompositeTransactionImp.java:282)
    at com.atomikos.icatch.jta.TransactionImp.commit(TransactionImp.java:172)
    ... 42 common frames omitted
{"timestamp":"2022-11-29 18:17:11.212","level":"INFO","thread":"[SimpleAsyncTaskExecutor-43] ","logger":"o.s.batch.core.step.AbstractStep","msg":"Step: [bagInsertingSlaveStep:partition3] executed in 1m1s410ms"}

原因分析 #

从日志和问题描述来看，核心问题并非单纯的Atomikos超时配置未生效，而是多因素叠加导致：

1. 配置未真正生效或单位误解：Atomikos的com.atomikos.icatch.max_timeout参数单位为秒，若之前配置的1000是毫秒，调整为10000毫秒仅相当于10秒，与日志中10秒左右触发超时的时间吻合，说明配置未达到预期时长。此外，若配置项写错或应用未重启，也会导致配置不生效。
2. 资源层超时未同步调整：数据库自身的事务超时（如MySQL的transaction_timeout、Oracle的TRANSACTION_TIMEOUT）、锁等待超时（如MySQL的innodb_lock_wait_timeout）可能比Atomikos的全局超时更短，导致数据库先触发回滚，Atomikos仅作为全局事务管理器被动回滚。
3. 事务粒度与数据处理效率不匹配：Spring Batch单个分区仅处理10条记录却耗时1分钟，说明单条记录的处理逻辑存在性能瓶颈，事务覆盖整个分区处理过程，持续时间远超超时阈值。
4. 锁竞争加剧：数据量增大后，热点数据的并发访问导致锁等待时间变长，进一步拉长事务执行时间，最终触发超时。

解决方案 #

1. 验证并修正Atomikos超时配置 #

• 确认配置项：检查配置文件中是否正确设置com.atomikos.icatch.max_timeout，单位为秒，若需要更长超时（如30分钟），设置为1800。
• 验证生效状态：重启应用后，查看Atomikos启动日志，确认事务超时参数已加载为预期值；或通过Atomikos内置的监控接口查看当前事务的超时配置。

2. 同步调整资源层超时设置 #

• 数据库层面：
  • MySQL：执行SET GLOBAL transaction_timeout = 1800;（单位秒），同时调整innodb_lock_wait_timeout至合理值（如600秒），避免锁等待提前中断事务。
  • Oracle：执行ALTER SYSTEM SET TRANSACTION_TIMEOUT = 1800 SCOPE=BOTH;，确保数据库事务超时不短于Atomikos配置。
• 连接池层面：调整连接池的超时参数，如HikariCP的connectionTimeout设置为300000（5分钟），idleTimeout设置为1800000（30分钟），避免连接提前释放导致事务失败。

3. 缩小事务粒度，优化Batch处理逻辑 #

• 调整Chunk大小：将Spring Batch的Chunk大小从当前的10条进一步缩小（如5条），让每个事务仅处理少量记录，缩短事务持续时间。
• 拆分分区：当前4个分区处理39条记录，可将分区拆分为更小的粒度（如每个分区处理5条），降低单个分区的处理压力。
• 优化数据处理逻辑：检查BagInsertUpdater中的业务代码，避免在事务中执行非数据库操作（如复杂计算、IO操作）；使用批量插入/更新替代单条操作，减少数据库交互次数。

4. 优化SQL与数据库性能 #

• 排查慢查询：开启数据库慢查询日志，定位耗时较长的SQL语句，添加必要的索引（如查询条件、关联字段的索引）。
• 减少锁竞争：调整数据处理顺序，避免同时操作同一热点数据；若业务允许，使用乐观锁替代悲观锁，降低锁持有时间。

5. 调整并发策略 #

• 降低Spring Batch的分区并发数：当前日志显示多个事务同时超时，说明并发数过高导致数据库压力过大，可将TaskExecutorPartitionHandler的maxWorkers设置为2或3，减少并发事务数量。

内容的提问来源于stack exchange，提问作者leetrinh