Why does Hibernate throw org.hibernate.exception.LockAcquisitionException?

Question

I have this method :

mymethod(long id){  
    Person p = DAO.findPerson(id);

    Car car = new Car();
    car.setPerson(p);
    p.getCars().add(car);

    DAO.saveOrUpdate(car);
    DAO.saveOrUpdate(p);
    DAO.delete(p.getCars().get(0));//A person have many cars
}  

Mapping :

Person.hbm.xml

<!-- one-to-many : [1,1]-> [0,n] -->
<set name="car" table="cars" lazy="true" inverse="true">
    <key column="id_doc" />
    <one-to-many class="Car" />
</set>

<many-to-one name="officialCar"
class="Car" 
column="officialcar_id" lazy="false"/>  

Cars.hbm.xml

<many-to-one name="person" class="Person"
            column="id_person" not-null="true" lazy="false"/>   

This method works well for a single thread, and on multiple threads, gives me an error:

02/08/2014 - 5:19:11 p.m. - [pool-1-thread-35] - WARN - org.hibernate.util.JDBCExceptionReporter - SQL Error: 60, SQLState: 61000 
02/08/2014 - 5:19:11 p.m. - [pool-1-thread-35] - ERROR - org.hibernate.util.JDBCExceptionReporter - ORA-00060: deadlock detection while waiting for a resource 
 
02/08/2014 - 5:19:11 p.m. - [pool-1-thread-35] - WARN - org.hibernate.util.JDBCExceptionReporter - SQL Error: 60, SQLState: 61000 
02/08/2014 - 5:19:11 p.m. - [pool-1-thread-35] - ERROR - org.hibernate.util.JDBCExceptionReporter - ORA-00060: deadlock detection while waiting for a resource 
 
02/08/2014 - 5:19:11 p.m. - [pool-1-thread-35] - ERROR - org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session 
org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

AOP Transaction :

<tx:advice id="txAdviceNomService" transaction-manager="txManager">
    <tx:attributes>
        <tx:method name="*" propagation="REQUIRED" rollback-for="java.lang.Exception" />
        <tx:method name="getAll*" read-only="true" propagation="SUPPORTS" />
        <tx:method name="find*" read-only="true" propagation="SUPPORTS" />
    </tx:attributes>
</tx:advice>

NB : When i add Thread.sleep(5000) after update, it is ok. But this solution is not clean.

Answer 1

According to your mapping, the sequence of operations should look like this:

Person p = DAO.findPerson(id);

Car car = new Car();
car.setPerson(p);

DAO.saveOrUpdate(car);

p.getCars().add(car);

Car firstCar = p.getCars().get(0);
firstCar.setPerson(null);
p.getCars().remove(firstCar);
if (p.officialCar.equals(firstCar)) {
   p.officialCar = null;
   p.officialCar.person = null;
}

DAO.delete(firstCar);

An update or a delete means acquiring an exclusive lock, even on READ_COMMITTED isolation level.

If another transaction wants to update the same row with the current running transaction (which already locked this row in question) you won't get a deadlock, but a lock acquisition timeout exception.

Since you got a deadlock, it means you acquire locks on multiple tables and the lock acquisitions are not properly ordered.

So, make sure that the service layer methods set the transaction boundaries, not the DAO methods. I see you declared the get and find methods to use SUPPORTED, meaning they will use a transaction only if one is currently started. I think you should use REQUIRED for those as well, but simply mark them as read-only = true.

So make sure the transaction aspect applies the transaction boundary on the "mymethod" and not on the DAO ones.