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 ?
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:
If you have read-only transactions that only execute one query, you can enable auto-commit for those.
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.