Jooq DSL.using seems to ignore TransactionAwareConnectionFactoryProxy

[fcabouat]

Hi,

(coming from spring-projects/spring-boot#24049)

Expected behavior

DSL.using(
    DefaultConfiguration()
        .set(TransactionAwareConnectionFactoryProxy(connectionFactory))
        .set(SQLDialect.POSTGRES)
)

=> Should happen in transactions like when we pass a connection to Jooq directly from this factory

Actual behavior

Absence of transaction awareness

Steps to reproduce the problem

class CommandLineSpringApp(
    @Autowired private val transactionalOperator: TransactionalOperator,
    @Autowired private val connectionFactory: ConnectionFactory
) : CommandLineRunner {

    override fun run(vararg args: String?) {
        runBlocking {
            getTxIdFromDBTransactional()
            getTxIdFromDBTransactional()
        }
    }

    private suspend fun getTxIdFromDBTransactional() =
        transactionalOperator.executeAndAwait {
            println("With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: " + getTxIdJooqDirectConnection())
            println("With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: " + getTxIdJooqDirectConnection())
            println("With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: " + getTxIdJooqDirectConnection())
            println("It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: " + getTxIdJooqConnectionFactory())
            println("It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: " + getTxIdJooqConnectionFactory())
            println("It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: " + getTxIdJooqConnectionFactory())
        }

    private suspend fun getTxIdJooqDirectConnection(): Int {
        val reactiveConn = TransactionAwareConnectionFactoryProxy(connectionFactory).create().awaitSingle()

        return DSL.using(
            reactiveConn
        ).dsl()
            .select(DSL.function("txid_current", SQLDataType.INTEGER))
            .fetchOneAwait()
            .value1()
    }

    private suspend fun getTxIdJooqConnectionFactory(): Int =
        DSL.using(
            DefaultConfiguration()
                .set(TransactionAwareConnectionFactoryProxy(connectionFactory))
                .set(SQLDialect.POSTGRES)
        ).dsl()
            .select(DSL.function("txid_current", SQLDataType.INTEGER))
            .fetchOneAwait()
            .value1()

    suspend fun <T : Record> ResultQuery<T>.fetchOneAwait(): T =
        Mono.from(this).awaitSingle()

Output :

With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1560
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1560
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1560
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1561
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1562
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1563
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1564
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1564
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1564
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1565
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1566
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1567

Versions

• jOOQ: 3.16.5
• Java: 11 Temurin
• Database (include vendor): PostgreSQL 13-alpine
• OS: Windows
• JDBC Driver (include name if inofficial driver): PostgreSQL / R2DBC

Regards,

[lukaseder]

Thanks for your report. Are you sure you're supposed to create the proxy yourself? It seems like something you should rather have Spring inject into your application, instead.

[lukaseder]

Another curious thing that is clearly different between your two approaches: You're never closing your explicitly created connection...

[fcabouat]

Thanks for your report. Are you sure you're supposed to create the proxy yourself? It seems like something you should rather have Spring inject into your application, instead.

I created it explicitly here to try to isolate a minimal case, but yes, the idea would be to either declare the bean elsewhere or let Spring create it. I was facing the same issue when the bean was injected from elsewhere though?

You're right I should close the connection in the case where I create it explicitly, thanks for catching it !

[lukaseder]

I created it explicitly here to try to isolate a minimal case, but yes, the idea would be to either declare the bean elsewhere or let Spring create it. I was facing the same issue when the bean was injected from elsewhere though?

Makes sense, just trying to think what causes the difference and if it's really jOOQ related.

You're right I should close the connection in the case where I create it explicitly, thanks for catching it !

So, what happens then? Will your calls still share the same transaction?

[fcabouat]

I added a close() call on the connection, but the output stays similar :

fun main(args: Array<String>) {
    runApplication<SpringBootApp>(*args) {
    }
}

@SpringBootApplication
class SpringBootApp

@Configuration
class CommandLineRunner(
    @Autowired private val transactionalOperator: TransactionalOperator,
    @Autowired private val connectionFactory: ConnectionFactory
) : CommandLineRunner {

    override fun run(vararg args: String?) {
        runBlocking {
            getTxIdFromDBTransactional()
            getTxIdFromDBTransactional()
        }
    }

    private suspend fun getTxIdFromDBTransactional() =
        transactionalOperator.executeAndAwait {
            println("With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: " + getTxIdJooqDirectConnection())
            println("With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: " + getTxIdJooqDirectConnection())
            println("With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: " + getTxIdJooqDirectConnection())
            println("It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: " + getTxIdJooqConnectionFactory())
            println("It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: " + getTxIdJooqConnectionFactory())
            println("It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: " + getTxIdJooqConnectionFactory())
        }

    private suspend fun getTxIdJooqDirectConnection(): Int {
        val reactiveConn = TransactionAwareConnectionFactoryProxy(connectionFactory).create().awaitSingle()

        val res = DSL.using(
            reactiveConn
        ).dsl()
            .select(DSL.function("txid_current", SQLDataType.INTEGER))
            .fetchOneAwait()
            .value1()

        reactiveConn.close()

        return res
    }

    private suspend fun getTxIdJooqConnectionFactory(): Int =
        DSL.using(
            DefaultConfiguration()
                .set(TransactionAwareConnectionFactoryProxy(connectionFactory))
                .set(SQLDialect.POSTGRES)
        ).dsl()
            .select(DSL.function("txid_current", SQLDataType.INTEGER))
            .fetchOneAwait()
            .value1()

    suspend fun <T : Record> ResultQuery<T>.fetchOneAwait(): T =
        Mono.from(this).awaitSingle()
}

Output :

With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1298
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1298
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1298
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1299
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1300
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1301
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1302
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1302
With direct connection from TransactionAwareConnectionFactoryProxy = all calls happen in same tx: 1302
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1303
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1304
It doesn't seem to be when we use TransactionAwareConnectionFactoryProxy: 1305

[lukaseder]

Connection::close returns a Publisher, so you're not closing things yet

[fcabouat]

I added an awaitSingle() on the Publisher of close and apparently the connection is already closed at that point :

java.lang.IllegalStateException: Failed to execute CommandLineRunner
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:780) ~[spring-boot-2.6.4.jar:2.6.4]
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:761) ~[spring-boot-2.6.4.jar:2.6.4]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:310) ~[spring-boot-2.6.4.jar:2.6.4]
	at fr.gouv.finances.douane.lcf.smartbackend.mainwebapp.adapters.jooq.CommandLineSpringAppKt.main(CommandLineSpringApp.kt:83) ~[classes/:na]
