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

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.

Comment From: 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.

Comment From: 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.