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?

score 6 · Accepted Answer · answered May 15 '12 at 20:17

6

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.
  }
}

answered May 15 '12 at 20:17

krock

28,904
13
79
85

Thanks for the answer. Tried that. App failed to start. Error in the log: org.springframework.beans.NotWritablePropertyException: Invalid property 'defaultTransactionIsolation' of bean class [org.springframework.jdbc.datasource.DriverManagerDataSource]: Bean property 'defaultTransactionIsolation' is not writable or has an invalid setter method. Does the parameter type of the setter match the return type of the getter? It fails with the same exception if I type 1 instead of java.sql.Connection.TRANSACTION_READ_UNCOMMITTED or try to initialize other properties. – Andrey.Kozyrev May 15 '12 at 22:26
I tried this using an oracle datasource and got this error: `Cannot create PoolableConnectionFactory (READ_COMMITTED and SERIALIZABLE are the only valid transaction levels)`. So it seems that setting might be data source dependant. There is a `readOnly` attribute for the datasource config, I suggest you use that instead. – krock May 16 '12 at 03:36
1

Andrey, you need to add "pooled = true" under dataSource to fix your problem. – Lari Hotari May 16 '12 at 17:42

score 4 · Answer 2 · answered May 16 '12 at 23:57

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

How to specify default transaction isolation level in Grails

2 Answers2

Linked