Caused by: java.lang.IllegalArgumentException: Active Connection is required
	at org.springframework.util.Assert.notNull(Assert.java:201) ~[spring-core-5.3.16.jar:5.3.16]
	at org.springframework.r2dbc.connection.ConnectionHolder.getConnection(ConnectionHolder.java:112) ~[spring-r2dbc-5.3.16.jar:5.3.16]
	at org.springframework.r2dbc.connection.R2dbcTransactionManager.lambda$doCleanupAfterCompletion$13(R2dbcTransactionManager.java:322) ~[spring-r2dbc-5.3.16.jar:5.3.16]
	at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:44) ~[reactor-core-3.4.15.jar:3.4.15]
	at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52) ~[reactor-core-3.4.15.jar:3.4.15]

[lukaseder]

Wow, well who is closing it, then? jOOQ shouldn't be closing it... I can have a closer look, if you could share the complete reproducer project? We have a template for that: https://github.com/jOOQ/jOOQ-mcve, but any format is fine...

[vkremianskii]

Hey, guys.

After some debugging, I came to the conclusion that this is a problem in TransactionAwareConnectionFactoryProxy. See spring-projects/spring-framework#28133 for details.

This is my workaround, if that would be of help to anyone:

https://github.com/vkremianskii/pits/blob/jooq-r2dbc-tx/core/core-data/src/main/java/com/github/vkremianskii/pits/core/data/TransactionalJooq.java

[lukaseder]

I'm closing this issue since it seems to be about a specific Spring configuration and setup, not about a bug in jOOQ.