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 # 13502an equivalent API will also be made available on top of R2DBCfor 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 do not auto-commit. You write transactional logic.
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 your code, you are allowed to handle that gracefully, and jOOQ’s transaction management will not 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.
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
SAVEPOINTsupport, which is not available in all RDBMS that support transactions
SAVEPOINToverheadwhich can be a problem if you do not actually need to nest transactions (although we might argue that the API is then wrongly annotated with too many incidental
@Transactionalannotations. Just like you should not mindlessly run
SELECT *you should not annotate everything without giving things enough thought.)
- It is not unlikely that in Spring user code, every service method is just blindly annotated with
@Transactionalwithout giving this topic too much thought (same as error handling), and then, making transactions
NESTEDwould just be a more convenient default “to make it work.” That would be in favor of
REQUIREDbeing more of an incidental default than a well chosen one.
- JPA can not actually work well with
NESTEDtransactions, 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 does not even support it at all).
But this is jOOQ and jOOQ has always been taking a step back to think about how things should berather 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
- Do some nested transactional work in
- Try doing some other nested transactional work in
tx2()succeeds, fine, move on
tx2()fails, just log the error,
tx2()and move on
- Irrespective of
tx2()continue working with
tx1()‘s (and possibly also
But this is not what
REQUIRED, which is the default in Jakarta EE and Spring, will do. It will just rollback
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 is not, 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
NESTEDin the happy path
- Weird in the not so happy path
Which is a strong argument in favor of making
NESTED the default, at least in jOOQ. Now, the linked twitter discussion digressed quite a bit into architectural concerns of why:
NESTEDis a bad idea or does not work everywhere
- Pessimistic locking is a bad idea
I do not 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 not see anything else that Spring’s
NESTED semantics transaction. I simply can not.
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.
ctx.transaction(trx -> trx.dsl().transaction(trx1 -> // .. ); try trx.dsl().transaction(trx2 -> // .. ); catch (Exception e) log.info(e); continueWorkOnTrx1(trx); );
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 could not possibly want anything else, because then, you’d just not nest the transaction in the first place, no?
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 (ie 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.
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 do not 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 does not support at all. The
Propagation.NESTED semantics, which is a powerful way to keep the laborious savepoint related logic out of your code.