I was working on setting the transaction isolation level for the first CF9 ORM application I have put into production and thought I would share what I learned.

The first thing I learned is that by default, hibernate will use whatever isolation the datasource it is using has set.  In the drivers Coldfusion uses for MSSQL, the default is TRANSACTION_READ_COMMITTED. 

What I needed for my application was TRANSACTION_READ_UNCOMMITTED, so I knew that this had to change.  I researched how to change it at the datasource level, but couldn't find a "quick and dirty" way to do that, so I decided that since this application was using hibernate ORM, I would set it there.  If anyone of you knows how to change this setting at the datasource level on CF9, feel free to chime in.  I know how to change it on CF 6/7, but that doesn't work on CF 9.

So, since changing the isolation level isn't one of the settings available in the ormsettings, I had to create a custom hibernate config file.

At first I thought this would be difficult as I thought that I would have to insert all the "standard" settings the Coldfusion implementation of hibernate was already using, but it turns out, you only have to set any additional properties you wish to use.  Here is a list of available hibernate configuration options.

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html

So, on to some code...

Here is my newly created hibernate config file. 

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<!-- a SessionFactory instance listed as /jndi/name -->

<session-factory

name="java:hibernate/SessionFactory">

<!-- properties -->

<property name="connection.isolation">1</property>

</session-factory>

</hibernate-configuration>

Notice one thing in the config file.  <property name="connection.isolation">1</property>.  Why one you ask...

Here is the int values for each of the isolation levels available.

java.sql.Connection
public static final int	TRANSACTION_NONE	0
public static final int	TRANSACTION_READ_COMMITTED	2
public static final int	TRANSACTION_READ_UNCOMMITTED	1
public static final int	TRANSACTION_REPEATABLE_READ	4
public static final int	TRANSACTION_SERIALIZABLE	8

Source: http://java.sun.com/j2se/1.5.0/docs/api/constant-values.html#java.sql.Connection.TRANSACTION_READ_COMMITTED

Then in my application.cfc I set

<cfset this.ormsettings = {dialect="MicrosoftSQLServer", ormconfig="hibernate.xml"}>  

Now, be sure that you have your hibernate.xml file somewhere outside of webroot to protect it from being read by any evil eyes.

There you go, hope this helps you as it took me a lot of digging to figure it out