Hi Everybody,

I am currently having a really difficult time with a webapp that I have been working on. Here is the scenario:

Background
1) I have a table with a list of QueueEntries which contains entries that are owned by Client objects.

2) The QueueEntries are mapped with a hibernate List because their positioning/index is important (they can be moved up or down).

3) An outside http request sent by a daemon/service will periodically request updates about QueueEntries table (each daemon/service is a Client) and may also remove entries from that table.

4) An additional Quartz scheduled job goes through a list of new items in feeds and may periodically add to the QueueEntries table for the Clients that are subscribed to those feeds.

The Problem

It seems like occasionally what happens is when the Quartz job comes in and is adding stuff to the queue the http request from the Client might come in and also try to remove stuff from the queue. Because of this conflict I am trying to use database ISOLATION_SERIALIZABLE on the two different methods in my service tier that are called by either the quartz job or the http request handler. I would expect this to cause database level locking on the QueueEntries table because the foreign key index of the Client object should cause the DB to lock those rows; however, it appears that the isolation doesn't have an effect because I have seen cases where the index of the hibernate list becomes corrupted (e.g. 0,1,2,9,10)! Ugh!

It's a pretty standard configuration: Spring MVC -> Business tier with Spring Declarative Transactions -> Hibernate DAO layer.

MySQL is the database.

I don't believe that there is inherently anything wrong with the configuration. But I'm wondering if there is something I am missing. One alternative would be to use table level locks, try to use hibernate versioning, use java synchronization outside the database etc. All of these leave me with a dirty feeling.

Any ideas?

Thanks in advance,
Jarrod

P.S. Where is the best place to put logging to see if transactions are getting suspended??? Thanks!

Hibernate version:
Version 3.0

Full stack trace of any exception that occurs:
It's a null pointer exception in the resolve. I can give a full stack if it is necessary but it's because the index is jacked in the list.

Name and version of the database you are using:
MySQL 4.0.11

I can't say for certain, but I seriously doubt MySQL supports the serializable isolation level.

BTW, generally speaking, simply reading data in serializable isolation does not acquire write locks on that data. Again, I'm not familiar with MySQL's support/behavior (if any) here, so this may or may not hold true for MySQL...

You might want to do pessimistic locking on the Client object itself before munging around with the QueueEntries. Something like

session.lock(clientObject, LockMode.UPGRADE);

Doing this before doing any of the subsequent QueueEntry updates would guarantee that the webapp and the Quartz scheduler would not be hitting the same data at the same time. I'm not sure how else you could try to make this work.

There are other patterns that might work better (i.e. have a priority queue that is ONLY updated by the webapp, and have a separate status bit on QueueEntries that gets updated by Quartz, so they're not fighting over the same list indexes). The pessimistic locking seems like a quicker potential fix, though possibly prone to deadlocks if one side or the other hangs in mid-transaction....

Cheers,
Rob

Hi Rob/Steve,

Thanks for your reply. Rob, after looking through it I believe that your solution will work as it will cause a "select for update" in MySQL which will effectively lock the rows in the transaction.

Thanks to you as well Steve for your quick reply.

The ISOLATION_SERIALIZABLE is supported in both MySQL although (even as stated in the EJB spec) the underlying implementation of ISOLATION_SERIALIZABLE varies greatly from DB to DB. The ANSI SQL 92 spec states loosely that ISOLATION_SERIALIZABLE should make transactions execute as though they happened in serial. I guess this definition was a little vague and the performance implications are pretty big.

Thanks again to everyone for all your help :)

Jarrod