Nested Transactions in jOOQ – Java, SQL and jOOQ.

Since jOOQ 3.4, we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ 3.17 and #13502, an equivalent API will also be made available on top of R2DBC, for reactive applications.

As with everything jOOQ, transactions are implemented using explicit, API based logic. The implicit logic implemented in Jakarta EE and Spring works great for those platforms, which use annotations and aspects everywhere, but the annotation-based paradigm does not fit jOOQ well.

This article shows 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 much as in R2DBC), a standalone statement is always non-transactional, or auto-committing. The same is true for jOOQ. If you pass a non-transactional JDBC connection to jOOQ, a query like this will be auto-committing as well:

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

So far so good, this has been a reasonable default in most APIs. But usually, you don’t auto-commit. You write transactional logic.

Transactional lambdas

If you want to run multiple statements in a single transaction, you can write this in jOOQ:

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

    // The whole lambda expression is the transaction's content
    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, "Beginning jOOQ")
       .values(2, 2, "jOOQ Masterclass")
       .execute();

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

The mental model is exactly the same as with Jakarta EE and Spring @Transactional aspects. Normal completion implicitly commits, exceptional completion implicitly rolls back. The whole lambda is an atomic “unit of work,” which is pretty intuitive.

You own your control flow

If there’s any recoverable exception inside of your code, you are allowed to handle that gracefully, and jOOQ’s transaction management won’t notice. For example:

ctx.transaction(trx -> {
    try {
        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 already have the authors
    }

    // If we had a constraint violation above, we can continue our
    // work here. The transaction isn't rolled back
    trx.dsl()
       .insertInto(BOOK)
       .columns(BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE)
       .values(1, 1, "Beginning jOOQ")
       .values(2, 2, "jOOQ Masterclass")
       .execute();
});

The same is true in most other APIs, including Spring. If Spring is unaware of your exceptions, it will not interpret those exceptions for transactional logic, which makes perfect sense. After all, any third party library may throw and catch internal exceptions without you noticing, so why should Spring notice.

Transaction propagation

Jakarta EE and Spring offer a variety of transaction propagation modes (TxType in Jakarta EE, Propagation in Spring). The default in both is REQUIRED. I’ve been trying to research why REQUIRED is the default, and not NESTED, which I find much more logical and correct, as I’ll explain afterwards. If you know, please let me know on twitter or in the comments:

My assumption for these APIs is

1. NESTED requires SAVEPOINT support, which isn’t available in all RDBMS that support transactions
2. REQUIRED avoids SAVEPOINT overhead, which can be a problem if you don’t actually need to nest transactions (although we might argue that the API is then wrongly annotated with too many incidental @Transactional annotations. Just like you shouldn’t mindlessly run SELECT *, you shouldn’t annotate everything without giving things enough thought.)
3. It is not unlikely that in Spring user code, every service method is just blindly annotated with @Transactional without giving this topic too much thought (same as error handling), and then, making transactions REQUIRED instead of NESTED would just be a more convenient default “to make it work.” That would be in favour of REQUIRED being more of an incidental default than a well chosen one.
4. JPA can’t actually work well with NESTED transactions, because the entities become corrupt (see Vlad’s comment on this). In my opinion, that’s just a bug or missing feature, though I can see that implementing the feature is very complex and perhaps not worth it in JPA.

So, for all of these merely technical reasons, it seems to be understandable for APIs like Jakarta EE or Spring not to make NESTED the default (Jakarta EE doesn’t even support it at all).

But this is jOOQ and jOOQ has always been taking a step back to think about how things should be, rather than being impressed with how things are.

When you think about the following code:

@Transactional
void tx() {
    tx1();

    try {
        tx2();
    }
    catch (Exception e) {
        log.info(e);
    }

    continueWorkOnTx1();
}

@Transactional
void tx1() { ... }

@Transactional
void tx2() { ... }

The intent of the programmer who wrote that code can only be one thing:

• Start a global transaction in tx()
• Do some nested transactional work in tx1()
• Try doing some other nested transactional work in tx2()
  • If tx2() succeeds, fine, move on
  • If tx2() fails, just log the error, ROLLBACK to before tx2(), and move on
• Irrespective of tx2(), continue working with tx1()‘s (and possibly also tx2()‘s) outcome

But this is not what REQUIRED, which is the default in Jakarta EE and Spring, will do. It will just rollback tx2() and tx1(), leaving the outer transaction in a very weird state, meaning that continueWorkOnTx1() will fail. But should it really fail? tx2() was supposed to be an atomic unit of work, independent of who called it. It isn’t, by default, so the Exception e must be propagated. The only thing that can be done in the catch block, before mandatorily rethrowing, is clean up some resources or do some logging. (Good luck making sure every dev follows these rules!)

And, once we mandatorily rethrow, REQUIRED becomes effectively the same as NESTED, except there are no more savepoints. So, the default is:

• The same as NESTED in the happy path
• Weird in the not so happy path

Which is a strong argument in favour of making NESTED the default, at least in jOOQ. Now, the linked twitter discussion digressed quite a bit into architectural concerns of why:

• NESTED is a bad idea or doesn’t work everywhere
• Pessimistic locking is a bad idea
• etc.

I don’t disagree with many of those arguments. Yet, focusing only on the listed code, and putting myself in the shoes of a library developer, what could the programmer have possibly intended by this code? I can’t see anything other that Spring’s NESTED transaction semantics. I simply can’t.

jOOQ implements NESTED semantics

For the above reasons, jOOQ’s transactions implement only Spring’s NESTED semantics if savepoints are supported, or fail nesting entirely if they’re not supported (weirdly, this isn’t an option in either Jakarta EE and Spring, as that would be another reasonable default). The difference to Spring being, again, that everything is done programmatically and explicitly, rather than implicitly using aspects.

For example:

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

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

    continueWorkOnTrx1(trx);
});

If trx2 fails with an exception, only trx2 is rolled back. Not trx1. Of course, you can still re-throw the exception to roll back everything. But the stance here is that if you, the programmer, tell jOOQ to run a nested transaction, well, jOOQ will obey, because that’s what you want.

You couldn’t possibly want anything else, because then, you’d just not nest the transaction in the first place, no?

R2DBC transactions

As mentioned earlier, jOOQ 3.17 will (finally) support transactions also in R2DBC. The semantics is exactly the same as with JDBC’s blocking APIs, except that everything is now a Publisher. So, you can now 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, "Beginning jOOQ")
        .values(2, 2, "jOOQ Masterclass"))
}));

The example uses reactor as a reactive streams API implementation, but you can also use RxJava, Mutiny, or whatever. The example works exactly the same as the JDBC one, initially.

Nesting also works the same way, in the usual, reactive (i.e. more laborious) way:

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

The sequencing using thenMany() is just one example. You may find a need for entirely different stream building primitives, which aren’t strictly related to transaction management.

Conclusion

Nesting transactions is occasionally useful. With jOOQ, transaction propagation is much less of a topic than with Jakarta EE or Spring as everything you do is usually explicit, and as such, you don’t accidentally nest transactions, when you do, you do it intentionally. This is why jOOQ opted for a different default than Spring, and one that Jakarta EE doesn’t support at all. The Propagation.NESTED semantics, which is a powerful way to keep the laborious savepoint related logic out of your code.