Custom Isolation Levels with JTA and Spring

15/12/2009

My current project involves multiple databases -- MySQL and MS SQL Server. From the point of view of the application that I work on, there is one central MS SQL database, one additional MS SQL database for jBPM (the workflow/service orchestration framework we're using), and a number of auxilliary databases interfacing with different component of the system, some of which run on MS SQL, some on MySQL.

The obvious choice for transaction management in such a setup is to use JTA. As the application is supposed to be deployed on a Tomcat 6 application server, and as I like to use Jetty during development, both of which do not come with a transaction manager, a JTA transaction manager had to be found. After a lot of experimenting, and after a lot of trouble with JOTM (which did not seem to rollback, ever) and Atomikos (which had problems with some of the XA datasources), I settled for the Bitronix Transaction Manager (BTM) because it was reasonably simple to set up and just worked.

MS SQL server needs a little tweaking to support XA transactions, but after that has been set up, they work as expected.

Using MS SQL server with some rather long-running transactions revealed a peculiar side of MS SQL server: By default, updates work through row locks, meaning that long transactions writing to the database block simultanious reads of the table (for display in a list, in our case), until they are committed. As we perform large inserts on multiple databases, we also have very long transactions, resulting in an unusable UI. I don't know why this is the default setting for MS SQL, as most other databases (Oracle, MySQL/InnoDB) support concurrent read/write IO with such an ease that you rarely have to worry about locking issues.

Anyway, MS SQL has a per-schema switch to enable non-locking concurrency via row versioning: READ_COMMITTED_SNAPSHOT. I tried it, and it removed the locking instantly. However, it is either not implemented well in SQL Server 2008, or does not play with XA transactions: There is a time after a transaction is committed (i.e. the COMMIT command has executed) where subsequent reads from the database will result in stale data! When a lot of writing is done during a transaction, the timespan can be multiple seconds. This resulted in unexpected behaviour in our workflows, and we had to abandon READ_COMMITTED_SNAPSHOT.

Another option to ensure non-blocking reads is changing the isolation level to READ_UNCOMMITTED, which is acceptable for our UI. The problem is that JTA does not support setting custom isolation levels for individual transactions: You have to rely on proprietary features of the transaction manager to have access to such functionality. Spring has a couple of custom JtaTransactionManager implementations for various JTA implementations. BTM, however, does not have this feature.

The standards-compliant workaround is to set the isolation level directly on the JDBC connection. Luckily, the Spring framework has a couple of helper classes that do just that:

With that set up, custom isoltation levels work like a charm -- using only standard JTA and JDBC.

The other, perfectly viable workaround is to use any other RDBMS than MS SQL. This is my first project where MS SQL holds the main database, and I can't say I'm impressed.

Below are the relevant bits of the configuration to make this work.

This is the BTM configuration in jetty-env.xml:

<Call name="getConfiguration" class="bitronix.tm.TransactionManagerServices">
  <Set name="defaultTransactionTimeout">900</Set>
  <Set name="logPart1Filename">./target/work/btm1.tlog</Set>
  <Set name="logPart2Filename">./target/work/btm2.tlog</Set>
</Call>

<New class="org.mortbay.jetty.plus.naming.Resource">
  <Arg>javax.transaction.TransactionManager</Arg>
  <Arg>
    <Call name="getTransactionManager" class="bitronix.tm.TransactionManagerServices" />
  </Arg>
</New>

<New class="org.mortbay.jetty.plus.naming.Transaction">
  <Arg>
    <Call name="getTransactionManager" class="bitronix.tm.TransactionManagerServices" />
  </Arg>
</New>

<New id="obamaDatasource" class="org.mortbay.jetty.plus.naming.Resource">
  <Arg>
    <Ref id="wac" />
  </Arg>
  <Arg>jdbc/obamaDatasource</Arg>
  <Arg>
     <New class="bitronix.tm.resource.jdbc.PoolingDataSource">
      <Set name="className">com.microsoft.sqlserver.jdbc.SQLServerXADataSource</Set>
      <Set name="uniqueName">ds</Set>
      <Set name="allowLocalTransactions">true</Set>
      <Set name="minPoolSize">0</Set>
      <Set name="maxPoolSize">10</Set>
      <Get name="driverProperties">
        <Put name="URL">jdbc:sqlserver://xxxx;DatabaseName=yyyy</Put>
        <Put name="user">username</Put>
        <Put name="password">password</Put>
      </Get>
      <Call name="init" />
    </New>
  </Arg>
</New>

The relevant bits from web.xml:

<resource-ref>
  <description>UserTransaction</description>
  <res-ref-name>UserTransaction</res-ref-name>
  <res-type>javax.transaction.UserTransaction</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

<resource-ref>
  <description>TransactionManager</description>
  <res-ref-name>javax.transaction.TransactionManager</res-ref-name>
  <res-type>javax.transaction.TransactionManager</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

<resource-ref>
  <description>Obama DataSource</description>
  <res-ref-name>jdbc/obamaDatasource</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

And the relevant sections from the Spring configuration:

<!-- JTA transaction manager -->

<bean id="jtaTransactionManager" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="resourceRef" value="true" />
  <property name="jndiName" value="javax.transaction.TransactionManager" />
  <property name="expectedType" value="javax.transaction.TransactionManager" />
</bean>
<bean id="userTransaction" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="resourceRef" value="true" />
  <property name="jndiName" value="UserTransaction" />
  <property name="expectedType" value="javax.transaction.UserTransaction" />
</bean>

<!-- Data source from the container via JNDI -->
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="jndiName" value="jdbc/obamaDatasource" />
  <property name="resourceRef" value="true" />
  <property name="expectedType" value="javax.sql.DataSource" />
</bean>


<!-- Transaction management -->
<bean id="transactionManager"
  class="org.springframework.transaction.jta.JtaTransactionManager">
  <property name="transactionManager" ref="jtaTransactionManager" />
  <property name="userTransaction" ref="userTransaction" />
  <property name="allowCustomIsolationLevels" value="true"/>
</bean>


<tx:annotation-driven transaction-manager="transactionManager"
  mode="aspectj" />

<!--
JPA Entity manager configuration through Spring. See
http://static.springframework.org/spring/docs/2.5.x/reference/orm.html#orm-jpa-setup.
-->
<bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource">
    <!-- Workaround to allow custom isolation levels -->
    <bean class="org.springframework.jdbc.datasource.lookup.IsolationLevelDataSourceRouter" >
      <property name="defaultTargetDataSource" ref="dataSource" />
      <property name="targetDataSources">
        <map>
          <entry key="ISOLATION_READ_UNCOMMITTED">
            <bean class="org.springframework.jdbc.datasource.IsolationLevelDataSourceAdapter">
              <property name="targetDataSource" ref="dataSource" />
              <property name="isolationLevelName" value="ISOLATION_READ_UNCOMMITTED" />
            </bean>
          </entry>
        </map>
      </property>
    </bean>
  </property>
  <property name="jpaVendorAdapter">
    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
      <property name="showSql" value="false" />
      <property name="generateDdl" value="true" />
    </bean>
  </property>
  <property name="persistenceUnitName" value="My_PU" />
</bean>

Comments