Announcement Announcement Module
Collapse
No announcement yet.
ISOLATION_SERIALIZABLE and Hibernate Lists Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ISOLATION_SERIALIZABLE and Hibernate Lists

    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!

  • #2
    Hi,

    Am seeing a similar thing - did you manage to solve this?

    cheers,

    David

    Comment


    • #3
      Hi,

      The use of SERALIZABLE isolation level seems too drastic from my point of view.
      You need to eliminate the chance that the two jobs (http request sent by daemon and Quartz job) act on the same records at the same time. The QueueEntries are grouped by the client. Jobs that process QueueEntries for different Clients at the same are not conflicting.

      For this a READ_COMMITED isolation level would be enough. Before continuing with the code that access the QueueEntries table needs, you need to do this:
      Code:
      select ... from client where client.id = ? for update
      .
      This will ensure that if you have 2 threads working on the QueueEntries of the same client, one of them will wait for the other to finish.

      Hope this helps. Mircea

      Comment


      • #4
        This is not quite what we're doing...do you have any comments on my other post that explains our situation more fully? I would be grateful...!

        http://forum.springframework.org/showthread.php?t=21330

        cheers,

        David

        Comment

        Working...
        X