Why you should always use hibernate.connection.provider_disables_autocommit for resource-local JPA transactions

(Last Updated On: January 4, 2018)

Introduction

One of my major goals for Hibernate is to make sure we offer all sorts of performance improvements to reduce transaction response time and increase throughput. In Hibernate 5.2.10, we addressed the HHH-11542 Jira issue which allows you now to delay the database connection acquisition for resource-local transactions as well.

In this article, I’m going to explain how Hibernate acquires connections and why you want it to delay this process as long as possible.

Resource-local vs. JTA

In Hibernate, the database connection acquisition, as well as the connection release, are relative to the type of the currently running transaction:

  • resource-local: For JDBC transactions, that operate with a single DataSource, the Connection is acquired right when the transaction starts and is closed when the transaction ends (either commit or rollback)
  • JTA: For XA transactions, that span over multiple DataSource(s), the Connection is acquired upon executing the first Statement and is released after each Statement execution. The aggressive connection release mechanism can be skipped if the underlying application server allows us to do so.

Delaying the resource-local connectcion acquisition

Our goal is to make the resource-local transaction behave like JTA and delay the connection acquisition until Hibernate needs to execute the first JDBC Statement of the currently running unit-of-work.

The reason why resource-local transaction requires a database connection from the very beginning can be easily visualized in the following diagram:

Hibernate needs to check the underlying JDBC Connection auto-commit status, and disable it if the Connection is set to auto-commit. This way, Hibernate can control the transaction boundaries and make sure that the unit-of-work JDBC Statement(s) are executed in the context of the same database transaction.

Although this behavior is correct since we cannot know if the auto-commit flag was set or not, we could hint Hibernate to skip this check since we already know that all JDBC Connection(s) run in manual commit mode.

For instance, all enterprise applications already use a connection pooling solution which can disable the auto-commit mode when the database connection is firsts established.

HikariConfig hikariConfig = super.hikariConfig( dataSource );
hikariConfig.setAutoCommit( false );

For this reason, in Hibernate 5.2.10, we introduced the hibernate.connection.provider_disables_autocommit configuration property which tells Hibernate that the underlying JDBC Connection(s) already disabled the auto-commit mode.

Benchmark

To measure the performance advantage of delaying database connection acquisition, we are going to use a test case which emulates a resource-intensive XML document parsing which happens within the boundaries of the JPA transaction context.

If we don’t delay the connection acquisition, the XML parsing duration is going to be added to the database connection lease time. However, once we switch to the new connection delay mechanism, the connection lease time is reduced considerably as illustrated by the graph below.

Therefore, if you are using resource-local transactions (which is quite the norm when using Spring framework), you should definitely configure the connection pool (e.g. HikariCP) to disable the auto-commit commit, and provide the connection acquisition delay Hibernate configuration property:

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

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Hibernate is not just an ORM tool, but a full-blown data access framework. Because database connection management is very important for a high-performance enterprise application, Hibernate allows you to minimize the window of time required to execute a database transaction which leads to a higher transaction throughput as well.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

20 thoughts on “Why you should always use hibernate.connection.provider_disables_autocommit for resource-local JPA transactions

  1. I can not reply to the last answer, apparently the depth does not allow.
    True, many queries under one transaction will be faster than with an autocommit. But I was talking about one query. So I thought, can this be improved.

    With read-only this situation: initially, we configure the pool as read-write in hikari and when we declare @Transactional (readOnly = true), hibernate perform additional synchronous JDBC calls SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY at begin and SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE in the end. Therefore, even with hibernate dirty checking read-write transaction is faster than read-only. (if we don’t get too many queries under the same transaction and entities don’t have blob’s).

    For this, i found this solution to seperate two pools: read-only and read-write in this link: http://256stuff.com/gray/docs/misc/performance_optimizing_spring_hibernate_transactions/#customPool
    But I’m not sure that it is reliable. I would like that such a proposed standard toolkit.

    Then I thought that in this way i can make 2 additional pools with autocommit and without(which would track whether there is a transaction or not) , but it seems to me that this is already some kind of perversion.

    1. You can use the AbstractRoutingDataSource in Spring to have multiple DataSource behind a common interface. Usually, this will allows you to route RW transactions to a Master node while RO transactions to slave nodes. You can configure the underlying RO DataSource with read-only true.

      1. Thank you very much for your answers. Tried to do it today. Created an aspect that intercepts the readOnly transaction. But the problem is, after commit transaction in DataSourceUtils.resetConnectionAfterTransaction readOnly is set to false, and after close connection in ProxyConnection.close readOnly is returned to its original state – to true. As I understood, it is necessary to define ProxyConnection wrapped by custom connection with override setReadOnly method

      2. You don’t need to create anything. Spring already provides the logic via TransactionInterceptor.

  2. Hello Vlad, one of these days just was fixed in a head how to improve productivity of connections though at work there is no in this requirement. As always, excellent article.
    I had such a question: if we set autocommit to false, we must wrap each method in @Transactional(read only or not), but then in this case the performance of single queries is lost (for example, simply to get the user by ID and nothing more). Am I right to understand that this is due to the fact that additional statements “begin” and “coomit” are executed? Will we have to compromise in such cases or is there a solution?

    1. Even for single queries you should use transactions and not the autocommit mode. The extra overhead you’re talking about is negligible. Just benchmark it and see if you ever get a performance bottleneck from using transactions.

      1. With hikari config autocommit = true, readOnly = false and 40k iterations to get user by id from service layer i get this results in average (after 10 runs):
        1) without transaction: 22.6 seconds
        2) with transaction: 30.9 seconds
        3) with transaction read-only: 44.6 seconds
        With config autocommit = false, readOnly = false and provider_disables_autocommit = true:
        1) with transaction: 29.5 seconds
        2) with read-only transaction: 43.5 seconds
        The difference is quite palpable. Still there is a problem when at us a pool only read-write, and therefore in read only such wild overhead. But from the standard solutions, I could not find how to create two pools, in order to use read-only in one situation, and in another – read-write

      2. Yes, but in a real-life situation you have a connection pool that can either disable or leave the autocommit flag set to true.

        The problem is that the connections are fetched from the pool for various transactions: one statement or multiple statements.

        In your benchmark, try to execute 5 or 10 statements in autocommit vs 5 or 10 with a single transaction. You’d see that there’s an overhead of committing after every statement.

        As for read-only, you don’t need a separate CP. The Spring @Transactional(readOnly=true) can propagate to the underlying Connection and be reset back to the previous value prior to returning the connection to the pool.

  3. Hi Vlad,
    I want to ask you what is the difference between “hibernate.connection.autocommit” and the new one “hibernate.connection.provider_disables_autocommit”?

Leave a Reply

Your email address will not be published. Required fields are marked *