Hi,

I have a one-to-many relationship in survey mapping file is defined as:

A survey consists of one or many questions. The order of the question can be changed by modifying SEQUENCE_NO field in question table.

The problem is whenever I changed the sequence_no, the questions set in survey object doesn't reorder to the correct sequence. Here is the rough flow of the action:

1. Open-session-in-view servlet filter opens a session and a read-only transaction T1

2. Another read-write transaction T2 is created -> get survey instance -> initialize the set of questions in the survey instance (coz of lazy="true") -> change the sequence_no in one of the questions -> commit the transaction

3. [Resume T1 transaction] My view get the survey instance (which has been instantiated in step 2, so it is from first-level cache). When I tried to display out the questions, the order doesn't updated to reflect the change in step 2.


Can anyone advise me on how to solve this? I got two solutions, but I don't really like the way of doing it:
1. Do a session.refresh(surveyObj) after committed the transaction in step 2 to load the list from database again. I cannot refresh it before the transaction is commited, coz I'm relying on hibernate transaction dirty-check. But, the problem is I have to call refresh() manually.

2. Don't use bi-directional relationship, throw away the one-to-many relationship. Use HQL query to get the list of questions instead.

Anyone got a better solution? Thanks!!

Merry christmas to all!

As I understand what you describe:
* t1 begins,
* then t2 begins,
* t2 modifies something
* t2 commits and ends
* t1 reads and doesn't see what was done by t2
* t1 ends too

Here's what I'd look at:
* if the data added in t2 is visible using an external sql client, maybe this has to do with your tx isolation level. If possible try and change it only for your t1 tx, or maybe globally if you estimate it's ok. If not, I guess you'll be forced to use refresh().
* why can't you do that t2 tx before even beginning t1 ?

Thanks for your reply!

If you're using open session in view, you should know what I'm describing.

The problem, I believe, is not about tx isolation level. It is because of the first-level cache in the session. Since t1, t2 are in the same session, they share the same first-level cache. Unless I do a manual refresh (invalidate the cache, and recreate the object from database), or else, it will just take the object out, right from the first-level cache, which the order will not be changed to reflect the correct sequence.

The order-by attribute in the hibernate mapping XML file is used only when querying the database, not inside the object. So, I think there is no way I can get the correct sequence, unless I create the object again from database.

Yes, I know OpenSessionInView, although I don't use it anymore since I feel this pattern can too easily lead to N+1 Selects if every developers using Hibernate are not well aware of this potential problem…

In fact, I guess you'll have to refresh your list. If the cache is containing X and you issue some query that modifies in the db something in X, Hibernate won't sync it, so the only way is to force Hibernate to retrieve the modified data from the db (by calling refresh() or by "clear()ing" the whole cache).

Thanks for your reply!

But, I didn't modify the database neither from outside hibernate nor using query (e.g. SQL, HQL, etc..). The problem is "order-by" in the mapping file is only used when retrieving from database to object (as order by clause in sql) , but it is not guaranteed to be preserved after the object is retrieved from db into the cache. When I add in or change the existing element in the set (which internally using LinkedHashSet by hibernate), the order will not be preserved. I didn't explicity execute any query to update the database because I'm using hibernate's dirty check.

Sometimes, I find that the so-called best practises and recommended designs are not really practical at all. However, if we don't use open session in view, we will still run into N+1 selects problem, am I right? The only way to solve N+1 is to explicity call HQL query, which uses join to retrieve every thing in one query.