Using R2dbc transactional and non-transactional on a database connection pool fails for Oracle

[andma03]

Affects: 6.0.10++

spring boot 3.0.10
oracle-r2dbc 1.1.1
r2dbc-pool 1.0.1.RELEASE

Using R2dbcEntityTemplate to handle CRUD.

Configuring only 1 connection in the pool so the problem occurs directly.
spring.r2dbc.pool.enabled = true
spring.r2dbc.pool.initial-size = 1

Using both @transactional and non transactional transactions on the same pooled oracle connection will result in:
ERROR: "ORA-01453: SET TRANSACTION must be first statement of transaction"

Steps to reproduce:
Step 1: Use a transactional transaction. (set transaction... SQL... commit)
Step 2: Use NON transactional. (SQL...)
Step 3: Use a transactional transaction again. It will fail when trying execute "SET TRANSACTION..."

It looks like auto-commit is causing this. In step 1 auto-commit is true (default for JDBC). When doing a Transaction the auto-commit is set to false so that the transaction logic can be handled manually.
In step 2 you will use the same connection and auto-commit is then false, and no explicit commit is issued.
In step 3 the R2dbcTransactionManager will fail to start a new transaction because of the state of the connection.

spring-r2dbc prior to 6.0.10 works because auto-commit was put back to the original state.
Commit: 9751987 removed this feature.

[jhoeller]

@mp911de it looks like some R2DBC drivers, e.g. H2, reset the auto-commit flag after commit/rollback. However, the Oracle driver seems to turn off auto-commit in beginTransaction but does not turn it on again in commit/rollbackTransaction. What was the original intention there in the R2DBC 1.0 API, should there be reliable auto-commit handling in the drivers themselves?

Otherwise, we'll have to restore some degree of auto-commit handling in R2dbcTransactionManager. The removal in 6.0.10 was primarily about the manual auto-commit switch before beginTransaction which is definitely unnecessary as of R2DBC 1.0. That said, we could still apply setAutoCommit(true) after commit/rollback, addressing this regression.

[mp911de]

Auto-commit mode is specified to be disabled upon beginTransaction. Some drivers restore auto-commit mode themselves, the spec doesn't mandate drivers to restore auto-commit.

I didn't catch that aspect previously in the commit removing auto-commit restoration.