Nested Transactions in jOOQ – Java, SQL and jOOQ.

Since jOOQ 3.4, we now have an API that simplifies transactional logic on prime of JDBC in jOOQ, and ranging from jOOQ 3.17 and #13502, an equal API may even be made obtainable on prime of R2DBC, for reactive functions.

As with every little thing jOOQ, transactions are carried out utilizing specific, API based mostly logic. The implicit logic carried out in Jakarta EE and Spring works nice for these platforms, which use annotations and features all over the place, however the annotation-based paradigm doesn’t match jOOQ effectively.

This text exhibits how jOOQ designed the transaction API, and why the Spring Propagation.NESTED semantics is the default in jOOQ.

Following JDBC’s defaults

In JDBC (as a lot as in R2DBC), a standalone assertion is at all times non-transactional, or auto-committing. The identical is true for jOOQ. For those who cross a non-transactional JDBC connection to jOOQ, a question like this shall be auto-committing as effectively:

ctx.insertInto(BOOK)
   .columns(BOOK.ID, BOOK.TITLE)
   .values(1, "Starting jOOQ")
   .values(2, "jOOQ Masterclass")
   .execute();

To this point so good, this has been an inexpensive default in most APIs. However normally, you don’t auto-commit. You write transactional logic.

Transactional lambdas

If you wish to run a number of statements in a single transaction, you’ll be able to write this in jOOQ:

