[Spring Batch] Oracle DB-Job실행중 ORA-08177 발생

ORA 08177 Exception이 발생했다.
여러 jvm에서 동시에 spring batch job를 실행하고 하나의 job repository를 사용하는 곳에 문제가 발생될 수 있다.


2011-02-25 11:19:25,927 ERROR [org.springframework.batch.core.launch.support.CommandLineJobRunner] - <Job Terminated in error: PreparedStatementCallback; SQL [INSERT INTO BATCH_JOB_EXECUTION_CONTEXT (SHORT_CONTEXT, SERIALIZED_CONTEXT, JOB_EXECUTION_ID) VALUES(?, ?, ?)]; ORA-08177: 이 트랜잭션에 대한 직렬화 액세스를 할 수 없습니다
; nested exception is java.sql.SQLException: ORA-08177: 이 트랜잭션에 대한 직렬화 액세스를 할 수 없습니다

또는

ORA-08177: can't serialize access for this transaction

transactionManager의 IsolationLevelForCreate 속성은 따로 지정하지 않을 경우 디폴트 값인 ISOLATION_SERIALIZABLE으로 설정된다.

<job-repository id="jobRepository"
    dataSource="dataSource"
    transactionManager="transactionManager"
    isolation-level-for-create="SERIALIZABLE"
    table-prefix="BATCH_"
/>

오라클 DB를 경우는 isolation-level-for-create="DEFAULT" 로 해줘야 해당 exception이 발견되지 않는다.

<job-repository id="jobRepository"
    dataSource="dataSource"
    transactionManager="transactionManager"
    isolation-level-for-create="DEFAULT"
    table-prefix="BATCH_"
/>


http://forum.springsource.org/showthread.php?t=61948&highlight=ORA-08177

" multiple spring batch processes were starting simultaneously as separate jobs via a cron schedule.
This was resulting in frequent "ORA-08177: can't serialize access for this transaction" errors.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref1981
위의 링크중 오라클쭝 링크의 내용을 보면 다음과 같다.

Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:

ORA-08177: Cannot serialize access for this transaction 

When a serializable transaction fails with the Cannot serialize access error, the application can take any of several actions:

• Commit the work executed to that point

• Execute additional (but different) statements (perhaps after rolling back to a savepoint established earlier in the transaction)

• Undo the entire transaction

Figure 13-2 shows an example of an application that rolls back and retries the transaction after it fails with the Cannot serialize access error:

Figure 13-2 Serializable Transaction Failure