Session Binding in SessionBinderJobListener causes nested withNewSession, withTransaction to leak database connections

[kaaloo]

Hi Sergey,

I have hunted down an issue in our app for the past few days and am now at a point where I have a grails project zip that reproduces this. The problem occurs when something like this takes places in the grails job:

withNewSession {
// Some code
withTransaction {
// Some code
}
}

This works great in grails itself outside of the quartz plugin, but when called from a quartz job, then this type of code will leave database connections open in the connection pool and they can run out of course if this is done enough number of times. The standard pool in a newly created grails app maxes out at 8 so you'll see in the project that illustrates the bug that after 8 times the test will hang for 20s, retry, and then eventually fail with an exception because the pool is empty.

You will see that I copied code over from SessionBinderJobListener directly in the test to setup the session and to shut it down in the tearDown() method. I imagine the issue is in the setup method which copies over the jobtoBeExecuted method below since tearDown() is only called after the failure. If I cannot attach a file to this issue I will send you the zip by email.

public void jobToBeExecuted(JobExecutionContext context) {
Session session = SessionFactoryUtils.getSession(sessionFactory, true);
session.setFlushMode(FlushMode.AUTO);
TransactionSynchronizationManager.bindResource(sessionFactory, new SessionHolder(session));
if (LOG.isDebugEnabled()) LOG.debug("Hibernate Session is bounded to Job thread");
}

Thanks!
Luis

[nebolsin]

Test case that reproduces the problem, provided by Luis: http://cl.ly/341G0j242O0B0z0G2t1a.

[kaaloo]

Sergey,

I have done some further investigation into this issue and have implemented a workaround. I no longer believe the issue is tied to the quartz plugin itself but is shared between grails and hibernate. For instance this issue is directly related:

http://jira.grails.org/browse/GRAILS-7780

I have a workaround that works for the above grails issue and this one that looks like this (careful its a big ugly, but you'll get the idea). I'm not sure its worth updating the SessionBinderJobListener since its code is only run at the start and end of a job though and if the app uses withNewSession it will most likely get the db connection leak issue anyway. I would like to report something coherent to grails and or hibernate but I'm not an expert.

safeWithNewSession = { Closure callable ->
  HibernateTemplate template = new HibernateTemplate(sessionFactory)
  SessionHolder sessionHolder = TransactionSynchronizationManager.getResource(sessionFactory)
  if (sessionHolder) {
    // This is the only way to force hibernate to actually close the db connection in the new session
    TransactionSynchronizationManager.unbindResource(sessionFactory)
  }
  Session previousSession = sessionHolder?.session
  try {
    template.alwaysUseNewSession = true
    template.execute({ Session session ->
      TransactionSynchronizationManager.bindResource(sessionFactory, new SessionHolder(session))

      callable(session)
    } as HibernateCallback)
  }
  finally {
    // This is the only way to force hibernate to actually close the db connection in the new session
    TransactionSynchronizationManager.unbindResource(sessionFactory)
    if (previousSession) {
      TransactionSynchronizationManager.bindResource(sessionFactory, new SessionHolder(previousSession))
    }
  }
}