I am using spring batch 5 using oracle. I have more than 15 batches running using the database. But it shows below error:
error code [8177]; ORA-08177: can't serialize access for this transaction
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
VALUES (?, ?, ?, ?)
]; SQL state [72000]; error code [8177]; ORA-08177: can't serialize access for this transaction
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1573)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1020)
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:157)
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:169)
at jdk.internal.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.lambda$getObject$0(AbstractJobRepositoryFactoryBean.java:204)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244)
at jdk.proxy2/jdk.proxy2.$Proxy124.createJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:145)
at org.springframework.batch.core.launch.support.TaskExecutorJobLauncher.run(TaskExecutorJobLauncher.java:59)
at com.citylive.prepaidcard.config.ScheduledJobBean.perform(ScheduledJobBean.java:62)
at jdk.internal.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:96)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLException: ORA-08177: can't serialize access for this transaction
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:151)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1051)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1530)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1310)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3745)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3917)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3896)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:991)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:648)
... 34 more
Caused by: Error : 8177, Position : 0, Sql = INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
VALUES (:1 , :2 , :3 , :4 )
, OriginalSql = INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
VALUES (?, ?, ?, ?)
, Error Msg = ORA-08177: can't serialize access for this transaction
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
... 51 more
Here is my code:
My application.properties:
spring.datasource.url=jdbc:oracle:thin:@localhost:1521/mydb
spring.datasource.username=test
spring.datasource.password=test124
db.name=test
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.hikari.maximumPoolSize=10
spring.batch.jdbc.initialize-schema=always
My Schedule Run the job:
@Component
@Slf4j
public class MyJobJob {
@Autowired
JobLauncher jobLauncher;
@Autowired
EftJob2 eftJob;
@Autowired
private JobRepository jobRepository;
@Scheduled(cron = "0 */2 * * * ?")
public void perform() throws Exception
{
JobExecution execution = null;
JobExecution lastJobExecutionStep1 = jobRepository.getLastJobExecution("EFT-STEP1", new JobParametersBuilder().toJobParameters());
if (lastJobExecutionStep1 == null || lastJobExecutionStep1.getStatus().isLessThan(BatchStatus.STARTING)) {
log.info( "{} batch step1 {} execution {} Started: ");
try {
JobParameters jobParameters = new JobParametersBuilder()
.addLong("startAtEftStep1", System.currentTimeMillis())
.toJobParameters();
execution = jobLauncher.run(eftJob.createEft(), jobParameters);
log.info(" batch {} Step1 existStatus: "+execution.getExitStatus().getExitCode());
} catch (JobExecutionAlreadyRunningException | JobRestartException |
JobInstanceAlreadyCompleteException |
JobParametersInvalidException ex) {
log.error("batch {} Step1 {} execution {} JobExecutionAlreadyRunningException | JobRestartException | JobInstanceAlreadyCompleteException | JobParametersInvalidException message: "+ex.getMessage());
ex.printStackTrace();
}catch (Exception ex) {
ex.printStackTrace();
}
}else{
log.info(" batch step1 {} execution {} isRunning: ");
}
}
}
Job:
@Component
@RequiredArgsConstructor
@Slf4j
public class EftJob2 {
private final MySummaryRepo approvedTrxSummaryRepo;
private final MyTransactionRepo corpApprovedTransactionRepo;
private final EftProcessorStep1 batchProcessorStep1;
private final EftWriterStep1 batchWriterStep1;
private final PlatformTransactionManager transactionManager;
private final JobRepository jobRepository;
@Autowired
private JobExecutionListenerStep1 jobExecutionListenerStep1;
@Autowired
private MySPcall transactionSPcall;
public Job createEft(){
return new JobBuilder("EFT-STEP1", jobRepository)
.incrementer(new RunIdIncrementer())
.start(step1())
.build();
}
private Step step1(){
return new StepBuilder("step1", jobRepository)
.<ApprovedTrxSummery, ApprovedTrxSummery> chunk(10, transactionManager)
.reader(new EftReaderStep1(approvedTrxSummaryRepo,corpApprovedTransactionRepo, transactionSPcall))
.processor(batchProcessorStep1)
.writer(batchWriterStep1)
.build();
}
}
Batch config:
@Configuration
public class BatchBean {
@Bean
public EftProcessorStep1 batchProcessorStep1() {
return new EftProcessorStep1();
}
@Bean
public EftWriterStep1 batchWriterStep1() {
return new EftWriterStep1();
}
}
Repository:
@Transactional
@Repository
public interface TransactionRepo extends JpaRepository<TransactionEntity, Long> {
@Query(value = "SELECT * FROM TRANSACTION WHERE STATUS =1 FOR UPDATE ", nativeQuery = true)
List<TransactionEntity> getbKashTransaction();
}
Batch Reader: Read data from repository using ItemReader
@Slf4j
public class ReaderStep1 implements ItemReader<TransactionEntity> {
private Iterator<TransactionEntity> iterator;
private TransactionSPcall transactionSPcall;
public ReaderStep1(TransactionRepo transactionRepo, TransactionSPcall transactionSPcall) {
this.transactionSPcall = transactionSPcall;
List<TransactionEntity> list = transactionRepo.getbKashTransaction();
if(!list.isEmpty()){
this.iterator = list.iterator();
}
}
@Override
public TransactionEntity read() {
return (iterator != null && iterator.hasNext()) ? iterator.next() : null;
}
}
Batch Processor:
@Slf4j
public class ProcessorStep1 implements ItemProcessor<TransactionEntity, TransactionEntity> {
@Override
public TransactionEntity process(TransactionEntity item) throws Exception {
// Logic calling api
return item;
}
Batch writer:
@Slf4j
public class WriterStep1 implements ItemWriter<TransactionEntity> {
@Override
public void write(Chunk<? extends TransactionEntity> chunk) {
chunk.forEach(item -> {
// update database
});
}
}
}
It often occur the error.
What is the wrong of my code?
Please help
Problem is about
@Transactional
@Repository
public interface TransactionRepo extends JpaRepository<TransactionEntity, Long> {
@Query(value = "SELECT * FROM TRANSACTION WHERE STATUS =1 FOR UPDATE ", nativeQuery = true)
List<TransactionEntity> getbKashTransaction();
}
You already have @Transactional on top which makes everything transactional and below using FOR UPDATE is SQL level transactions, instead of mixing java and sql level transactions try to keep them in one layer. I would suggest keep all in java. Otherwise this code may break somewhere else.
And define isolation level like below or like this
batch:
repository:
isolationlevelforcreate: ISOLATION_READ_COMMITTED
Also the exception may come from multiple batch jobs starting at the same time. Try to separate their start times and make sure they do not overlap.
Reference: