hibernate transaction (read one row as inprog, write to other table) (read other table, set row to notinprog)

I am new to hibernate and transactions.

I have a row in a table that marks it as in-progress state. I have a second table that is used to write rows that are to be received by the in-progress worker just before it wraps up, and this effectively adds to its worklist.

There are 2 transactions that are using these 2 tables:

1) a transaction fetches the in-progress dummy row for the maximum sequence number column. When it sees this row, it must know that that row has been committed. (What is the minimum isolation?) Then it checks to see if the row's in-progress flag is true. If in-progress, it knows that there is another long-running thread (not a single transaction) that has already committed that row to in-progress, and it adds its new piece of data to an auxilliary table, expecting that the long-running thread will get/remove/process the rows from the auxilliary table and do something with them and finally set inprogress to false in a single atomic transaction.

2) the second thread starts and sees that in-progress is committed to true upon its start. It does several pieces of work in separate transactions. Then it does one final atomic transaction: (1) read the auxilliary data that several of the thread 1(s) wrote to the second table, (2) removes them and processes them, (3) writes in-progress column of in-progress row to false.

My first question is, for these 2 related transactions, what minimum isolation levels do I need to make them work properly in that when thread 2 finishes, it will have processed all th开发者_如何学Pythone auxilliary data that thread 1 added to second table B when it saw the in-progess state.

Also, I'm very concerned that when I use hibernate setter to set the in-progress state to false in thread 2), that I must do it BEFORE fetching, removing and processing the auxilliary data. And that, doing it first in the transaction, do I also have to flush the session to get hibernate to cause the isolation?

Thanks, Andy

---

It seems to me that the key is to lock the row holding your in-progress flag, essentially equivalent to synchronizing on it in Java terms. You should consistently do this as the first thing before (or at the same time as) reading other data. Using the read-committed isolation level is fine.

You can lock the row by specifying LockMode.UPGRADE (or LockOptions.UPGRADE I think it's changed to in 3.5) to Hibernate when getting the row from a query or from the session. If you're using a native SQL query, you'll have to use your databases implementation (typically ... FOR UPDATE). e.g.:

StatusData status = (StatusData) session.get(StatusData.class, id, LockOptions.UPGRADE);

which Hibernate will use to produce SELECT s_.id, ... FROM status_data s_ WHERE s_id = ? FOR UPDATE OF s_ or the appropriate equivalent for your dialect (e.g. with(updlock,rowlock))

Istr you specify this in a Query too, so probably something like:

StatusData status = (StatusData)
  session.getNamedQuery("Status.FindLatest")
         .setLockMode("status", LockMode.UPGRADE)
         .uniqueResult();

Once you have this row in this mode, any other code trying to perform the same query will block until you have completed your transaction- so you are now free to make changes to status or its dependent collections.

I assume you've found the documentation of using transactions with Hibernate? http://docs.jboss.org/hibernate/core/3.3/reference/en/html/transactions.html

I have had good experiences with using declarative transaction boundaries. So a DAO/EJB/whatever to perform the database interaction, exposed via an interface, and a decorator to automatically begin/commit/rollback around all calls through that interface. Spring and J2EE containers provide infrastructure to do this stuff, although it is easy to do it by hand too. This makes it clear in the code which bits are inside a database transaction and which bits aren't. So if you do the equivalent of:

void finishProcessing() {
   Session session = sessionFactory.openSession();
   Transaction tx = session.beginTransaction();
   ThreadLocalSessionContext.bind(session);
   boolean committed = false;
   try {
       underlying.finishProcessing();
       tx.commit();
       committed = true;
   } finally {
       if (!committed) tx.rollback();
       session.close();
       ThreadLocalSessionContext.unbind(sessionFactory);
   }
}

In your underlying implementation of finishProcessing() you can simply get the status row, perform your other changes and return: the transaction will be committed (which implicitly flushes the session) and the session closed on the way out.