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.

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;