Oracle Deadlock when Hibernate application loading data for readonly use

Question

We are experiencing an Oracle Deadlock (org.hibernate.util.JDBCExceptionReporter - ORA-00060: deadlock detected while waiting for resource) error. It has been suggested that the issue is with a process that is performing readonly operations using Hibernate while another process is performing an update on the same row.

The readonly process in question is configured using Hibernate and Spring. We have not explicitly defined a transaction for the service. While that may not be ideal - I fail to see why Hibernate would try to get an exclusive lock on a row when no save/update operations were performed - only a get/load.

So my question is: Does Hibernate, when no explicit transaction management is defined, try to get a read/write lock on a row even if only a "load" of an object is performed. No Save/Update is performed.

Is it possible that defining a transaction around the service that is loading data, and then specifically saying READONLY on the transactionAttributes would cause Hibernate to ignore an already existing row lock and just load the data for readonly purposes?

Here are some code examples:

For loading the record we are using a HibernateDaoTemplate

public class HibernatePurchaseOrderDataService extends HibernateDaoSupport implements PurchaseOrderDataService {
    public PurchaseOrderData retrieveById(Long id) {
        return (PurchaseOrderData)getHibernateTemplate().get(PurchaseOrderData.class, id);
    }
}

The Spring configuration for the service calling this method is:

<bean id="orderDataService"
      class="com.example.orderdata.HibernatePurchaseOrderDataService">
    <property name="sessionFactory" ref="orderDataSessionFactory"/>
</bean>

<bean id="orderDataSessionFactory"
      class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="hibernateDataSource"/>
    <property name="hibernateProperties" ref="hibernateProperties"/>
    <property name="mappingResources">
        <list>
            <value>com/example/orderdata/PurchaseOrderData.hbm.xml</value>
            <value>com/example/orderdata/PurchaseOrderItem.hbm.xml</value>
        </list>
    </property>
</bean>

The actual deadlock is occurring on one of the PurchaseOrderItem records being loaded by the call to the load the PurchaseOrder.

Would this cause a deadlock if the record being loaded was locked by another process? And if so - would adding a transaction wrapper such as the one below solve the problem?

<bean id="txWrappedOrderDataService"
      class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager" ref="transactionManager"/>
    <property name="target" ref="orderDataService"/>
    <property name="transactionAttributes">
 <props>
     <!-- all methods require a transaction -->
     <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
 </props>
    </property>
</bean>

Update: The DataBase team has seen trace messages on the server that seem to indicate that our "readonly" process is actually writing to the database automatically. There are logged "UPDATE" commands that are performed on the exact columns we are reading from the database. It seems that Hibernate is automatically writing these records back out to the database (even though we aren't asking it to). That would probably explain why there is a deadlock.

Could this be because of a Session FLUSH or something similar? Looking more like the solution might be to use a transaction wrapper with readOnly on it...

Answer 1

Have you checked for any triggers in the database? Are you sure it's Hibernate and not some other process updating those same rows? Maybe there is a column storing a timestamp of the last read and it gets updated every time the row is read (Although I can't remember off the top of my head that you could make SELECT triggers)...

Answer 2

Involuntary updates might happen with hibernate when you use setters that manipulate the value they actually set. An example would be a setter for an String attribute that replaces a value of null with "". A likely candidate are also collections. Make sure setters don't replace the contained collection. If you replace a collection of an entity with a another collection containing the same contents, hibernate will not be able to realize that and update the full collection.

Answer 3

Jens is right

To add on- you need to closely inspect your setters and getters and see if they return a different value on different calls e.g new Date ()- this would return a new value- each time its called and will make hibernate think the object has changed

Answer 4

We finally determined that the solution was to wrap it in a readOnly transaction.

I am not clear why, we were not using the setters at all (simply reading the data) - nothing was getting changed in the database. But for some reason Hibernate was trying to re-write the same data back and was causing a lock when another process tried to read those records.

Using the readOnly transaction caused the problem to go away!

<bean id="txWrappedOrderDataService"
  class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
<property name="transactionManager" ref="transactionManager"/>
<property name="target" ref="orderDataService"/>
<property name="transactionAttributes">
    <props>
        <!-- all methods require a transaction -->
        <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
</property>

Answer 5

One thing interesting to do is to add

log4j.logger.org.hibernate.persister.entity.AbstractEntityPersister=TRACE

in your log4j configuration. By doing that hibernate will log why an entity needs update in the database. We had some issues with entities returning "" when a property was null causing updates in the DB. By doing that we were able to pinpoint such problems.

Answer 6

One thing interesting to do is to add

log4j.logger.org.hibernate.persister.entity.AbstractEntityPersister=TRACE

in your log4j configuration. By doing that hibernate will log why an entity needs update in the database. We had some issues with entities returning "" when a property was null causing updates in the DB. By doing that we were able to pinpoint such problems.

Answer 7

I have seen this issue happen in our system when we had missing indexes. The queries that are being executed in the database are running too long due to missing indexes on key columns thus locking up the table.