A beginner’s guide to transaction isolation levels in enterprise Java

Introduction

A relational database strong consistency model is based on ACID transaction properties. In this post we are going to unravel the reasons behind using different transaction isolation levels and various configuration patterns for both resource local and JTA transactions.

Isolation and consistency

In a relational database system, atomicity and durability are strict properties, while consistency and isolation are more or less configurable. We cannot even separate consistency from isolation as these two properties are always related.

The lower the isolation level, the less consistent the system will get. From the least to the most consistent, there are four isolation levels:

  • READ UNCOMMITTED
  • READ COMMITTED (protecting against dirty reads)
  • REPEATABLE READ (protecting against dirty and non-repeatable reads)
  • SERIALIZABLE (protecting against dirty, non-repeatable reads and phantom reads)

Although the most consistent SERIALIZABLE isolation level would be the safest choice, most databases default to READ COMMITTED instead. According to Amdahl’s law, to accommodate more concurrent transactions, we have to reduce the serial fraction of our data processing. The shorter the lock acquisition interval, the more requests a database can process.

Isolation levels

As we previously demonstrated, application level repeatable reads paired with an optimistic locking mechanism are very convenient for preventing lost updates in long conversations.

In a highly concurrent environment, optimistic locking might lead to a high transaction failure rate. Pessimistic locking, like any other queuing mechanism might accommodate more transactions when giving a sufficient lock acquisition time interval.

Database and isolation levels

Apart from MySQL (which uses REPEATABLE_READ), the default isolation level of most relational database systems is READ_COMMITTED. All databases allow you to set the default transaction isolation level.

Typically, the database is shared among multiple applications and each one has its own specific transaction requirements. For most transactions the READ_COMMITTED isolation level is the best choice and we should only override it for specific business cases.

This strategy proves to be the very efficient, allowing us to have stricter isolation levels for just a subset of all SQL transactions.

DataSource isolation level

The JDBC Connection object allows us to set the isolation level for all transactions issued on that specific connection. Establishing a new database connection is a resource consuming process, so most applications use a connection pooling DataSource. The connection pooling DataSource can also set the default transaction isolation level:

Compared to the global database isolation level setting, the DataSource level transaction isolation configurations are more convenient. Each application may set its own specific concurrency control level.

We can even define multiple DataSources, each one with a per-defined isolation level. This way we can dynamically choose a specific isolation level JDBC Connection.

Hibernate isolation level

Because it has to support both resource local and JTA transactions, Hibernate offers a very flexible connection provider mechanism.

JTA transactions require an XAConnection and it’s the JTA transaction manager responsibility to provide XA compliant connections.

Resource local transactions can use a resource local DataSource and for this scenario, Hibernate offers multiple connection provider options:

  • Driver Manager Connection Provider (doesn’t pool connections and therefore it’s only meant for simple testing scenarios)
  • C3P0 Connection Provider (delegating connection acquiring calls to an internal C3P0 connection pooling DataSource)
  • DataSource Connection Provider (delegating connection acquiring calls to an external DataSource.

Hibernate offers a transaction isolation level configuration called hibernate.connection.isolation, so we are going to check how all the aforementioned connection providers behave when being given this particular setting.

For this we are going to:

  1. Create a SessionFactory
    @Override
    protected SessionFactory newSessionFactory() {
        Properties properties = getProperties();
    
        return new Configuration()
            .addProperties(properties)
            .addAnnotatedClass(SecurityId.class)
            .buildSessionFactory(
                new StandardServiceRegistryBuilder()
                    .applySettings(properties)
                    .build()
        );
    }
    
  2. Open a new Session and test the associated connection transaction isolation level
    @Test
    public void test() {
        Session session = null;
        Transaction txn = null;
        try {
            session = getSessionFactory().openSession();
            txn = session.beginTransaction();
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
                }
            });
            txn.commit();
        } catch (RuntimeException e) {
            if ( txn != null && txn.isActive() ) txn.rollback();
            throw e;
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }
    

The only thing that differs is the connection provider configuration.

Driver Manager Connection Provider

The Driver Manager Connection Provider offers a rudimentary DataSource wrapper for the configured database driver. You should only use it for test scenarios since it doesn’t offer a professional connection pooling mechanism.

