How to set the SNAPSHOT isolation level in Spring

Question

Could anyone help me understand how to set isolation level to SNAPSHOT in Spring's XML config?

I am taking over a project used to be developed by someone else and in certain cases we get deadlocks when working with database. I've verified that despite the DB isolation level of SNAPSHOT, when the application issues a request, isolation level for that transaction changes to READ_COMMITTED. According to my brief research, if not set explicitly, hibernate uses the DEFAULT isolation, which for SQLServer 2012 is READ_COMMITTED.

Unfortunately, I am not an expert in either Spring or Hibernate, so I'll just give the configs that seem relevant:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName">
        <value>jdbc/ds</value>
    </property>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />     
    <property name="mappingResources">
        <list>
            ...
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${nn.hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${nn.hibernate.showSQL}</prop>
            <prop key="hibernate.generate_statistics">${nn.hibernate.generatestatics}</prop>
            <prop key="hibernate.hbm2ddl.auto">${nn.hibernate.hbm2ddl}</prop>
            <prop key="hibernate.jdbc.batch_size">${nn.hibernate.batchsize}</prop>
            <prop key="hibernate.cache.provider_class">${nn.hibernate.cache.provider_class}</prop>
            <prop key="hibernate.cache.use_second_level_cache">${nn.hibernate.cache.use_second_level_cache}</prop>
        </props>
    </property>
</bean>


<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory">
        <ref local="sessionFactory" />
    </property>
</bean>


<tx:advice id="txAdvice" transaction-manager="txManager">
    <tx:attributes>
        <tx:method name="save*" read-only="false" rollback-for="NNDBException"/>
        <tx:method name="update*" read-only="false" rollback-for="NNDBException"/>
        <tx:method name="checkFor*" read-only="false" rollback-for="NNDBException"/>
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>


<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

While researching possible solutions I also learnt that setting hibernate.connection.isolation when DataSource is provided is not effective.

From there I got to this example where IsolationLevelDataSourceAdapter is used to set isolation level on each instance of Connection. The configs became this:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName">
        <value>jdbc/ds</value>
    </property>
</bean>


<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource">
        <bean class="org.springframework.jdbc.datasource.IsolationLevelDataSourceAdapter">
            <property name="targetDataSource" ref="dataSource"/>
            <property name="isolationLevel" value="4096" />
        </bean>
    </property>
    <property name="mappingResources">
        <list>
            ...
        </list>
    </property> 
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${nn.hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${nn.hibernate.showSQL}</prop>
            <prop key="hibernate.generate_statistics">${nn.hibernate.generatestatics}</prop>
            <prop key="hibernate.hbm2ddl.auto">${nn.hibernate.hbm2ddl}</prop>
            <prop key="hibernate.jdbc.batch_size">${nn.hibernate.batchsize}</prop>
            <prop key="hibernate.cache.provider_class">${nn.hibernate.cache.provider_class}</prop>
            <prop key="hibernate.cache.use_second_level_cache">${nn.hibernate.cache.use_second_level_cache}</prop>
        </props>
    </property>
</bean>


<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory">
        <ref local="sessionFactory" />
    </property>
    <property name="allowCustomIsolationLevels" value="true" />
</bean>


<tx:advice id="txAdvice" transaction-manager="txManager">
    <tx:attributes>
        <tx:method name="save*" read-only="false" rollback-for="NNDBException"/>
        <tx:method name="update*" read-only="false" rollback-for="NNDBException"/>
        <tx:method name="checkFor*" read-only="false" rollback-for="NNDBException"/>
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>


<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

where I set isolation level to 4096 in sessionFactory. However, this is where I hit the wall: IllegalArgumentException: Only isolation constants allowed, which I presume are DEFAULT, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE.

Is there a proven way to set isolation level to SNAPSHOT (or 4096) in the context of the configs that I have?

Thank you.

Answer 1

The IsolationLevelDataSourceAdapter checks for the standard isolation level codes, which is a clear limitation (I suggest you file a Spring JIRA issue for this).

To work around it, you need to extend the IsolationLevelDataSourceAdapter class:

public class CustomIsolationLevelDataSourceAdapter extends IsolationLevelDataSourceAdapter {

    private final Integer customIsolationLevel;

    public CustomIsolationLevelDataSourceAdapter(Integer customIsolationLevel) {
        this.customIsolationLevel = customIsolationLevel;
    }

    @Override
    protected Integer getIsolationLevel() {
        return customIsolationLevel != null ? customIsolationLevel : super.getIsolationLevel();
    }
}

and use this dataSource instead:

<bean class="my.package.CustomIsolationLevelDataSourceAdapter">
    <constructor-arg value="4096"/>
</bean>