title: Spring Boot/HikariCP @Transactional not overwriting isolation level
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>I have managed to set my default transaction isolation level as follows in my app properties:</p>
  
  <pre><code>spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_UNCOMMITTED
  </code></pre>
  
  <p>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":</p>
  
  <pre><code>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
  </code></pre>
  
  <p>However, in one of my services, I'm attempting to overwrite the isolation level back to READ_COMMITTED, but it is not taking effect.</p>
  
  <p>Given the following:</p>
  
  <p><strong>Selections from application.properties</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>JpaConfig.java</strong></p>
  
  <pre><code>@Configuration
  @EnableJpaRepositories("my.project.repository")
  @EntityScan(basePackages = "my.project.model")
  @EnableTransactionManagement
  public class JpaConfig {
      //DataSource configured by Spring from application.properties
  }
  </code></pre>
  
  <p><strong>MyService.java</strong></p>
  
  <pre><code>@Service
  public class MyService {
  
      @Autowired private MyRepository myRepository;
  
      @Transactional(isolation = Isolation.READ_COMMITTED)
      public void myMethod() {
          //Logic and call to myRepository.save()
      }
  }
  </code></pre>
  
  <p><strong>MyRepository.java</strong></p>
  
  <pre><code>public interface MyRepository extends JpaRepository&lt;MyClass, Long&gt; {
  
  }
  </code></pre>
  
  <p>What am I missing? I do not have a custom TransactionManager, as I'm allowing <code>@EnableTransactionManagement</code> to configure that for me, as I've found no indication anywhere that I should be providing my own custom implementation so far.</p>
  
  <p>I have verified that the transaction rollback is properly occurring if an exception is thrown, but I can't figure out why the <code>@Transactional</code> annotation isn't overwriting the isolation level like I'd expect.</p>
  
  <p>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.</p>