-
Couldn't load subscription status.
- Fork 131
Description
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