Add DSLContext.transactionAsync(TransactionRunnable) and transactionResultAsync(TransactionCallable)

[jCalamari]

Currently, it is only possible to run a synchronous transaction using DSLContext API. How difficult would it to support asynchronous transactions?

Something like:

Promise p = ...;
dslContext.scheduleTransaction(p);

Or:

CompletionStage cs = ...;
dslContext.scheduleTransaction(cs);

[lukaseder]

This is perhaps related to #4371. In previous versions, we had implemented "asynchronous" fetch operations, although the right choice of API is not obvious to accommodate all possible needs.

In the case of transactions, the semantics of "async" becomes even more delicate as we might need to make assumptions about the TransactionProvider implementation...

[jCalamari]

The only things which holds me back from implementing custom async transaction is DefaultTransactionContext being package private. There are few levels of implementations to copy across including Scope, AbstractScope and so on. Making these classes more extension friendly would help me greatly.

[lukaseder]

Thanks for the additional info. We can probably open up DefaultTransactionContext to public (#4436) by jOOQ 3.7. Would be very curious how you'll implement your own, though! Keep us in the loop.

[jCalamari]

Unfortunately DefaultTransactionProvider is not thread safe thus my custom implementation would not support nested transactions. It would follow open view transaction idea.

[lukaseder]

Yes, we're aware of that: #4290. This will be fixed very soon. There's no reason to cache the Connection

[jCalamari]

Yes, we're aware of that: #4290. This will be fixed very soon. There's no reason to cache the Connection

Does it mean nested transactions could potentially use different connections?

[lukaseder]

Not with the current DefaultTransactionProvider, which takes the "current" Connection (i.e. transaction) from the current Configuration / Scope.

[lukaseder]

We'll be looking into this in the context of the upcoming Flow API in JDK 9. #4371 is the relevant issue for this in jOOQ. I think that we should definitely look at async in a bigger context, not just in terms of transactions.

[lukaseder]

I've spent some more time thinking about asynchronicity and possibly even "reactiveness" (flow API). There's no need to wait for Flow and JDK 9 to happen. I agree with you that we can already leverage JDK 8's CompletionStage for this. For instance, there will also be asynchronous queries (#5012) and Oracle AQ dequeue commands (#5021). It certainly makes sense to add support for asynchronous transactions also.

[lukaseder]

A naive implementation in DefaultDSLContext is the following:

    @Override
    public CompletionStage<Void> transactionAsync(TransactionalRunnable transactional) {
        return CompletableFuture.runAsync(() -> transaction(transactional));
    }

    @Override
    public CompletionStage<Void> transactionAsync(Executor executor, TransactionalRunnable transactional) {
        return CompletableFuture.runAsync(() -> transaction(transactional), executor);
    }

    @Override
    public <T> CompletionStage<T> transactionResultAsync(TransactionalCallable<T> transactional) {
        return CompletableFuture.supplyAsync(() -> transactionResult(transactional));
    }

    @Override
    public <T> CompletionStage<T> transactionResultAsync(Executor executor, TransactionalCallable<T> transactional) {
        return CompletableFuture.supplyAsync(() -> transactionResult(transactional), executor);
    }

This would encapsulate the entire transaction (including translating exceptions to rollback) inside the CompletionStage. In case of CompletionStage.exceptionally(), the transaction will already have rolled back.

[lukaseder]

@jCalamari : Would you mind explaining again your rationale from #4435 (comment) ? What needs to be changed in Scope, according to you, in order to support async transactions?