Why is "hibernate.connection.autocommit = true" not recommended in Hibernate?

Question

In Hibernate API, there is a property hibernate.connection.autocommit which can be set to true.

But in the API, they have mentioned that it is not recommended to set it like so:

Enables autocommit for JDBC pooled connections (it is not recommended).

Why is it not recommended ? What are the ill-effects of setting this property to true ?

Answer 1

By default the autocommit value is false, therefore the transaction needs to be commited explicitly. This might be the reason why the changes not getting reflected in database, else can try flush to force the changes before commit.

When you close the session, then it will get commited in database implicitly [depends on the implementation].

When you have cascading transactions & needs to rollback for atomicity, you need to have control over transactions & in that case, autocommit should be false.

Either set autocommit as true or handle transactions explicitly.

Here is a good explanation on it.

Hibernate forum related to this.

Stackoverflow question on it.

Answer 2

All database statements are executed within the context of a physical transaction, even when we don’t explicitly declare transaction boundaries (BEGIN/COMMIT/ROLLBACK).

If you don't declare the transaction boundaries, then each statement will have to be executed in a separate transaction. This may even lead to opening and closing one connection per statement.

Declaring a service as @Transactional will give you one connection for the whole transaction duration, and all statements will use that single isolation connection. This is way better than not using explicit transactions in the first place. On large applications you may have many concurrent requests and reducing the database connection acquiring request rate is definitely improving your overall application performance.

So the rule of thumb is:

1. If you have read-only transactions that only execute one query, you can enable auto-commit for those.

2. If you have transactions containing more than one statement, you need to disable auto-commit, since you want all operations to execute in a single unit-of-work and you don't want to put extra pressure on your connection pool.