Transaction rollback does not work when using jOOQ

[sriram-wolt]

Expected behavior

When an exception is thrown within a transaction, jOOQ should rollback the whole transaction

Actual behavior

When an exception is thrown within a transaction, jOOQ does not rollback the whole transaction. The inserts before the exception are committed to the database.

Issue on jOOQ: jOOQ/jOOQ#16109

Steps to reproduce the problem

DemoRepository

suspend fun insert(id: String) {
    if (id == "b") {
        throw RuntimeException("error while inserting")
    }

    db.insertInto(DEMO_TABLE)
        .set(DEMO_TABLE.ID, id)
        .awaitSingle()
}

DemoService

suspend fun addRecords(): Unit = transactionalOperator.executeAndAwait {
    log.info("Inserting records")
    repo.insert("a")
    repo.insert("b")  // throws error
}

and then

repo.getAll() // returns ["a"] instead of []

Full code: https://github.com/linktosriram/r2dbc-jooq-rollback

Even-though the inserts are done within a transaction, it does not seem to be rolled back when an exception is thrown

Logs:

Executing query: BEGIN READ WRITE
Executing query: insert into demo_table (id) values ($1)
Executing query: ROLLBACK
Executing query: select demo_table.id from demo_table

When swapping jOOQ DSLContext to DatabaseClient the rollback works 🤔

suspend fun insert(id: String) {
    if (id == "b") {
        throw RuntimeException("error while inserting")
    }

    dbClient.sql("insert into demo_table (id) values ('$id')")
        .fetch()
        .awaitSingle()
}

Checking debug logs, found a difference after the insert statement was executed:

Using DatabaseClient

[demo-app] [actor-tcp-nio-1] i.r.p.client.ReactorNettyClient          : [cid: 0x1][pid: 60][id: 0xc218fdac, L:/127.0.0.1:52359 - R:/127.0.0.1:5559] Response: ReadyForQuery{transactionStatus=TRANSACTION}

Using DSLContext

[demo-app] [actor-tcp-nio-2] i.r.p.client.ReactorNettyClient          : [cid: 0x2][pid: 61][id: 0x4a0f6174, L:/127.0.0.1:52325 - R:/127.0.0.1:5559] Response: ReadyForQuery{transactionStatus=IDLE}

The transactionStatus is different when using jOOQ. Not sure if that's the reason

jOOQ Version

3.18.7

Database product and version

PostgreSQL 15.5

Java Version

OpenJDK Runtime Environment Corretto-21.0.1.12.1

OS Version

macOS Ventura 13.0

JDBC driver name and version (include name if unofficial driver)

org.postgresql:r2dbc-postgresql:1.0.3.RELEASE

[mp911de]

This doesn't seem related to Spring Data because of missing context propagation.

[lukaseder]

@mp911de jOOQ 3.20 has introduced a way to inject context (e.g. reactor context) into jOOQ's reactive streams support (R2DBC or JDBC backed):

• Add a new SubscriberProvider SPI that allows for overriding the way jOOQ internals creates reactive streams Subscribers, to allow for making them context aware jOOQ/jOOQ#17920
• https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/SubscriberProvider.html

Do you think it would be possible now, to propagate Spring transaction information to jOOQ? I've tried to reverse engineer what Spring is doing here, and couldn't access the relevant information through any public API:

• Transaction rollback does not seem to work when using r2dbc and spring jOOQ/jOOQ#16109 (comment)

I'll try tackling this again eventually, but if you have pointers, I'd be very glad. Perhaps, this could be solved with yet another SPI that extracts the (transaction aware) R2DBC Connection or ConnectionFactory from a reactor Context?

[mp911de]

Thanks a lot for spending time here. Spring Framework binds transactional resources to the context using a mutable Transaction Holder. This is to enable nested transactionality handling and the actual resource management is initiated with TransactionContextManager. TransactionContext holds resources bound by transaction managers such as R2dbcTransactionManager

That being said, there's a lot of Spring-specific classes involved that hold stacks, maps-of-maps and strangely looking map associations if you look at it during runtime.

If you want to participate in a Spring-managed transaction, that could be by users using TransactionAwareConnectionFactoryProxy. Specifically, ConnectionFactoryUtils is an entry point to extract the currently bound connection.

Let me know whether that helps (or if it adds to confusion) and if I can provide more details.

[lukaseder]

@mp911de Thanks, I found most of those classes as well during my reverse engineering session. I guess I'll just have to try again. I might eventually find the solution. I guess the solution would have to be implemented in Spring Boot's jOOQ starter, which is the only place that knows both jOOQ and Spring Data, and is thus allowed to depend on both. I can try to contribute it, once I'm sure how it should work. (I'm aware of this limitation: spring-projects/spring-boot#44372, though that isn't forever).