How to Delay Database Connection Acquisition in Hibernate (Spring Data)

Starting with Hibernate 5.2.10, the database connection acquisition can be delayed until it’s really needed. For a hyper-sonic guide about Spring transaction propagation, check out Spring Transaction Propagation in a Nutshell. In the case of resource-local (a single data source), Hibernate will acquire the database connection of a JDBC transaction right after the transaction starts (e.g., in Spring, a method annotated with @Transactional acquires the database connection immediately after it is called).

In resource-local, a database connection is acquired immediately because Hibernate needs to check the JDBC Connection auto-commit status. If this is true then Hibernate will disable it.

Practically, the database connection is useless to the application until the first JDBC statement of the current transaction is triggered; holding the database connection unused for this time induces a performance penalty that can have a big impact if there are many or/and time-consuming tasks before the first JDBC statement.

In order to prevent this performance penalty, you can inform Hibernate that you disabled auto-commit, so no check is needed. To do this, follow these two steps:

• Turn off auto-commit. For example, check your pool connection for a method of type setAutoCommit(boolean commit) and set it to false, e.g., HikariConfiguartion#setAutoCommit(false).
• Set to true the Hibernate-specific property: hibernate.connection.provider_disables_autocommit

By default, Spring Boot relies on HikariCP, and you can turn off auto-commit in application.properties via the spring.datasource.hikari.auto-commit property. So, the following two settings need to be added to application.properties:

xxxxxxxxxx

spring.datasource.hikari.auto-commit=false

spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true

As a rule of thumb, for resource-local JPA transactions, it is always good practice to configure the connection pool (e.g., HikariCP) to disable the auto-commit and set hibernate.connection.provider_disables_autocommit to true. So, go for it in all your applications using resource-local!Be careful not to set hibernate.connection.provider_disables_autocommit to true and then forget to disable the auto-commit mode! Hibernate will not disable it either! This means that every SQL statement will be executed in auto-commit mode, and no unit-of-work transaction will be available.

To see how a connection acquisition is delayed, consider the following method meant to isolate the main time slots when a connection is obtained from the HikariCP connection pool. Consider reading the comments of this method, as they explain what is going on:

x

@Transactional

public void doTimeConsumingTask() throws InterruptedException {        

   System.out.println("Waiting for a time-consuming task that doesn't need a database                                              connection ...");

   // we use a sleep of 40 seconds just to capture HikariCP logging status

   // which take place at every 30 seconds- this will reveal if

   // the connection was opened (acquired from the connection pool) or not

   Thread.sleep(40000);

   System.out.println("Done, now query the database ...");

   System.out.println("The database connection should be acquired now ...");

   Author author = authorRepository.findById(1L).get();

   // at this point, the connection should be open

   Thread.sleep(40000);

   author.setAge(44);

}                                                                                   

Calling this method without delaying the connection acquisition will reveal the output shown in the next figure (the connection is obtained immediately and is kept open until the first SQL is triggered).

Calling the same method with connection acquisition enabled will reveal that the connection is obtained right before the first SQL is triggered. Meanwhile, this connection can be used by another thread, as shown in the next figure:

The source code is available on GitHub.

If you liked this article, then you'll also like my book containing 150+ performance items: Spring Boot Persistence Best Practices.

 This book helps every Spring Boot developer to squeeze the performances of the persistence layer.