Announcement Announcement Module
Collapse
No announcement yet.
Confused with Hibernate transactions and order of SQL exection Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Confused with Hibernate transactions and order of SQL exection

    I have some interesting synchronization requirements I need to guard against. Basically, I have open "slots" that people (basic users) belonging to different "stores" can store information in. The slots are by date and time and I need to prevent multiple people from the same "store" adding data to the same time/date slot. I can't rely on a unique key in the database because there will be times when certain groups of users (administrators) can add multiple rows in the table for any given date/time. Think of that as "double booking" that only a manager can do.

    Anyway, I'm trying to solve the problem using row level locking. For example, if a basic user comes to the application I:

    Code:
    Begin transaction
    Row lock the store table using select for update
    Try to locate a record for the given store ID, time, and date.
    If the row exists
        throw an exception.
    else
        add the row
    End the transaction
    So now comes the problem. My hibernate code looks something like this:

    Code:
     public void save(AppointmentDate appt, Store s)
     {
         HibernateTemplate ht = getHibernateTemplate();
         ht.lock(s, LockMode.UPGRADE);
    
         List appts = ht.findByNamedQueryAndNamedParam("AppointmentDate.isApptSlotTaken",
                                                              labels, 
                                                              values); 
    
         if(appts.size() > 0)
         {
             StaleStateException sse = new StaleStateException("Slot taken");
         }
         else
         {
             ht.save(appt);
         }
    }
    When this code runs in the context of a larger test using AbstractAutorevenueTransactionalSpringContextTest I find (from debug output) that Hibernate is ordering the SQL such that the insert is happening first, then the select happens and I'm always throwing the exception.

    I thought my solution would be to fource this method, in this DAO, to always run in its own transaction. And So I changed the propogation behavior from PROPAGATION_REQUIRED to PROPAGATION_REQUIRES_NEW.

    But now when I run my test cases, I keep getting an
    Code:
    Illegal attempt to associate a collection with two open sessions error
    Does anyone have enough experience with this sort of synchronication to know if I'm on the right track? Or should I just scrap HIbernate with my save method and use JDBC instead?

  • #2
    I'm starting to wonder how much of what I'm seeing is an artifact of using AbstractAutorevenueTransactionalSpringContextTest as my test driver.

    I changed PROPAGATION_REQUIRES_NEW back to PROPAGATION_REQUIRED. Then, in my test constructore I did:

    Code:
    setDefaultRollback(false);
    Now when I run my tests, I see the select happens before the insert. That part is good. There is one thing that bothers me though. This particular test requires a lot of set-up. I have to populate rows in many tables. I also create a dozen or so appointments. When I look at the hibernate SQL output, ALL the SQL is put into a single transaction, with a SINGLE select for update occuring at the start of the SQl block. That means my row level lock is being set once for all appnt creation (which would be incorrect) and the lock is being held for a long time. What I really want is a new transaction evertime the save method is called so I hold the lock for a very short period of time.

    Can anyone comment on if what I'm seeing is an artifact of using AbstractAutorevenueTransactionalSpringContextTest, or should I be worried that this won't work correctly at run-time?

    Comment


    • #3
      I think I figured everything out. I'm posting my findings to add to the knowledge pool.

      Basically, when I switched from AbstractTransactionalSpringContextTests to AbstractDependencyInjectionSpringContextTests my SQL statements executed in the exact order I wanted. Now that I look back on it, this makes perfect sense.

      One of the things that took so long was that I had to setUp my tests to keep an open session when moving to AbstractDependencyInjectionSpringContextTests. Otherwise, I'd get the dreaded "lazy load error". Suprisingly, I didn't find a lot of examples for doing this on the web, so I'm posting my own spin on it in the hope that it will save someone frustration and headaches.

      I have an abstract base class that several of my tests extend from:

      Code:
      package com.autorevenue.persistence;
      
      
      import org.hibernate.Session;
      import org.hibernate.SessionFactory;
      
      import org.springframework.context.ApplicationContext;
      import org.springframework.orm.hibernate3.SessionHolder;
      import org.springframework.orm.hibernate3.SessionFactoryUtils;
      import org.springframework.test.AbstractDependencyInjectionSpringContextTests;
      import org.springframework.transaction.support.TransactionSynchronizationManager;
      
      public class MyTestClass extends AbstractDependencyInjectionSpringContextTests
      {
          protected String[] getConfigLocations()
          {
              return new String[] 
              {
                  "appointmentsContext-hibernate.xml"
              };
          }
          
      
        protected  void onSetUp() throws Exception
        {
            ApplicationContext c = getContext(contextKey());
            sessionFactory = (SessionFactory) c.getBean(SESSION_FACTORY);
            Session session = SessionFactoryUtils.getSession(sessionFactory, true);
            TransactionSynchronizationManager.bindResource(sessionFactory, new SessionHolder(session)); 
        }
        
        public void onTearDown() throws Exception 
        {
              SessionHolder holder = (SessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
              Session s = holder.getSession();
              s.flush();
              TransactionSynchronizationManager.unbindResource(sessionFactory);
              SessionFactoryUtils.releaseSession(s, sessionFactory);
         }
        
          protected static final String SESSION_FACTORY = "sessionFactory";
          protected SessionFactory sessionFactory = null;
      }
      Then, in the test class itself, all I need to do is this

      Code:
          public void onSetUp() throws Exception
          {
              super.onSetUp();
      
              // Populate database tables
          }
      
      
          public void onTearDown() throws Exception
          {
      
              // Remove rows added to tables
              
              super.onTearDown();
          }
      Seems to work ...

      Comment

      Working...
      X