Nested transaction support via savepoints is broken in Oracle database

[minjun-jin]

Related to pull request:

• Do not swallow exceptions on failing to release a savepoint #32992

org.springframework.jdbc.datasource.JdbcTransactionObjectSupport releaseSavepoint

The function was only handling exceptions as a log, but when it was modified to throw an exception, the nested transaction in the Oracle database was broken.

The Oracle driver does not support explicit releaseSavepoint. It releases automatically.

A modification is needed to revert to the existing logic that does not throw exceptions or to prevent releaseSavepoint calls when using an Oracle database.

[FabianB98]

We've also stumbled upon this change when trying to upgrade from Spring Boot 3.3.5 (Spring 6.1.14) to Spring Boot 3.4.0 (Spring 6.2.0).

To add some more information to this issue: Our setup consists of a Spring Boot application communicating via JOOQ to an Oracle 19c SQL Database. Specifically, there are a few operations where a nested transaction is needed and the code for this looks something like this:

@Transactional
public void doStuff() {
    // Some stuff to be performed in the outer transaction
    
    // An insert that needs to be committed to the database before the outer transaction is committed
    dslContext.transaction((tr) -> DSL.using(tr)
        .insertInto(
            Tables.SOME_TABLE,
            Tables.SOME_TABLE.IDENTIFIER,
            Tables.SOME_TABLE.FOO,
            Tables.SOME_TABLE.BAR)
        .values(
            id,
            foo,
            bar)
        .execute());

    // Some more stuff to be performed in the outer transaction
}

In Spring 6.1.14 this has worked as expected, however while trying to upgrade to Spring 6.2.0 we noticed that there were a few tests which started to fail due to an exception being thrown when the nested transaction is being committed:

org.springframework.transaction.TransactionSystemException: Could not explicitly release JDBC savepoint
        at org.springframework.jdbc.datasource.JdbcTransactionObjectSupport.releaseSavepoint(JdbcTransactionObjectSupport.java:183)
        at org.springframework.transaction.support.AbstractTransactionStatus.releaseHeldSavepoint(AbstractTransactionStatus.java:177)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:786)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:758)
        at org.springframework.data.transaction.MultiTransactionStatus.commit(MultiTransactionStatus.java:74)
        at org.springframework.data.transaction.ChainedTransactionManager.commit(ChainedTransactionManager.java:161)
        at org.springframework.boot.autoconfigure.jooq.SpringTransactionProvider.commit(SpringTransactionProvider.java:54)
        at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$3(DefaultDSLContext.java:534)
        at org.jooq.impl.Tools$3$1.block(Tools.java:6370)
        at java.base/java.util.concurrent.ForkJoinPool.unmanagedBlock(ForkJoinPool.java:3780)
        at java.base/java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3725)
        at org.jooq.impl.Tools$3.get(Tools.java:6367)
        at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:578)
        at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:502)
        at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:591)
        at [our class].doStuff([line number pinpointing to the call of dslContext.transaction])
Caused by: java.sql.SQLFeatureNotSupportedException: Nicht unterstützte Funktion: releaseSavepoint
        at oracle.jdbc.driver.PhysicalConnection.releaseSavepoint(PhysicalConnection.java:4283)
        at com.zaxxer.hikari.pool.HikariProxyConnection.releaseSavepoint(HikariProxyConnection.java)
        at org.springframework.jdbc.datasource.JdbcTransactionObjectSupport.releaseSavepoint(JdbcTransactionObjectSupport.java:180)
        ... 106 common frames omitted

As we investigated the cause of this failing test, we've found out that there was in fact a debug log with the same message being logged in Spring 6.1.14, whereas now with Spring 6.2.0 this exception is being thrown instead of being swallowed. We've pinpointed the origin of that change down to the same PR (#32992) as @minjun-jin did.

The reason for this PR seems reasonable in a way that clients of spring-jdbc should be informed about such database errors. However, this exception stems from a level so deep that there is no way how we could handle this in our application code while also ensuring that the nested transaction doesn't rollback. We believe that this exception should be handled from within spring-tx (presumably from within AbstractPlatformTransactionManager::processCommit) as any Spring application using a transaction from within spring-tx is only a transitive client of spring-jdbc. Furthermore, the logic being performed within spring-tx seems like a place where this exception could actually be handled in a useful way without causing the whole transaction to rollback.

Another point to mention here is the fact that the implementation of AbstractPlatformTransactionManager::processCommit tries to release the savepoint as soon as it detects that there is a savepoint without further checking whether the underlying database actually supports releasing a savepoint. Instead of trying to handle or swallow this exception it could also be reasonable to only call status.releaseHeldSavepoint() if this feature is supported by the database. While Oracle databases support the creation of savepoints, they don't support the explicit premature release of savepoints (this may or may not apply to other databases as well).