How is Hibernate deciding order of update/insert/delete

Question

Let's first forget about Hibernate. Assume that I have two tables, A & B. Two transactions are updating same records in these two tables, but txn 1 update B and then A, while txn 2 update A then B. This is a typical deadlock example. The most common way to avoid this is by pre-defining the order of acquiring resources. For example, we should update table A then B.

Go back to Hibernate. When we are updating lots of entities in one session, once I am flushing the session, changes of different entities is going to generate corresponding insert/update/delete statements to DB. Does Hibernate have some algorithm to decide the order of update among entities? If not, what is the way Hibernate used to prevent deadlock situation described in 1st paragraph?

If Hibernate is maintaining the order, how can I know or control the order? I don't want my explicit update in DB conflicts with Hibernate, and cause deadlock.

Fronnia

Answer 1

The problem you describe is not handled by the database, and from my experience is not entirely handled by Hibernate either.

You have to take explicit steps to avoid it being a problem.

Hibernate does some of the work for you. As per the previous answer, Hibernate ensures that within an isolated flush the inserts, deletes and updates are ordered in a way that ensures that they will be applied in an achievable order. See performExecutions(EventSource session) in the AbstractFlushingEventListener class:

Execute all SQL (and second-level cache updates) in a special order so that foreign-key constraints cannot be violated:

1. Inserts, in the order they were performed
2. Updates
3. Deletion of collection elements
4. Insertion of collection elements
5. Deletes, in the order they were performed

When having unique constraints it's very important to know this order, especially if you want to replace a one-to-many child (delete old/insert new) but both the old and the new child share the same unique constraints (e.g. same email address). In this case you could update the old entry, instead of deleting/inserting, or you could flush after delete only to then continue inserting. For a more detailed example you can check this article.

Note that it does not specify the order of updates. Examining the Hibernate code leads me to think the update order will depend on the order in which the entities were added to the persistence context, NOT the order they were updated. That might be predictable in your code, but reading the Hibernate code did not leave me feeling I would rely on that ordering.

There are three solutions I can think of:

1. Try setting hibernate.order_updates to be true. This should help avoid deadlocks when multiple rows in the same table are being updated, but won't help with deadlocks across multiple tables.
2. Make your transactions take a PESSIMISTIC_WRITE lock on one of the entities before doing any updates. Which entity you use will depend on your specific situation, but so long as you ensure an entity is chosen consistently if there is a risk of deadlock, this will block the rest of the transaction until the lock can be obtained.
3. Write your code to catch deadlocks when they occur and retry in a sensible fashion. The component managing the dead-lock retry must be located outside of the current transaction boundary. This is because the failing session must be closed and the associated transaction roll-backed. In this article you can find an example of an automatic retrying AOP Aspect.

Sura

Please accept my apology that, for unknown reason I missed this answer before :) I believe the result of your examination in Hibernate's code actually give the most trustable answer till now. Good suggestions especially for point 1 and 2 – Fronnia

Answer 2

Regarding first example - this kind of stuff is handled by database (read more about transaction isolation levels and locking strategies of your database). There are many different ways this can be handled.

As to Hibernate, javadoc to org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(EventSource) says:

Execute all SQL and second-level cache updates, in a special order so that foreign-key constraints cannot be violated:

1. Inserts, in the order they were performed
2. Updates Deletion of collection elements
3. Insertion of collection elements
4. Deletes, in the order they were performed

I assume that this is the only optimization of executed SQL queries Hibernate makes. The rest of problems is handled by database.

Avert

I believe the first example stuff is mostly NOT able to be handled by database. For isolation level, from my understanding, it is more on how the change of one transaction is visible to other. However, even for the lowest isolation level, two transaction writing to same row will still have locking. Therefore if one txn update A record then B, and another txn update B record then A, it will still create deadlock. The best a DBMS can do is to detect potential deadlock happening. I am awared about the Hibernate SQL execution order you mentioned, but what I am concerning about is about – Fronnia

the update order between entities. It will be quite surprising if Hibernate is not having any handling of that and assume that can be handled by DB (which obviously is unrealistic) – Fronnia

Oracle for example somehow handles deadlocks "automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock": oracle-base.com/articles/misc/deadlocks.php. This will probably cause JDBC exception, so you could add some retry logic in code handling the exception. – Avert

But of course it is better to avoid deadlocks by making sure resources are acquired in appropriate order. – Avert

that's what I mean by "DBMS...detect potential deadlock happening". However that is not widely provided by DBMS, and it should be only be used as last resort. – Fronnia

i was facing the same issue, in one transaction , i had a delete and insert of the same entity(relation entity). and i got the same behavior. and to reslve it , i was able to flush after a delete or making the two queries in a separate transaction. – Raceway