ORA-00060: deadlock detected while waiting for resource in spring batch writer

Question

I have spring batch application, basically it has its reader and writer implementations. In my writer first i am trying to read Table-A via JpaRepository findBy method, then if record doesnt exits in db then i am trying to insert records to the same table Table-A. However when i am doing so, i am facing issue of table deadlock in spring batch logs like below

SQL Error: 60, SQLState: 61000
ORA-00060: deadlock detected while waiting for resource
HHH000010: On release of batch it still contained JDBC statements

i have used @Transactional annotation on top of write method, i am implementing ItemWriter for my custom writer. I have also tried to reduce db connection max idle time to 2 mins like below but still table deadlock exists in Table-A

spring.datasource.hikari.maxLifeTime : 120000

Table-A is a basic JPA entity class. However after 15 mins table lock gets released with following sequence of errors and writer processes all the records.

 JobRepository failure forcing rollback
org.springframework.dao.ConcurrencyFailureException: PreparedStatementCallback; SQL [UPDATE schema_name.BATCH_STEP_EXECUTION_CONTEXT SET SHORT_CONTEXT = ?, SERIALIZED_CONTEXT = ? WHERE STEP_EXECUTION_ID = ?]; ORA-02396: exceeded maximum idle time, please connect again
; nested exception is java.sql.SQLException: ORA-02396: exceeded maximum idle time, please connect again

java.sql.SQLException: ORA-01012: not logged on

Encountered an error saving batch meta data for step readDataStep in job Import DB Data. This job is now in an unknown state and should not be restarted.
org.springframework.dao.OptimisticLockingFailureException: Attempt to update step execution id=***** with wrong version (1), where current version is 2

I am using oracle database. Could anyone please suggest why i am getting table deadlock error, all i am doing reading from Table-A before inserting data and then writing the data to db if it is not available. I have exhausted my all option, please assist.

Answer 1

i have used @Transactional annotation on top of write method,

This is very likely the cause of your issue. The writer will be executed within a transaction driven by Spring Batch, so there is no need to add that annotation on the write method.