![Nested Transactions in jOOQ – Java, SQL and jOOQ. Nested Transactions in jOOQ – Java, SQL and jOOQ.](https://techtoguide.com/wp-content/uploads/2023/10/A-simple-example-configuration-to-generate-package-private-jOOQ-code.png)
[ad_1]
Since jOOQ 3.4, now we have an API that simplifies transactional logic on prime of JDBC in jOOQ, and ranging from jOOQ 3.17 and #13502, an equal API will even be made obtainable on prime of R2DBC, for reactive functions.
As with every thing jOOQ, transactions are applied utilizing express, API based mostly logic. The implicit logic applied in Jakarta EE and Spring works nice for these platforms, which use annotations and facets in all places, however the annotation-based paradigm doesn’t match jOOQ nicely.
This text reveals 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. When you cross a non-transactional JDBC connection to jOOQ, a question like this shall be auto-committing as nicely:
ctx.insertInto(BOOK)
.columns(BOOK.ID, BOOK.TITLE)
.values(1, "Starting jOOQ")
.values(2, "jOOQ Masterclass")
.execute();
Up to now so good, this has been an affordable default in most APIs. However often, 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 possibly can 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 strictly the identical as with Jakarta EE and Spring @Transactional
facets. 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 move
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 spite of everything, any third occasion library could throw and catch inside exceptions with out you noticing, so why ought to Spring discover.
Transaction propagation
Jakarta EE and Spring supply quite a lot of transaction propagation modes (TxType
in Jakarta EE, Propagation
in Spring). The default in each is REQUIRED
. I’ve been attempting to analysis why REQUIRED
is the default, and never NESTED
, which I discover far more logical and proper, as I’ll clarify afterwards. If , please let me know on twitter or within the feedback:
My assumption for these APIs is
NESTED
requiresSAVEPOINT
assist, which isn’t obtainable in all RDBMS that assist transactionsREQUIRED
avoidsSAVEPOINT
overhead, which generally is a drawback if you happen to don’t really must nest transactions (though we’d argue that the API is then wrongly annotated with too many incidental@Transactional
annotations. Identical to you shouldn’t mindlessly runSELECT *
, you shouldn’t annotate every thing with out giving issues sufficient thought.)- It isn’t unlikely that in Spring consumer code, each service technique is simply blindly annotated with
@Transactional
with out giving this subject an excessive amount of thought (identical as error dealing with), after which, making transactionsREQUIRED
as an alternative ofNESTED
would simply be a extra handy default “to make it work.” That might be in favour ofREQUIRED
being extra of an incidental default than a nicely chosen one. - JPA can’t really work nicely with
NESTED
transactions, as a result of the entities change into corrupt (see Vlad’s touch upon this). In my view, that’s only a bug or lacking characteristic, although I can see that implementing the characteristic may be very advanced and maybe not value 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 assist it in any respect).
However that is jOOQ and jOOQ has at all times been taking a step again to consider how issues ought to be, moderately than being impressed with how issues are.
When you concentrate on the next code:
@Transactional
void tx() {
tx1();
attempt {
tx2();
}
catch (Exception e) {
log.data(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()
- Attempt doing another nested transactional work in
tx2()
- If
tx2()
succeeds, effective, transfer on - If
tx2()
fails, simply log the error,ROLLBACK
to earlier thantx2()
, and transfer on
- If
- No matter
tx2()
, proceed working withtx1()
‘s (and probably additionallytx2()
‘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 speculated to be an atomic unit of labor, unbiased of who known as it. It isn’t, by default, so the Exception e
should be propagated. The one factor that may be finished within the catch
block, earlier than mandatorily rethrowing, is clear up some sources 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 are not any extra savepoints. So, the default is:
- The identical as
NESTED
within the pleased path - Bizarre within the not so pleased path
Which is a robust argument in favour of creating NESTED
the default, no less than in jOOQ. Now, the linked twitter dialogue digressed fairly a bit into architectural considerations of why:
NESTED
is a nasty thought or doesn’t work in all places- Pessimistic locking is a nasty thought
- and so on.
I don’t disagree with a lot of these arguments. But, focusing solely on the listed code, and placing myself within the footwear of a library developer, what might 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 choice in both Jakarta EE and Spring, as that may be one other cheap default). The distinction to Spring being, once more, that every thing is finished programmatically and explicitly, moderately than implicitly utilizing facets.
For instance:
ctx.transaction(trx -> {
trx.dsl().transaction(trx1 -> {
// ..
});
attempt {
trx.dsl().transaction(trx2 -> {
// ..
});
}
catch (Exception e) {
log.data(e);
}
continueWorkOnTrx1(trx);
});
If trx2
fails with an exception, solely trx2
is rolled again. Not trx1
. After all, you possibly can nonetheless re-throw the exception to roll again every thing. However the stance right here is that if you happen to, the programmer, inform jOOQ to run a nested transaction, nicely, 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) assist transactions additionally in R2DBC. The semantics is strictly the identical as with JDBC’s blocking APIs, besides that every 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 can too use RxJava, Mutiny, or no matter. The instance works precisely the identical because the JDBC one, initially.
Nesting additionally works the identical method, within the traditional, reactive (i.e. extra laborious) method:
Flux<?> flux = Flux.from(ctx.transactionPublisher(trx -> Flux
.from(trx.dsl().transactionPublisher(trx1 -> { ... }))
.thenMany(Flux
.from(trx.dsl().transactionPublisher(trx2 -> { ... }))
.onErrorContinue((e, t) -> log.data(e)))
.thenMany(continueWorkOnTrx1(trx))
));
The sequencing utilizing thenMany()
is only one instance. You might 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 way much less of a subject than with Jakarta EE or Spring as every thing you do is often express, and as such, you don’t by accident nest transactions, while you do, you do it deliberately. This is the reason jOOQ opted for a special default than Spring, and one which Jakarta EE doesn’t assist in any respect. The Propagation.NESTED
semantics, which is a robust strategy to preserve the laborious savepoint associated logic out of your code.
[ad_2]