5

Spring Data JPA or Hibernate have by default auto-commit set to false. This sounds reasonable as these frameworks work with entities and an update to an entity could involve updates to multiple tables through multiple SQL queries. So by setting auto-commit to false and explicitly controlling a transaction these frameworks ensure that a change to entity is atomic and consistent.

But now Hikari is the default connection pool provider for spring data jpa, and on looking at application logs I see hikari sets auto-commit to true for the connection pool.

2021-10-24 11:30:07.815 DEBUG   [restartedMain] com.zaxxer.hikari.HikariConfig||HikariConfig.logConfiguration:1135: autoCommit................................true

Any explanation of why this is set so and does this effect transactions(I don't think it effects transactions as each transaction might again set auto-commit to false and thus take over when to commit the transaction.)

Edit - following @ken-chan answer and discussion.
For projects using spring data jpa with @Transactional (with 100% Hiberante), changing hikaris connection pool settings to auto-commit=false should give performance benefit. Please see answer and subsequent discussion for more details.

samshers
  • 1
  • 6
  • 37
  • 84
  • please read ken-chan's answer and discussion. It goes into good detail. If you think the Q &A has resulted into useful learning, please upvote Q&A. – samshers Oct 24 '21 at 14:23

1 Answers1

6

I think Hikari just follows the default auto-commit value (i.e. true) defined by JDBC in order to align with its default behaviour .(See this)

And your guess is correct , the framework will take care to configure the necessary auto-commit value of the JDBC Connection such that it can do the transactional stuff over multiple JDBC Statement.

For example in Spring @Transactionl using JDBC , the following codes show that if the auto-commit is enabled , it will disable it before executing any transactional codes. It also will re-enable it after completing the transaction.

// Switch to manual commit if necessary. This is very expensive in some JDBC drivers,
// so we don't want to do it unnecessarily (for example if we've explicitly
// configured the connection pool to set it already).
if (con.getAutoCommit()) {
    txObject.setMustRestoreAutoCommit(true);
    if (logger.isDebugEnabled()) {
        logger.debug("Switching JDBC Connection [" + con + "] to manual commit");
    }
    con.setAutoCommit(false);
}
Ken Chan
  • 84,777
  • 26
  • 143
  • 172
  • you can verfiy my finding on hikar `auto-commit=true` by following the logs of this project - https://github.com/ramshers/JPAInPractice/tree/master – samshers Oct 24 '21 at 11:21
  • 1
    thanks for clarifying the second point with spring code reference. +1 – samshers Oct 24 '21 at 11:22
  • the default auto commit value of Hikari is already mentioned in their website at here https://github.com/brettwooldridge/HikariCP#frequently-used – Ken Chan Oct 24 '21 at 11:40
  • right. so this is what it summarizes up to - `the framework will take care to configure the necessary auto-commit` and the default 'true' set by hikari is overridden every time. Thanks. But from the java docs this flip is going to cost time. So, will it be wise for me to set the default to 'false' to save some time...when using hibernate (spring data jpa). – samshers Oct 24 '21 at 12:22
  • or based up on the references you gave. I should now rephrase my Q as - For Spring Data JPA (Hibernate) project does spring boot 2 default connection pool provider i.e Hikari's default `auto-commit=true` makes sense. Will it be safe if it change this to `false` for the little performance benefit or will I run in to other troubles. – samshers Oct 24 '21 at 12:30
  • yes. setting to `commit=false` should have performance benefit when work with spring 's @Transactional. As long as your connection pool is only used by hibernate , it is 100% safe. But if you have some codes that manually get the connection from the pool to work with , you have to review such portion of code.. – Ken Chan Oct 24 '21 at 14:09
  • 1
    `As long as your connection pool is only used by hibernate , it is 100% safe.` - accepted. – samshers Oct 24 '21 at 14:15
  • i am from Hong Kong, and I am not the native english speaker :D . Is it strange to say cheers in such context ? – Ken Chan Oct 24 '21 at 14:29
  • would you consider - editing your statement - `I think Hikari just follows the default auto-commit value (i.e. false)` to `I think Hikari just follows the default auto-commit value (i.e. true) `. Ref - [When a connection is created, it is in auto-commit mode.](https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html) and [By default, JDBC uses an operation mode called auto-commit.](https://www.ibm.com/docs/en/i/7.1?topic=transactions-jdbc-auto-commit-mode) – samshers Oct 24 '21 at 15:34