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: https://github.com/jOOQ/jOOQ/issues/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.