I would like to use one DataSource bean for a mix of these operations: 1. readOnly=true (implies autoCommit=true) 2. readOnly=false with autoCommit=false 3. readOnly=false with autoCommit=true

One option might be @Transactional annotation supporting autoCommit and readOnly tags; it only has readOnly tag today.

Another option might be two out-of-the-box PlatformTransactionManager beans; where @Transactional value tag can be used pick DataSourceTransactionManager (aka AutoCommitOffTransactionManager) versus AutoCommitOnTransactionManager (new).

I am open to other solutions too, these are just suggestions.

In general, autoCommit on or off is a convenience feature. Both of these examples are transactions.

SET AUTOCOMMIT=0;
BEGIN;
UPDATE table SET version=version+1 WHERE version=0;
COMMIT;

SET AUTOCOMMIT=1;
UPDATE table SET version=version+1 WHERE version=0;

Both examples are transactional, because all of the affected rows are updated atomically. If a connection pool parks connections with AUTOCOMMIT=1, then the 2nd example only requires one round-trip to the database.

  1. For high latency connections, using autoCommit=false does 4 expensive round-trips, while using autoCommit=true only does 1 expensive round-trip to execute the one-statement UPDATE. Both are transactional, but one is much faster.
  2. For using @Version in READ COMMITTED mode, the first transaction requires application code to use OptimisticLockingException and Retryable due to competing version column updates. For the second transaction example, the DB would handle optimistic locking much more efficiently, and Retryable may not be needed at all.

I opened a Stack Exchange question, but the answers I got were not helpful.

https://stackoverflow.com/questions/78268584/how-to-mix-autocommit-true-and-autocommit-true-transactional-operations-in

In summary, I would like to see Spring support using a single DataSource bean with a mix of autoCommit=true and autoCommit=false in user applications. Setting autoCommit=false at the DataSource level doesn't work, because I would need to create two DataSource beans, and I would end up with separate connection pools.

Comment From: jhoeller

I actually agree with the answers on StackOverflow here: @Transactional demarcates a transaction boundary, and the only way to enforce a consistently bounded transaction there with JDBC is to use autoCommit=false and an explicit commit step, encompassing any number of SQL statements inbetween. For that purpose, it does not matter that autoCommit=true effectively leads to single-statement transactions on the database side. Our notion of an application-demarcated transaction is a guaranteed boundary with a single commit/rollback step which we need to enforce against the database resource.

FWIW, @Transactional(readOnly=true) does not imply autoCommit=true. Quite on the contrary, the read-only flag is effectively like an isolation level in that it provides an execution hint for a larger transaction. For single-statement operations in auto-commit mode, the read-only flag usually does not have much effect at all. That said, Spring's @Transactional(readOnly=true) can imply a wider effect in that it may also affect connection pool routing and Hibernate caching behavior, so it may be worth setting that hint in any case for a transaction boundary that is known to only perform read-only operations.

Overall, classic @Transactional with its default propagation=REQUIRED is only to be used with explicitly demarcated transactions by design. However, you may declare @Transactional(propagation=SUPPORTS) for a transaction boundary that supports application-level resource reuse without an underlying transaction enforced on the resource itself. Such a SUPPORTS transaction boundary would work well with an auto-commit connection, and it can also participate in a larger transaction when called from a larger @Transactional(propagation=REQUIRED) boundary.

Alternatively, you may also not declare any @Transactional boundary for such single-statement operations at all. Simply let JdbcTemplate and co execute their statements ad-hoc against a DataSource that is set up with autoCommit=true. The same DataSource can then participate in @Transactional-managed transactions still, incurring the auto-commit switch overhead for such explicitly demarcated transactions only.

So if all you want is an ad-hoc auto-commit transaction for certain data access operations, a hypothetical @Transactional(autoCommit=true) would be roughly equivalent to the existing @Transactional(propagation=SUPPORTS). And in many scenarios, it would effectively be equivalent to no @Transactional demarcation at all.