Grails: Fixing timeout exceptions and hibernate AssertionFailure when execute a SQL query

Some times, for a particular reason you need to perform a SQL query instead of execute a Grails/GORM operation. In these cases you can do the job writing a method in your domain models, or in your service classes like the following example:

class MyService {

 def sessionFactory

 int myCustomOperation() { 
  def session = sessionFactory.openSession()
  def sql = session.createSQLQuery(
   "SELECT field1 FROM ...MY CRAZY QUERY ... LIMIT 1")
  sql.uniqueResult().intValue()
 }
}

As you can seen, a new database session will open each time the function will call, so if the method is massively called by the application, it's possible that after the 30^th or more calls, a timeout exception will rise, because the BBDD engine doesn't have more connections to give to our application.

A possible solution can be replace the openSession() call by a getCurrentSession() call, I have tried it, but the result is a new exception given the concurrent operations performed:

hibernate.AssertionFailure  - an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session)

So, coming back to the first approach, we can fix it with something that in Grails guides and Spring guides will found like something that is not necessary to do: close the session explicitly. That's all, in general lines, doing the close() operation is not necessary because Grails (Spring) do it for us, but when the operation is executed so repetitively in a short time, the framework is not able to close the connections so fast as the new sessions are opened, and in a few of seconds the limit of concurrent connections is reached.

The code above can be fixed with a few extra lines:

int myCustomOperation() {

def session = null

try {
  def query = "SELECT field1 FROM ...MY CRAZY QUERY ... LIMIT 1"
  session = sessionFactory.openSession()
  def sql = session.createSQLQuery(query)
  sql.uniqueResult().intValue()

} finally {
  if(session) session.close()
 }

}

Exceptions during the startup with "Searchable" module

Also, if you have activated the "searchble" module in your project, and the SQL method is a transient field, you have to add the field in the defaultExcludedProperties key in the grails-app/conf/Searchable.groovy configuration file, or set to false the bulkIndexOnStartup key in the same file. This is because the session is not available during the startup process for this type of operations.