Spring batch jobs simultaneously access database locks

Question

I have two Spring Batch jobs: first one (job A) reads data from a CRM system (thru web services) and writes it to Oracle database table; second one (job B) - vise verse - reads data from same Oracle database table and sends it to CRM (thru web services). I use HibernateTemplate to do database operations. Methods for saving and updating to Oracle database are marked with @Transactional(propagation = Propagation.REQUIRES_NEW). These batch jobs work simultaneously. At some point the two jobs block each other: - job A while reading record from Oracle database blocks, the job which has to read and send data to CRM freezes and the only action I can made is to stop it manually. -job B freezes too and later throws exception:

Caused by: org.hibernate.exception.LockAcquisitionException: could not execute update query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:87)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
    at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
    at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
    at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
    at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
    at org.springframework.orm.hibernate3.HibernateTemplate$39.doInHibernate(HibernateTemplate.java:1150)
    at org.springframework.orm.hibernate3.HibernateTemplate$39.doInHibernate(HibernateTemplate.java:1)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 60 more

Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

I'm not familiar with Hibernate especially when it works along with Spring. My understanding was that Hibernate manages transactions but obviously I'm wrong. Can you please advice me where can be the reason for these locks. My Hibernate settings are:

<property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
            <prop key="hibernate.cglib.use_reflection_optimizer">false</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.generate_statistics">true</prop>
            <prop key="hibernate.default_batch_fetch_size">0</prop>
            <prop key="hibernate.cache.use_structured_entries">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
            <prop key="hibernate.use_sql_comments">true</prop>
            <prop key="hibernate.jdbc.batch_size">0</prop>
            <prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.connection.characterEncoding">UTF8</prop>
            <prop key="hibernate.mapping.precedence">class</prop>
            <prop key="hibernate.transaction.flush_before_completion">true</prop>
            <prop key="hibernate.connection.release_mode">auto</prop>
        </props>
    </property>

here is a good link for this : http://www.oratechinfo.co.uk/deadlocks.html#unindex_fk — Cygnusx1, Jun 18 '13 at 20:20

score 0 · Answer 1 · edited May 23 '17 at 12:05

Ok, first you should be able to find a trace log in oracle for this deadlock. you will probably see what is causing the deadlock precisely.

see this thread

Deadlock can and will happen whatever setting you try on the transaction level! You will probably have to review the logic of your batch and the fact that running them in parallel might cause more arms then good.

in your case, job A and job B probably fight to lock the same record in the oracle database.

Good luck

Spring batch jobs simultaneously access database locks

1 Answers1