How to Delay Database Connection Acquisition in Hibernate (Spring Data)
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.
Join the DZone community and get the full member experience.
Join For FreeStarting 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 tofalse
, 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
totrue
. So, go for it in all your applications using resource-local!Be careful not to sethibernate.connection.provider_disables_autocommit
totrue
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
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.
Opinions expressed by DZone contributors are their own.
Comments