@Override
protected Properties getProperties() {
	Properties properties = new Properties();
        properties.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
        //driver settings
        properties.put("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");
        properties.put("hibernate.connection.url", "jdbc:hsqldb:mem:test");
        properties.put("hibernate.connection.username", "sa");
        properties.put("hibernate.connection.password", "");
        //isolation level
        properties.setProperty("hibernate.connection.isolation", String.valueOf(Connection.TRANSACTION_SERIALIZABLE));
	return properties;
}

The test generates the following output:

WARN  [main]: o.h.e.j.c.i.DriverManagerConnectionProviderImpl - HHH000402: Using Hibernate built-in connection pool (not for production use!)
DEBUG [main]: c.v.h.m.l.t.TransactionIsolationDriverConnectionProviderTest - Transaction isolation level is SERIALIZABLE

The Hibernate Session associated JDBC Connection is using the SERIALIZABLE transaction isolation level, so the hibernate.connection.isolation configuration works for this specific connection provider.

C3P0 Connection Provider

Hibernate also offers a build-in C3P0 Connection Provider. Like in the previous example, we only need to provide the driver configuration settings and Hibernate instantiate the C3P0 connection pool on our behalf.

@Override
protected Properties getProperties() {
	Properties properties = new Properties();
        properties.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
        //log settings
        properties.put("hibernate.hbm2ddl.auto", "update");
        properties.put("hibernate.show_sql", "true");
        //driver settings
        properties.put("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");
        properties.put("hibernate.connection.url", "jdbc:hsqldb:mem:test");
        properties.put("hibernate.connection.username", "sa");
        properties.put("hibernate.connection.password", "");
        //c3p0 settings
        properties.put("hibernate.c3p0.min_size", 1);
        properties.put("hibernate.c3p0.max_size", 5);
        //isolation level
        properties.setProperty("hibernate.connection.isolation", String.valueOf(Connection.TRANSACTION_SERIALIZABLE));
	return properties;
}

The test generates the following output:

Dec 19, 2014 11:02:56 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Dec 19, 2014 11:02:56 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.2.1 [built 20-March-2013 10:47:27 +0000; debug? true; trace: 10]
DEBUG [main]: c.v.h.m.l.t.TransactionIsolationInternalC3P0ConnectionProviderTest - Transaction isolation level is SERIALIZABLE

So, the hibernate.connection.isolation configuration works for the internal C3P0 connection provider too.

DataSource Connection Provider

Hibernate doesn’t force you to use a specific connection provider mechanism. You can simply supply a DataSource and Hibernate will use it whenever a new Connection is being requested. This time we’ll create a full-blown DataSource object and pass it through the hibernate.connection.datasource configuration.

@Override
protected Properties getProperties() {
	Properties properties = new Properties();
        properties.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
        //log settings
        properties.put("hibernate.hbm2ddl.auto", "update");
        //data source settings
        properties.put("hibernate.connection.datasource", newDataSource());
        //isolation level
        properties.setProperty("hibernate.connection.isolation", String.valueOf(Connection.TRANSACTION_SERIALIZABLE));
	return properties;
}

protected ProxyDataSource newDataSource() {
        JDBCDataSource actualDataSource = new JDBCDataSource();
        actualDataSource.setUrl("jdbc:hsqldb:mem:test");
        actualDataSource.setUser("sa");
        actualDataSource.setPassword("");
        ProxyDataSource proxyDataSource = new ProxyDataSource();
        proxyDataSource.setDataSource(actualDataSource);
        proxyDataSource.setListener(new SLF4JQueryLoggingListener());
        return proxyDataSource;
}    

The test generates the following output:

DEBUG [main]: c.v.h.m.l.t.TransactionIsolationExternalDataSourceConnectionProviderTest - Transaction isolation level is READ_COMMITTED

This time, the hibernate.connection.isolation doesn’t seem to be taken into consideration. Hibernate doesn’t override external DataSources, so this setting is useless in this scenario.

If you are using an external DataSource (e.g. maybe through JNDI), then you need to set the transaction isolation at the external DataSource level.

To fix our previous example, we just have to configure the external DataSource to use a specific isolation level:

protected ProxyDataSource newDataSource() {
	JDBCDataSource actualDataSource = new JDBCDataSource();
	actualDataSource.setUrl("jdbc:hsqldb:mem:test");
	actualDataSource.setUser("sa");
	actualDataSource.setPassword("");
	Properties properties = new Properties();
	properties.setProperty("hsqldb.tx_level", "SERIALIZABLE");
	actualDataSource.setProperties(properties);
	ProxyDataSource proxyDataSource = new ProxyDataSource();
	proxyDataSource.setDataSource(actualDataSource);
	proxyDataSource.setListener(new SLF4JQueryLoggingListener());
	return proxyDataSource;
}

Generating the following output:

DEBUG [main]: c.v.h.m.l.t.TransactionIsolationExternalDataSourceExternalconfgiurationConnectionProviderTest - Transaction isolation level is SERIALIZABLE

Java Enterprise transaction isolation support

Hibernate has a built-in Transaction API abstraction layer, isolating the data access layer from the transaction management topology (resource local or JTA). While we can develop an application using Hibernate transaction abstraction only, it’s much more common to delegate this responsibility to a middle-ware technology (JEE or Spring).

Java Enterprise Edition

JTA (Java Transaction API specification) defines how transactions should be managed by a JEE compliant application server. On the client side, we can demarcate the transaction boundaries using the TransactionAttribute annotation. While we have the option of choosing the right transaction propagation setting, we cannot do the same for the isolation level.

JTA doesn’t support transaction-scoped isolation levels and so we have to resort to vendor-specific configurations for providing an XA DataSource with a specific transaction isolation setting.

Spring

Spring @Transactional annotation is used for defining a transaction boundary. As opposed to JEE, this annotation allows us to configure:

  • isolation level
  • exception types rollback policy
  • propagation
  • read-only
  • timeout

As I will demonstrate later in this article, the isolation level settings is readily available for resource local transactions only. Because JTA doesn’t support transaction-scoped isolation levels, Spring offers the IsolationLevelDataSourceRouter to overcome this shortcoming when using application server JTA DataSources.

Because most DataSource implementations can only take a default transaction isolation level, we can have multiple such DataSources, each one serving connections for a specific transaction isolation level.

The logical transaction (e.g. @Transactional) isolation level setting is introspected by the IsolationLevelDataSourceRouter and the connection acquire request is therefore delegated to a specific DataSource implementation that can serve a JDBC Connection with the same transaction isolation level setting.

So, even in JTA environments, the transaction isolation router can offer a vendor-independent solution for overriding the default database isolation level on a per transaction basis.

Spring transaction-scoped isolation levels

Next, I’m going to test the Spring transaction management support for both resource local and JTA transactions.

For this, I’ll introduce a transactional business logic Service Bean:

@Service
public class StoreServiceImpl implements StoreService {

    protected final Logger LOGGER = LoggerFactory.getLogger(getClass());

    @PersistenceContext(unitName = "persistenceUnit")
    private EntityManager entityManager;

    @Override
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void purchase(Long productId) {        
        Session session = (Session) entityManager.getDelegate();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
            }
        });
    }
}

The Spring framework offers a transaction management abstraction that decouples the application logic code from the underlying transaction specific configurations. The Spring transaction manager is only a facade to the actual resource local or JTA transaction managers.

Migrating from resource local to XA transactions is just a configuration detail, leaving the actual business logic code untouched. This wouldn’t be possible without the extra transaction management abstraction layer and the cross-cutting AOP support.

Next, we are going to test how various specific transaction managers support transaction-scope isolation level overriding.

JPA transaction manager

First, we are going to test the JPA Transaction Manager:

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

When calling our business logic service, this is what we get:

DEBUG [main]: c.v.s.i.StoreServiceImpl - Transaction isolation level is SERIALIZABLE

The JPA transaction manager can take one DataSource only, so it can only issue resource local transactions. In such scenarios, Spring transaction manager is able to override the default DataSource isolation level (which is READ COMMITTED in our case).

JTA transaction manager

Now, let’s see what happens when we switch to JTA transactions. As I previously stated, Spring only offers a logical transaction manager, which means we also have to provide a physical JTA transaction manager.

Traditionally, it was the enterprise application server (e.g. Wildfly, WebLogic) responsibility to provide a JTA compliant transaction manager. Nowadays, there is also a great variety of stand-alone JTA transaction managers:

In this test, we are going to use Bitronix:

<bean id="jtaTransactionManager" factory-method="getTransactionManager"
	  class="bitronix.tm.TransactionManagerServices" depends-on="btmConfig, dataSource"
	  destroy-method="shutdown"/>

<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
	<property name="transactionManager" ref="jtaTransactionManager"/>
	<property name="userTransaction" ref="jtaTransactionManager"/>
</bean>

When running the previous test, we get the following exception:

org.springframework.transaction.InvalidIsolationLevelException: JtaTransactionManager does not support custom isolation levels by default - switch 'allowCustomIsolationLevels' to 'true'

So, let’s enable the custom isolation level setting and rerun the test:

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

The test gives us the following output:

DEBUG [main]: c.v.s.i.StoreServiceImpl - Transaction isolation level is READ_COMMITTED

Even with this extra configuration, the transaction-scoped isolation level wasn’t propagated to the underlying database connection, as this is the default JTA transaction manager behavior.

For WebLogic, Spring offers a WebLogicJtaTransactionManager to address this limitation, as we can see in the following Spring source-code snippet:

// Specify isolation level, if any, through corresponding WebLogic transaction property.
if (this.weblogicTransactionManagerAvailable) {
	if (definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
		try {
			Transaction tx = getTransactionManager().getTransaction();
			Integer isolationLevel = definition.getIsolationLevel();
			/*
			weblogic.transaction.Transaction wtx = (weblogic.transaction.Transaction) tx;
			wtx.setProperty(ISOLATION_LEVEL_KEY, isolationLevel);
			*/
			this.setPropertyMethod.invoke(tx, ISOLATION_LEVEL_KEY, isolationLevel);
		}
		catch (InvocationTargetException ex) {
			throw new TransactionSystemException(
					"WebLogic's Transaction.setProperty(String, Serializable) method failed", ex.getTargetException());
		}
		catch (Exception ex) {
			throw new TransactionSystemException(
					"Could not invoke WebLogic's Transaction.setProperty(String, Serializable) method", ex);
		}
	}
}
else {
	applyIsolationLevel(txObject, definition.getIsolationLevel());
}

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

Transaction management is definitely not a trivial thing, and with all the available frameworks and abstraction layers, it really becomes more complicated than one might think.

Because data integrity is very important for most business applications, your only option is to master your current project data layer framework stack.

Code available for Hibernate and JPA.

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

17 thoughts on “A beginner’s guide to transaction isolation levels in enterprise Java

    1. Thanks for the update. I remember there used to be some vendor specific descriptor files to set specific transaction attributes. But that was in the J2EE days and I have mostly used Spring ever since.

  1. Very beautiful and useful post to get a headstart in transactions. Thanks Vlad. I liked it how you started with theory and demonstrated with some practical examples from widely used frameworks.

    God bless you.
    -Riaz

  2. When the DB creates a result set on “committed read” isolation, as the result is being prepared, the DB tries to open a shared lock on each row of the result set to ensure the the data is committed. Certain DBs don’t handle this well and wait for a lock time-out on each row of the result set, instead of just returning the last committed state of the row. This sometimes results in queries performing poorly with little or no timing consistency when the same query is executed multiple times. We have found this on Informix and DB2, but not on Oracle. Informix has a “Last Committed” flag which can be set to influence the “Committed Read” behavior. Not sure if any one else has experienced a similar behavior. The isolation setting seems to be more relevant for updates than reads as the data returned to a browser could already be stale.

  3. Vlad – This is great article ! I have one question. If isolation level is changed (say to SERIALIZABLE) on connection level in one application and if other total separate application have lower isolation level (say READ_COMMITED), Will other application wait while reading the same row in RDBMS when first application is reading it ? In general, is there any advantage of connection level isolation if not all apps are using SERIALIZABLE level.

    1. You can combine SERIALIZABLE transactions with READ_COMMITTED ones. The SERIALIZABLE transactions will acquire more locks and so other transactions will have to wait for those locks to be releases. If your database supports MVCC, then readers won’t be blocked but the SERIALIZABLE transaction will throw an exception if any concurrent transaction has changed the same rows as the SERIALIZABLE transaction. If the database doesn’t support MVCC, hard locks will be acquired to prevent such modifications. Using SERIALIZABLE might be justified for some use cases, but usually you can simply use FOR UPDATE statements with READ_COMMITTED and application-level optimistic locking.

  4. Hi Vlad,

    Great article. Can you mix the use of JEE and Spring with regards the isolation level? In essence we are using EJB’s as transaction boundaries and inject spring beans into them. Is it possible to use the @Transaction annotation on the injected spring bean? Will the transaction started on the EJB propagate yet take on the isolation level defined?

    Thank you,
    Garrick

    1. I think it can work if you use a Spring application server transaction manager, like WebSphereUowTransactionManager. You can use the @Transaction annotation for sure in your Spring beans but I’m not sure if Spring can be awrae of the transactions started in the application server layer.

  5. Great, thx.
    Since migration of our hibernate based application from SQL Server 2005 to 2012, we observe transaction error (“Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect”). Do you know if there exists a difference between them in transaction management (isolation levels…) or an adaptation to perform on hibernate use into our application ?
    Richard

  6. Hi Vlad,

    Great article. I have a case with spring-hibernate, when I query data, the result return stale data. After I read this article, I realize the problem is from MySQL isolation level (REPEATABLE_READ). Then I change isolation setting in spring @Transactional, but doesn’t work. Then I change it at datasource config (I using hikaricp), it’s working.
    My question, what different if I set isolation level at @Transactional and datasource config?

    My method looks like this
    @Transactional(value=”transactionManagerMaria”, propagation = Propagation.NEVER,
    noRollbackFor = Throwable.class, readOnly = true, isolation=Isolation.READ_COMMITTED)
    public returnType methodName(Param param) {
    currentSession().doWork(new Work() {
    //select
    }
    }

    and here is my datasource config:

    ${cacheDb.url}
    ${cacheDb.user}
    ${cacheDb.password}

  7. I have a question about the correct location of @Transactional; Maybe this is not the perfect place to post this question but I like to hear your opinion as you always have deep analysis.
    Should it be placed on the service classes or the repository classes?

    After some research and some analysis, I decided the following:
    – If a service method has at least two DB operations (assume at least one write operation), then definitely we should annotate the service and not the repository because we want all those operations to be executed in one transaction (Atomic).
    – If a service method has only ONE DB operation and this DB operation does not care about the other logic in the method (i.e. if an exception happens or not), then I would put it on the repository. Take this use case:

    public void anyServiceMethod(){
    /*assume you have non-DB operations that take a lot of time (e.g. 7 seconds). For example, getting data from a WS, read and save files ..etc */

    repository.save(entity); //the last operation is the DB operation.
    }

    Why I would put @Tran on the repository not on the service in this case? Because if I put it on the service method, then the transaction will be opened at the beginning of the service method and stays opened for 7 seconds while I only need it for the save which is the last operation.
    The only cons that I can think of is having inconsistency of where @Tran lives. In some cases, it will on the service and other times, it will be on the repositories. What do you think?

    From “Pivotal Certified Professional Spring Developer Exam” book page 230, mentions two cases of having @Tran on the service only OR on both the service and the repository. The book did not take about the case “repository only”. The book analysis:
    “In an application that does not use a service layer, to ensure transactional behavior when interacting with the database, the repository classes/ methods must be annotated with @Transactional. This will tell Spring to create transactional proxies for the repository classes. When a service layer and service classes are added, there are two possibilities: we annotate the new service classes with @Transactional and remove the annotation from the repository, or we annotate the service classes and keep @Transactional on the repository classes as well.”

    https://books.google.com/books?id=9ojJDQAAQBAJ&pg=PA230&lpg=PA230&dq=where+to+put+@Transactional+on+the+service+or+repository&source=bl&ots=EzxH4iUrb_&sig=5rrZuPbqoZW9bN3gxWVhvXFIde4&hl=en&sa=X&ved=0ahUKEwiNtZ7lxbfUAhWMMSYKHUZcC1MQ6AEIYjAH#v=onepage&q&f=false

    What do you think about this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s