Micronaut TX with JOOQ throws NoTransactionException in method without @Transactional when other methods in class use it

[matt-snider]

Expected Behavior

When using Micronaut's Transaction Management with JOOQ, we should be able to define methods both with and without @Transactional as in this example controller:

@Controller
public class TestController {
    private final DSLContext db;

    public TestController(DSLContext db) {
        this.db = db;
    }

    @Transactional
    @Get("/foo")
    public void foo() {
        db.execute("<some query>");
    }

    // Should still work despite not having @Transactional
    @Get("/bar")
    public void bar() {
        db.execute("<some query>");
    }
}

Actual Behaviour

When we mix methods with and without @Transactional we get the following error when calling the method without it:

io.micronaut.transaction.exceptions.NoTransactionException: No current transaction present. Consider declaring @Transactional on the surrounding method
    at io.micronaut.transaction.jdbc.TransactionalConnectionInterceptor.intercept(TransactionalConnectionInterceptor.java:65)
       // omitted
Caused by: io.micronaut.transaction.jdbc.exceptions.CannotGetJdbcConnectionException: No current JDBC Connection found. Consider wrapping this call in transactional boundaries.
    at io.micronaut.transaction.jdbc.DataSourceUtils.doGetConnection(DataSourceUtils.java:135)
    at io.micronaut.transaction.jdbc.DataSourceUtils.getConnection(DataSourceUtils.java:93)

As commented here by @dstepanov:

[...] the Jooq is injection the data source where getConnection is delegating to TransactionalConnectionInterceptor which is failing on the same problem. SpringTX is actually checking if the transaction is missing and returns a simple connection.

Steps To Reproduce

1. Checkout example application
2. Run gradle test --info
3. Compare with spring-tx branch, where test succeeds

Environment Information

No response

Example Application

https://github.com/matt-snider/micronaut-tx-issues

Version

3

[matt-snider]

@dstepanov Any thoughts on this one? From your comment it sounded like you had some idea of how this could be fixed. I could take a look if you point me in the right direction

[faustin0]

Issue still present with Micronaut data 4.7.0

[donbeave]

@faustin0 you need to use Connectable annotation.

[faustin0]

@faustin0 you need to use Connectable annotation.

thanks @donbeave, but do i need to use it on every method where i'm expected to do some query ? in this case, isn't it a little uncomfortable?

[donbeave]

@faustin0 frankly say I prefer this approach more than invisible magic with opening connection during execution for the query, this make it informative inside the code where you will get a connection from the pool. Probably, you can make a PR to the micronaut-sql project to add an ability to pull connection when you call jOOQ to execute the query.

[donbeave]

@faustin0 please check this interface: https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/ConnectionProvider.html

[donbeave]

@faustin0 looks like, you just need to pass https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DataSourceConnectionProvider.html to the jOOQ configuration with correct datasource.

[faustin0]

@faustin0 looks like, you just need to pass https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DataSourceConnectionProvider.html to the jOOQ configuration with correct datasource.

I'm using the out of the box configuration of micronaout-jooq: https://github.com/micronaut-projects/micronaut-sql/blob/5.7.x/jooq/src/main/java/io/micronaut/configuration/jooq/JooqConfigurationFactory.java#L105 this should be the correct datasource, right? (by the way i "resolved" it using a transactionManager.withTransaction on the upper level of a graphQL server "ExecutionStrategy", before any db call)