// The transaction() name wraps a transaction
ctx.transaction(trx -> {

    // The entire lambda expression is the transaction's content material
    trx.dsl()
       .insertInto(AUTHOR)
       .columns(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
       .values(1, "Tayo", "Koleoso")
       .values(2, "Anghel", "Leonard")
       .execute();

    trx.dsl()
       .insertInto(BOOK)
       .columns(BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE)
       .values(1, 1, "Starting jOOQ")
       .values(2, 2, "jOOQ Masterclass")
       .execute();

    // If the lambda is accomplished usually, we commit
    // If there's an exception, we rollback
});

The psychological mannequin is precisely the identical as with Jakarta EE and Spring @Transactional features. Regular completion implicitly commits, distinctive completion implicitly rolls again. The entire lambda is an atomic “unit of labor,” which is fairly intuitive.

You personal your management circulate

If there’s any recoverable exception within your code, you might be allowed to deal with that gracefully, and jOOQ’s transaction administration received’t discover. For instance:

ctx.transaction(trx -> {
    attempt {
        trx.dsl()
           .insertInto(AUTHOR)
           .columns(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
           .values(1, "Tayo", "Koleoso")
           .values(2, "Anghel", "Leonard")
           .execute();
    }
    catch (DataAccessException e) {

        // Re-throw all non-constraint violation exceptions
        if (e.sqlStateClass() != C23_INTEGRITY_CONSTRAINT_VIOLATION)
            throw e;

        // Ignore if we have already got the authors
    }

    // If we had a constraint violation above, we are able to proceed our
    // work right here. The transaction is not rolled again
    trx.dsl()
       .insertInto(BOOK)
       .columns(BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE)
       .values(1, 1, "Starting jOOQ")
       .values(2, 2, "jOOQ Masterclass")
       .execute();
});

The identical is true in most different APIs, together with Spring. If Spring is unaware of your exceptions, it is not going to interpret these exceptions for transactional logic, which makes excellent sense. In any case, any third occasion library could throw and catch inner exceptions with out you noticing, so why ought to Spring discover.

Transaction propagation

Jakarta EE and Spring provide quite a lot of transaction propagation modes (TxType in Jakarta EE, Propagation in Spring). The default in each is REQUIRED. I’ve been making an attempt to analysis why REQUIRED is the default, and never NESTED, which I discover rather more logical and proper, as I’ll clarify afterwards. If you recognize, please let me know on twitter or within the feedback:

My assumption for these APIs is

1. NESTED requires SAVEPOINT help, which isn’t obtainable in all RDBMS that help transactions
2. REQUIRED avoids SAVEPOINT overhead, which could be a drawback in the event you don’t really have to nest transactions (though we would argue that the API is then wrongly annotated with too many incidental @Transactional annotations. Similar to you shouldn’t mindlessly run SELECT *, you shouldn’t annotate every little thing with out giving issues sufficient thought.)
3. It isn’t unlikely that in Spring consumer code, each service methodology is simply blindly annotated with @Transactional with out giving this subject an excessive amount of thought (identical as error dealing with), after which, making transactions REQUIRED as a substitute of NESTED would simply be a extra handy default “to make it work.” That may be in favour of REQUIRED being extra of an incidental default than a effectively chosen one.
4. JPA can’t really work effectively with NESTED transactions, as a result of the entities develop into corrupt (see Vlad’s touch upon this). For my part, that’s only a bug or lacking characteristic, although I can see that implementing the characteristic may be very complicated and maybe not price it in JPA.

So, for all of those merely technical causes, it appears to be comprehensible for APIs like Jakarta EE or Spring to not make NESTED the default (Jakarta EE doesn’t even help it in any respect).

However that is jOOQ and jOOQ has at all times been taking a step again to consider how issues must be, fairly than being impressed with how issues are.

When you consider the next code:

@Transactional
void tx() {
    tx1();

    attempt {
        tx2();
    }
    catch (Exception e) {
        log.information(e);
    }

    continueWorkOnTx1();
}

@Transactional
void tx1() { ... }

@Transactional
void tx2() { ... }

The intent of the programmer who wrote that code can solely be one factor:

• Begin a world transaction in tx()
• Do some nested transactional work in tx1()
• Strive doing another nested transactional work in tx2()
  • If tx2() succeeds, effective, transfer on
  • If tx2() fails, simply log the error, ROLLBACK to earlier than tx2(), and transfer on
• Regardless of tx2(), proceed working with tx1()‘s (and probably additionally tx2()‘s) consequence

However this isn’t what REQUIRED, which is the default in Jakarta EE and Spring, will do. It should simply rollback tx2() and tx1(), leaving the outer transaction in a really bizarre state, that means that continueWorkOnTx1() will fail. However ought to it actually fail? tx2() was presupposed to be an atomic unit of labor, impartial of who referred to as it. It isn’t, by default, so the Exception e should be propagated. The one factor that may be achieved within the catch block, earlier than mandatorily rethrowing, is clear up some assets or do some logging. (Good luck ensuring each dev follows these guidelines!)

And, as soon as we mandatorily rethrow, REQUIRED turns into successfully the identical as NESTED, besides there aren’t any extra savepoints. So, the default is:

• The identical as NESTED within the completely happy path
• Bizarre within the not so completely happy path

Which is a robust argument in favour of constructing NESTED the default, at the very least in jOOQ. Now, the linked twitter dialogue digressed fairly a bit into architectural considerations of why:

• NESTED is a foul thought or doesn’t work all over the place
• Pessimistic locking is a foul thought
• and so forth.

I don’t disagree with lots of these arguments. But, focusing solely on the listed code, and placing myself within the sneakers of a library developer, what may the programmer have probably meant by this code? I can’t see something different that Spring’s NESTED transaction semantics. I merely can’t.

jOOQ implements NESTED semantics

For the above causes, jOOQ’s transactions implement solely Spring’s NESTED semantics if savepoints are supported, or fail nesting solely in the event that they’re not supported (weirdly, this isn’t an possibility in both Jakarta EE and Spring, as that may be one other affordable default). The distinction to Spring being, once more, that every little thing is completed programmatically and explicitly, fairly than implicitly utilizing features.

For instance:

ctx.transaction(trx -> {
    trx.dsl().transaction(trx1 -> {
        // ..
    });

    attempt {
        trx.dsl().transaction(trx2 -> {
            // ..
        });
    }
    catch (Exception e) {
        log.information(e);
    }

    continueWorkOnTrx1(trx);
});

If trx2 fails with an exception, solely trx2 is rolled again. Not trx1. In fact, you’ll be able to nonetheless re-throw the exception to roll again every little thing. However the stance right here is that in the event you, the programmer, inform jOOQ to run a nested transaction, effectively, jOOQ will obey, as a result of that’s what you need.

You couldn’t probably need anything, as a result of then, you’d simply not nest the transaction within the first place, no?

R2DBC transactions

As talked about earlier, jOOQ 3.17 will (lastly) help transactions additionally in R2DBC. The semantics is precisely the identical as with JDBC’s blocking APIs, besides that every little thing is now a Writer. So, now you can write:

Flux> flux = Flux.from(ctx.transactionPublisher(trx -> Flux
    .from(trx.dsl()
        .insertInto(AUTHOR)
        .columns(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
        .values(1, "Tayo", "Koleoso")
        .values(2, "Anghel", "Leonard"))
    .thenMany(trx.dsl()
        .insertInto(BOOK)
        .columns(BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE)
        .values(1, 1, "Starting jOOQ")
        .values(2, 2, "jOOQ Masterclass"))
}));

The instance makes use of reactor as a reactive streams API implementation, however you may also use RxJava, Mutiny, or no matter. The instance works precisely the identical because the JDBC one, initially.

Nesting additionally works the identical means, within the normal, reactive (i.e. extra laborious) means:

Flux> flux = Flux.from(ctx.transactionPublisher(trx -> Flux
    .from(trx.dsl().transactionPublisher(trx1 -> { ... }))
    .thenMany(Flux
        .from(trx.dsl().transactionPublisher(trx2 -> { ... }))
        .onErrorContinue((e, t) -> log.information(e)))
    .thenMany(continueWorkOnTrx1(trx))
));

The sequencing utilizing thenMany() is only one instance. It’s possible you’ll discover a want for solely completely different stream constructing primitives, which aren’t strictly associated to transaction administration.

Conclusion

Nesting transactions is often helpful. With jOOQ, transaction propagation is far much less of a subject than with Jakarta EE or Spring as every little thing you do is normally specific, and as such, you don’t by chance nest transactions, while you do, you do it deliberately. For this reason jOOQ opted for a distinct default than Spring, and one which Jakarta EE doesn’t help in any respect. The Propagation.NESTED semantics, which is a strong technique to hold the laborious savepoint associated logic out of your code.