SPRING BATCH:nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

Question

We are facing the above issue when we try to run multiple job at the same time .

Below is the functionality of one of the tasklet in spring batch job.

Tasklet1 : generate message for external system A , persist the message id , send message to external system ,stop the spring batch job (I am stopping this job since I don't have any control over the external system ,I don't know when I will receive response from the system)

Below is the listener which is always running to listen to the external system 's response .

listener1 -listener to listen response from external system A ,the moment response is received , listener class restart the same job by getting job id persisted in the DB .

If i run one or two jobs , it gets completed without any issue , but if I try to run 20 jobs in parallel then at least 5 are getting failed with the above exception as mentioned in the title.

I am not sure what should i do here so that all jobs gets completed .

I have already change "isolation-level-for-create" to "READ_COMMITTED" , that did not help me .

Any help would be highly appreciated .

Thanks.

UPDATE: I have tried to create bean for job repository and use aop as shown below.

<aop:config>
        <aop:pointcut id="allRepositoryMethods"
            expression="execution(* org.springframework.batch.core..*Repository+.*(..))" />
        <aop:advisor advice-ref="txAdvice" pointcut-ref="allRepositoryMethods" />
    </aop:config>
    <tx:advice id="txAdvice" transaction-manager="txManager">
        <tx:attributes>
            <tx:method name="*" isolation="READ_COMMITTED" />
        </tx:attributes>
    </tx:advice>

But this is throwing the error

"IllegalStateException Existing transaction detected in JobRepository. Please fix this and try again (e.g. remove @Transactional annotations from client)"

I have made sure to remove @Transactional if any in all methods , but still i am getting the above error .

Any idea ?

.

Am I missing something? Wouldn't `` and a custom annotation including @Transactional simply avoid all that AOP code? My comment is not related to the issue — usr-local-ΕΨΗΕΛΩΝ, Dec 22 '17 at 15:52

score 1 · Answer 1 · answered Feb 18 '16 at 17:49

1

The value that JobRepositoryFactoryBean.isolationLevelForCreate should be set to is 'ISOLATION_READ_COMMITTED', not simply 'READ_COMMITTED'.

answered Feb 18 '16 at 17:49

Artefacto

96,375
17
202
225

I have xml configuration of job repository . – DevG Feb 18 '16 at 17:52
If i try to change ""READ_COMMITTED" to "ISOLATION_READ_COMMITTED" ,I can see the error "The value 'ISOLATION_READ_COMMITTED' of attribute 'isolation-level-for-create' on element 'batch:job-repository' is not valid with respect to its type, 'isolationType'." – DevG Feb 18 '16 at 17:54
@DevG Ah you're using the XML configuration. Then I guess that is correct. In any case, I suggest you put a breakpoint in `AbstractJobrepositoryfactoryBean::setIsolationLevelForCreate` and see what's being passed there (if anything). Setting the `READ_COMMITTED` did solve this problem for me a while ago. – Artefacto Feb 18 '16 at 17:58
I think 'READ_COMMITED' is being overridden. – DevG Feb 22 '16 at 13:16

score 0 · Answer 2 · edited Dec 22 '17 at 15:51

0

Yes, Putting below line wont work :

jobRepositoryFactoryBean.setIsolationLevelForCreate(Isolation.READ_COMMITTED.toString());

we need to include ISOLATION_READ_COMMITTED for setting the value.

jobRepositoryFactoryBean.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");

This will work!!!

edited Dec 22 '17 at 15:51

usr-local-ΕΨΗΕΛΩΝ

26,101
30
154
305

answered Oct 05 '16 at 21:21

Bharath B

76
1
10

SPRING BATCH:nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

2 Answers2