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.

Solution 1

I have Cars -> (1 -n) places. And i have a foreign key in the table place (id_car). This foreign key dont have an index. When i add an index to this foreign key, my problem is resolved.

Refer to This Answer

Solution 2

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.

Comments

[Abdelhafid]

The transaction is applied for mymethod. I change the code for the question.

[Vlad Mihalcea]

You don't need to call saveOrUpdate for Person, call it for a new car. Is person.cars unidirectional? If it's not set cat.person too, when saving/deleting.

[Abdelhafid]

Thanks Vlad. I changed the code. But i have the same problem.

[Vlad Mihalcea]

Can you post the mapping for Car and Person?

[Vlad Mihalcea]

Updated my response too.

[Abdelhafid]

Thank you Vlad, i will add a solution. Your code is correct, but my problem is related to the indexs.