Saturday, April 12, 2008

Tx isolation levels with JTA and Spring

I just "rediscovered" that JTA does not support custom isolation levels. I was using Spring's JtaTransactionManager together with @Transactional and setting custom isolation levels for different operations. The application was a legacy EJB2.x based application which had been migrated to Spring in order to simplify the deployment and eliminate the horrible realms of XML. But after eliminating the SLSBs lock problems in DB2 were observed. It turned out that some of the heaviest queries were not being run with the intended isolation value as defined in the annotations, just the default datasource level which is usually READ_COMMITTED. This level is the equivalent of "cursor stability" level in DB2.
The Cursor Stability (CS) isolation level is the default isolation level and locks any row on which the cursor is positioned during a unit of work
This was really not what was intended since it locks rows with a shared lock. Other transactions can still access the table for selects but holding the locks places a heavy load on the db. In this case what was desired was to have a READ_UNCOMMITTED isolation level for those completely read-only operations.

There are usually two ways to solve the problem of custom tx levels with JTA.
  • Define a datasource for each isolation level you intend to use in your application and then use IsolationLevelDataSourceRouter
  • Use a vendor-specific extension that wraps the JNDI datasource
It's typical to learn this the hard way so I'd like to recommend this article that shows how to specify custom isolation levels with Spring and JTA in detail.

1 comment: