Spring Boot/HikariCP @Transactional not overwriting isolation level

Question

My application is experiencing lock contentions on one of our heavily-trafficked tables in our SQL Server database. I have been advised by our DBA team to follow other teams' configuration, which have their default transaction isolation level set to READ_UNCOMMITTED. They then, supposedly, set the isolation level back to READ_COMMITTED for their inserts and updates. I've fought against doing this for a while, as it feels like a cop-out, and I've seen warnings all over the place against using READ_UNCOMMITTED. However, my hands are now being tied.

I'm using Spring Boot, with HikariCP and using Spring Data repositories to interact with my SQL Server database. I'm allowing Spring to auto-configure my DataSource from my application.properties, and have very little other configuration.

I have managed to set my default transaction isolation level as follows in my app properties:

spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_UNCOMMITTED

I've been able to verify that this is working by querying the transaction log, taking the SPID from the transaction entry, and running the following query, which now returns "ReadUncommitted":

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

However, in one of my services, I'm attempting to overwrite the isolation level back to READ_COMMITTED, but it is not taking effect.

Given the following:

Selections from application.properties

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_UNCOMMITTED

JpaConfig.java

@Configuration
@EnableJpaRepositories("my.project.repository")
@EntityScan(basePackages = "my.project.model")
@EnableTransactionManagement
public class JpaConfig {
    //DataSource configured by Spring from application.properties
}

MyService.java

@Service
public class MyService {

    @Autowired private MyRepository myRepository;

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void myMethod() {
        //Logic and call to myRepository.save()
    }
}

MyRepository.java

public interface MyRepository extends JpaRepository<MyClass, Long> {

}

What am I missing? I do not have a custom TransactionManager, as I'm allowing @EnableTransactionManagement to configure that for me, as I've found no indication anywhere that I should be providing my own custom implementation so far.

I have verified that the transaction rollback is properly occurring if an exception is thrown, but I can't figure out why the @Transactional annotation isn't overwriting the isolation level like I'd expect.

For what it's worth, the root problem we're trying to solve are the lock contentions on our SQL Server database. From what I understand, in SQL Server, even SELECTs put a lock on a table (or row?). The DBAs' first suggestion was to add the WITH (NOLOCK) hint to my queries. I can't figure out for the life of me how to cleanly do this without scrapping the use of JPA entirely and using native queries. So, their solution was to use READ_UNCOMMITTED by default, setting READ_COMMITTED explicitly on our write transactions.

Abisha

Answer 1

from the source code of hikari  

    final int level = Integer.parseInt(transactionIsolationName);
            switch (level) {
               case Connection.TRANSACTION_READ_UNCOMMITTED:
               case Connection.TRANSACTION_READ_COMMITTED:
               case Connection.TRANSACTION_REPEATABLE_READ:
               case Connection.TRANSACTION_SERIALIZABLE:
               case Connection.TRANSACTION_NONE:
               case SQL_SERVER_SNAPSHOT_ISOLATION_LEVEL: // a specific isolation level for SQL server only
                  return level;
               default:
                  throw new IllegalArgumentException();
             }

As you see above you have to give numeric value of transaction level like 

spring.datasource.hikari.transaction-isolation=1 

All level numeric values listed:

TRANSACTION_NONE             = 0;
TRANSACTION_READ_UNCOMMITTED = 1;
TRANSACTION_READ_COMMITTED   = 2;
TRANSACTION_REPEATABLE_READ  = 4;
TRANSACTION_SERIALIZABLE     = 8;
SQL_SERVER_SNAPSHOT_ISOLATION_LEVEl =4096;

Fendig

Answer 2

transactionIsolation

This property controls the default transaction isolation level of connections returned from the pool. If this property is not specified, the default transaction isolation level defined by the JDBC driver is used. Only use this property if you have specific isolation requirements that are common for all queries. The value of this property is the constant name from the Connection class such as TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, etc. Default: driver default

ref: https://github.com/brettwooldridge/HikariCP

Hindemith

Answer 3

Make sure you set in your application.properties

spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider

Limoges

Answer 4

Thanks for the detail you provided in your question. It really helped in clarifying my scenario and you already provided the answer to use the NOLOCK option.

I was able to figure out how to apply the option using a custom dialect and some query adjustments to force the dialect logic to always be used.

We are querying from a sql server database that is a read-only replica of our production database.

In our case certain tables used for looking up user characteristics are totally deleted and recreated. This ripples into a large amount of locking on the sql server replica during the replication process.

We are seeing outliers with worst case query times into the minutes (should be < 10 millisecond). We think this is most likely locking related.

I was able to get the WITH (NOLOCK) to be emitted properly with the following approach:

1. Create a custom Dialect.

public class ReadOnlySqlServerDialect extends SQLServer2012Dialect {
    @Override
    public String appendLockHint(LockOptions lockOptions, String tableName) {       
        // in our case the entire db is a replica and we never do any writes       
        return tableName + " WITH (NOLOCK)";
    }
}

2. Configure hibernate.dialect to point at ReadOnlySqlServerDialect.class.getName()

3. Force Queries to use LockModeType.PESSIMISTIC_READ as this bypasses a shield within hibernate and assures that the ReadOnlySqlServerDialect.appendLockHint() method is always called.

return entityMgr.createNamedQuery(UserLog.FIND_BY_EMAIL, UserLog.class)
                .setParameter("email", email)
                .setLockMode(LockModeType.PESSIMISTIC_READ)
                .getSingleResult();

4. Resulting in SQL generated like this:

select userlog0_.EMAIL, userlog0_.NAME as email0_18_ from APP.USER_LOG userlog0_ WITH (NOLOCK) 

Helicoid