Micronaut, db-scheduler: No current transaction present. Consider declaring @Transactional on the surrounding method

Question

I'm trying to use db-scheduler with Micronaut. Therefore, I created a @Singleton service where I inject the actual DataSource which is of type TransactionAwareDataSource. I then call a certain method to setup the scheduler which is something like:

  @Transactional
  public void createJob() {

    RecurringTask<Void> hourlyTask = Tasks.recurring("my-hourly-task", FixedDelay.ofHours(1))
        .execute((inst, ctx) -> {
          System.out.println("Executed!");
        });

    final Scheduler scheduler = Scheduler
        .create(dataSource)
        .startTasks(hourlyTask)
        .threads(5)
        .build();

    scheduler.start();
  }

which, at "create" throws this exception:

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)
    at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:96)
    at io.micronaut.transaction.jdbc.TransactionalConnection$Intercepted.getMetaData(Unknown Source)
    at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.<init>(AutodetectJdbcCustomization.java:40)
    at com.github.kagkarlsson.scheduler.SchedulerBuilder.lambda$build$0(SchedulerBuilder.java:190)
    at java.base/java.util.Optional.orElseGet(Optional.java:369)

Everywhere else in my app everything is working like it should, means, I can read and write to the DB via the repositories and @Transactional is working as well.

I'm not 100% sure where the problem is, but I guess it does have to do with placing the annotation. Which - in this case - is nothing I can really change. On the other hand, if I create the datasource manually, effectively bypassing micronaut, it's working.

BTW: the exception comes up within db-scheduler where the first call to the DB is made (c.getMetaData().getDatabaseProductName()).

Micronaut-Version: 2.3.4, Micronaut-Data: 2.2.4, everything setup properly.

Do you guys have any ideas how to solve this problem? Or is it even a bug?

Thanks!

Answer 1

So the problem is that Micronaut Data wraps the DataSource into a TransactionAwareDataSource, as you mentioned. Your library db-scheduler or mine JobRunr picks it up, and operates without the required annotations. The solution is to unwrap it before giving it to the db-scheduler or JobRunr:

Kotlin:

val unwrappedDataSource = (dataSource as DelegatingDataSource).targetDataSource

Java:

DataSource unwrappedDataSource = ((DelegatingDataSource) dataSource).targetDataSource

Answer 2

This worked better for me than the existing answer. See below for explanation. The example is in Kotlin but it naturally translates to Java.

Note: The example is for Micronaut v3.

@Factory
internal class DbSchedulerFactory(
    private val dataSource: DataSource,
) {

    private val log = LoggerFactory.getLogger(this::class.java)

    @Singleton
    fun scheduler(): Scheduler {
        return Scheduler.create(configureDataSource(dataSource), tasks)
            .threads(10)
            // ...
            .build()
    }

    private fun configureDataSource(dataSource: DataSource): DataSource {
        // Is transaction-aware DataSource proxy active? (Note: class is private so we match by name)
        return if (dataSource::class.qualifiedName == "io.micronaut.transaction.jdbc.TransactionAwareDataSource.DataSourceProxy") {
            log.info("Adding support for out-of-transaction connection fetching since the data source is a TransactionAwareDataSource")
            object : DelegatingDataSource(dataSource) {
                override fun getConnection() =
                    if (TransactionSynchronizationManager.isSynchronizationActive()) dataSource.connection
                    else DelegatingDataSource.unwrapDataSource(dataSource).connection
            }
        } else {
            dataSource
        }
    }
}

Explanation

After (too) many hours of debugging and searching, I found this issue basically stating that: if a) you use Micronaut's default transaction handling and b) you have a code that will fetch and interact with a Connection from a transaction-aware DataSource, then you will get the NoTransactionException exception unless the interaction with the connection happens within transaction scope (either with @Transactional or programmatic transactions).

X.Y.'s solution gets rid of the exception by getting connections straight from the underlying data source (bypassing transaction-aware DataSource proxy), which typically will be a connection pool like Hikari. However, in doing so, it doesn't support db-scheduler participating in Micronaut's transaction scope.

This was a deal breaker for me, since I wanted scheduler.schedule, scheduler.reschedule, scheduler.cancel (and all the other advanced features like custom tasks with CompletionHandlers) participate in @Transaction scope. At the same time I didn't want plain db-scheduler code to be concerned with transaction scopes.

Slapping @Transactional or programmatic transactions everywhere isn't a viable solution as you don't always control the code interacting with Connections (and this is what db-scheduler does when: building the Scheduler, starting it, and during it's operation).

Possibly, I could switch to Spring-based transaction handling (described in the Tip at the end of this section) but this would pull a lot of other stuff I didn't want.

Instead, I wrote the above: a wrapper around the transaction-aware DataSource proxy (yes, wrapper for a wrapper) that:

1. Returns the connection bound to current transaction scope, if there is one.
2. Returns a connection from the underlying data source, if there isn't a transaction scope.