Hibernate joined-subclass deadlocks with MSSQL

Question

I'm using Hibernate with Joined-SubClasses to Map a class hierarchy to a database. Unfortunately this causes deadlocks when an object is updated while a different thread is trying to load the same object. With objects that are mapped to a single table this is no problem. This seems to be caused the way MSSQL acquires locks on the tables of the class hierarchy.

When Hibernate loads an object from the database it uses a SELECT with a JOIN:

SELECT ...
FROM
    subclass
    LEFT JOIN class
        ON ...
WHERE ...

When Hibernate updates an object of this subclass it does:

UPDATE
    class
SET ...
WHERE ...

UPDATE
    subclass
SET ...
WHERE ...

The problem is that if an object is loaded between the two update statements it causes a deadlock. The SELECT statement seems locks the 2 tables one after another. So what seems to happen is:

1. Thread 1 loads an object and places shared locks on both tables
2. Thread 1 executes the UPDATE statement for the class table and upgrades the lock on the class table to an exclusive lock.
3. Thread 2 tries to load the same object by executing the SELECT statement, it places a shared lock on the subclass table and then waits until the exclusive lock on the class table is released
4. Thread 1 executes the UPDATE statement for the subclass table, it wants to upgrade it's lock on the subclass table to an exclusive lock, but the table is already locked by thread 2, which is waiting for thread 1
5. Thread 2 is aborted due to a deadlock with thread 1

The deadlock graph looks like this: Deadlock Graph

These object are updated quiet often and this causes deadlocks all the time even when just a single object is loaded. I also tried to reproduce the problem with HSQLDB but then it does not deadlock, HSQLDB seems to either lock both tables at once or waits until it can lock both, so it seems to be a problem that only occurs with MSSQL.

What would be a solution to avoid this problem with Hibernate without modifying the schema (except for indexes)?

Answer 1

Looks to me that those updates need to be done atomically within a single transaction. Unfortunately I don't have a lot of background on Hibernate so I'll leave it to others to point you in the right direction there.

Answer 2

Have you turned on the SQL Server deadlock trace flags 1204 or 1222? This will help identify exactly what resources are causing the deadlock. See the MSDN article on Detecting and Ending Deadlocks for more information.

Are there indexes on these tables? If so, deadlocks can occur if applications acquire locks on a clustered index then try to acquire more locks on the same table by seeking the non-clustered index.