How to specify default transaction isolation level in Grails

Question

I cannot figure out how to specify default transaction isolation level in Grails application . Please help and point where my mistake is. Following are the details.

Grails: 1.3.7

Database: Sql Server 2008.

DataSource.groovy:

dataSource {
  ...
  driverClassName = "net.sourceforge.jtds.jdbc.Driver"
  dialect = org.hibernate.dialect.SQLServerDialect
  defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_UNCOMMITTED
}

hibernate {
  ...
  connection.isolation = java.sql.Connection.TRANSACTION_READ_UNCOMMITTED
}

Then I'm navigating through the application and execute the following query at the same time:

SELECT session_id, host_name, program_name, login_name, status, transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE host_name IS NOT NULL AND login_name = 'cm'
ORDER BY host_name, program_name

that returns:

session_id  host_name   program_name    login_name  status  transaction_isolation_level
61          ANDREYK-WS  jTDS            cm          running 2

2 means READ_COMMITTED. I expect to see 1, i.e. READ_UNCOMMITTED.

If I explicitly specify: @Transactional(isolation=Isolation.READ_UNCOMMITTED)

The query above returns 1 as expected. However I do not want to attribute all the services in my application. What am I missing?

Answer 1

This needs to be set under the properties attribute of the datasource configuration i.e.

dataSource {
  ...
  driverClassName = "net.sourceforge.jtds.jdbc.Driver".
  dialect = org.hibernate.dialect.SQLServerDialect
  properties {
    defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_UNCOMMITTED.
  }
}

Answer 2

I have a bit of a variation of this as I have multiple DS

At the top of your datasources define a shared map of properties (tune this to your environment):

def defaultConnectionProperties =  [
    maxActive: 50,
    maxIdle: 25,
    minIdle: 5,
    initialSize: 5,
    minEvictableIdleTimeMillis: 60000,
    timeBetweenEvictionRunsMillis: 60000,
    maxWait: 10000,
    defaultTransactionIsolation: java.sql.Connection.TRANSACTION_READ_UNCOMMITTED
]

Then each DS is something like:

dataSource {
    pooled = true
    driverClassName = "net.sourceforge.jtds.jdbc.Driver"
//    driverClassName = "com.p6spy.engine.spy.P6SpyDriver" // use this driver to enable p6spy logging
    //readOnly = "true"
    properties = defaultConnectionProperties
}

Restart your Grails app.

The wierd thing is I see the initial transaction_isolation_level = 2, but when I actually hit the DB the connection properties seem to be set and it flicks to a 1.

Also you can inspect: grailsApplication.config and look for the datasources and confirm the settings there