Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle Deadlock when Hibernate application loading data for readonly use

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...

like image 468
jonathanq Avatar asked Mar 19 '09 17:03

jonathanq


1 Answers

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.

like image 170
Jens Schauder Avatar answered Oct 18 '22 23:10

Jens Schauder