Consider disabling auto-commit when wiring connection pool configs and setting hibernate.connection.provider_disables_autocommit for resource-local transactions

[vladmihalcea]

As I explained in this article, there is a new optimization option we introduced in Hibernate 5.2.10 which makes a lot of sense for most Spring applications:

<property name="hibernate.connection.provider_disables_autocommit" valaue="true"/>

With this property in place, Hibernate can delay the database connection acquisition until there is a JDBC Statement to execute. This is very convenient, as otherwise, the database connection is acquired when entering the @Transactional service method.

Now, to benefit from this optimization, you'd have to do 2 things:

1. To make sure you disable auto-commit when wiring up the connection pool Spring bean. For instance, HickariCP allows you to set it via HikariConfiguartion#setAutoCommit(false).
2. To set up the hibernate.connection.provider_disables_autocommit when wiring a JpaTransactionManager or HibernateTranscationManager.

Enabling auto-commit by default is unlikely to break anything since, even if you don't do it, Hibernate does that anyway when starting a JPA Transaction.

[philwebb]

Thanks @vladmihalcea, I'll flag this one for discussion and we'll talk about it on our next team call.

[philwebb]

The setAutoCommit change will need careful consideration since it will affect all sorts of applications (not just Hibernate users).

[vladmihalcea]

Sure. Maybe it's better to inspect the DataSource connection pool configuration and check if the user has disabled auto-commit explicitly. In this case, it's safe to apply this optimization since the user already opted for auto-commit=false.

[philwebb]

Changing setAutoCommit(false) by default is a bit of a risky change. Perhaps we can detect auto-commit and use this to set the hibernate.connection.provider_disables_autocommit flag.

@vladmihalcea What happens if you set hibernate.connection.provider_disables_autocommit but your pool doesn't have setAutoCommit(true)?

[vladmihalcea]

What happens if you set hibernate.connection.provider_disables_autocommit but your pool doesn't have setAutoCommit(true)?

The problem is when the hibernate.connection.provider_disables_autocommit and the pool does not disable the auto-commit, meaning it doe snot call setAutoCommit(false). In this case, Hibernate will not disable it either and every statement will be executed in auto-commit, hence you get no transaction unit-of-work.

However, since Spring Boot supports 3 connection pools: Hikari, DBCP2 and TomcatCP, it's possible to add to the DataSourcePoolMetadata interface a new method isAutoCommit(), and implement it for every supported CP. This way, Spring Boot can figure out if the CP has disabled auto-commit and configure the aforementioned hibernate property too.