Nested Transactions in 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 … Continue reading Nested Transactions in jOOQ →

Nested Transactions in jOOQ

Posted on April 28, 2022April 29, 2022 by lukaseder

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:

Why is Propagation.REQUIRED the default in Spring? NESTED seems to be a much better default.

A NESTED transactional unit is truly transactional.
A REQUIRED transactional unit can leave data in a weird state, depending on whether it is called top level or from a nested scope.

— Lukas Eder (@lukaseder) April 28, 2022

My assumption for these APIs is

• NESTED requires SAVEPOINT support, which isn’t available in all RDBMS that support transactions
• 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.)
• 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.
• 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

[...]

Original source