Transaction questions

42 views
Skip to first unread message

robert....@cloudability.com

unread,
May 19, 2017, 12:08:40 PM5/19/17
to jOOQ User Group
Sorry if this has been asked before, but I have two basic questions.

1. Why do the functions for the transaction methods take a config instead of a DSLContext? Couldn't you have just gotten the DSLContext internally with DSL.using(config) so that the transaction function to execute just had the DSLContext?

2. Is there a way to use these methods so that instead of creating nested transaction when one is called from another that they reference count?

Thanks!

Lukas Eder

unread,
May 19, 2017, 4:22:48 PM5/19/17
to jooq...@googlegroups.com
2017-05-19 18:08 GMT+02:00 <robert....@cloudability.com>:
Sorry if this has been asked before, but I have two basic questions.

1. Why do the functions for the transaction methods take a config instead of a DSLContext? Couldn't you have just gotten the DSLContext internally with DSL.using(config) so that the transaction function to execute just had the DSLContext?

While the DSLContext type is quite useful to your SQL building usage, Configuration is really the "interesting" type behind the scenes. It would be a bit weird for DSLContext to be an argument type for any jOOQ SPI method.

In hindsight, it might have been interesting to introduce a TransactionContext that would give access to Configuration and, perhaps, DSLContext.
 
2. Is there a way to use these methods so that instead of creating nested transaction when one is called from another that they reference count?

Yes, you can implement your own TransactionProvider SPI for this purpose. Or use the DefaultTransactionProvider(ConnectionProvider, boolean) constructor to avoid nested transactions:

Hope this helps,
Lukas

robert....@cloudability.com

unread,
May 19, 2017, 10:39:34 PM5/19/17
to jOOQ User Group
That's perfect Lukas, thanks. I don't know that I've ever done anything interesting with Configuration other than bootstrap it, create a DSLContext and forget about it. Is there a blog post or anything that talks about the valuable things I could do with a configuration within a Transaction function?

Lukas Eder

unread,
May 22, 2017, 6:28:02 AM5/22/17
to jooq...@googlegroups.com
Hi Robert,

I think I gave you already an answer to this :)

> In hindsight, it might have been interesting to introduce a TransactionContext that would give access to Configuration and, perhaps, DSLContext.

Hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Ed Erwin

unread,
Feb 22, 2018, 7:27:43 PM2/22/18
to jOOQ User Group


On Friday, May 19, 2017 at 1:22:48 PM UTC-7, Lukas Eder wrote:


2017-05-19 18:08 GMT+02:00 <robert....@cloudability.com>:
Sorry if this has been asked before, but I have two basic questions.

1. Why do the functions for the transaction methods take a config instead of a DSLContext? Couldn't you have just gotten the DSLContext internally with DSL.using(config) so that the transaction function to execute just had the DSLContext?

While the DSLContext type is quite useful to your SQL building usage, Configuration is really the "interesting" type behind the scenes. It would be a bit weird for DSLContext to be an argument type for any jOOQ SPI method.


I'm struggling to try to find the right way to pass context (or configuration or transaction or connection) around between multiple separate methods of my classes.

Suppose I have a method like this, and I want to call it perhaps sometimes inside a transaction and sometimes not inside a transaction:
public void updateBook(int bookId, int authorId) {}

Which of these alternative would be best, and is there an even better way?

public void updateBook(DSLContext context, int bookId, int authorId) {}
public void updateBook(Configuration configuration, int bookId, int authorId) {}
public void updateBook(Connection connection, int bookId, int authorId) {}


You comments here indicate that passing the DSLContext is not good, but which object should I pass?

Also, is there any difference between calling DSL.using(configuration) compared to configuration.dsl() ?



Lukas Eder

unread,
Feb 23, 2018, 4:57:09 AM2/23/18
to jooq...@googlegroups.com
Hi Ed,

2018-02-23 1:27 GMT+01:00 Ed Erwin <enw...@gmail.com>:
I'm struggling to try to find the right way to pass context (or configuration or transaction or connection) around between multiple separate methods of my classes.

Suppose I have a method like this, and I want to call it perhaps sometimes inside a transaction and sometimes not inside a transaction:
public void updateBook(int bookId, int authorId) {}

Which of these alternative would be best, and is there an even better way?

public void updateBook(DSLContext context, int bookId, int authorId) {}
public void updateBook(Configuration configuration, int bookId, int authorId) {}

These two are equivalent as DSLContext is just a wrapper for a Configuration, which can be obtained either by calling DSL.using(configuration) or configuration.dsl() since jOOQ 3.10:
 
public void updateBook(Connection connection, int bookId, int authorId) {}

This one, I wouldn't recommend. After all, a jOOQ Configuration has more configuration information than just the JDBC Connection, including Settings (things like formatting, schema mapping, etc.) and all the other SPIs, like ExecuteListener. Also, if you decide to implement your own TransactionListener, then you will want that to be passed to your updateBook method through the Configuration.
 
You comments here indicate that passing the DSLContext is not good, but which object should I pass?

That comment you're referring to explained why the TransactionalRunnable doesn't pass the DSLContext to its transactional body:

I.e. it explains the rational of the library API.

You, on the other hand are free to do whatever you feel is most suitable. If you prefer working with the DSLContext type, you can pass that instead. From a user perspective, DSLContext and Configuration are equivalent types.
 
Also, is there any difference between calling DSL.using(configuration) compared to configuration.dsl() ?

No. The latter was introduced merely for convenience:

... mostly because most people prefer working with DSLContext rather than Configuration, and found accessing the DSLContext type from within a TransactionalRunnable a bit tedious :-)

I hope this clarifies the questions,
Lukas

Ed Erwin

unread,
Feb 23, 2018, 5:58:16 PM2/23/18
to jOOQ User Group


On Friday, February 23, 2018 at 1:57:09 AM UTC-8, Lukas Eder wrote:
Hi Ed,

 
Also, is there any difference between calling DSL.using(configuration) compared to configuration.dsl() ?

No. The latter was introduced merely for convenience:


Thanks.  Your comments do make me feel more comfortable passing around the configuration object and using "configuration.dsl()".

Based on looking at the code, I know that "DSL.using(configuration)" will always produce a new instance of DefaultDSLContext.  It was not clear to me that "configuration.dsl()" would always produce an equivalent object.

Robert DiFalco

unread,
Feb 23, 2018, 6:53:23 PM2/23/18
to jooq...@googlegroups.com
One of the problems I had with the JOOQ transaction approach is that I could not enforce that users only make SQL calls through a transaction. I solved it by making this little class. 

class Transactor {

private final DSLContext ctx;

Transactor(DSLContext ctx) {
this.ctx = ctx;
}

<T> T apply(Function<DSLContext, T> sql) {
ctx.transactionResult(config ->
sql.apply(DSL.using(config)));
}

void accept(Consumer<DSLContext> sql) {
ctx.transaction(config ->
sql.accept(DSL.using(config)));
}
}

So instead of a DAO having a DSLContext instance variable they have a Transactor instance variable. This gives them no access to the DSLContext so no risk of doing non-transacted updates or inserts (we always have auto commit off). Maybe there is a better way but this worked out pretty well. Then we just have it used like so:

public OltpCredentialsDao(DSLContext dslContext) {
this.transactor = new Transactor(dslContext);
}
Optional<CredentialsRecord> findByUUID(UUID id) {
return transactor.apply(sql ->
Optional.ofNullable(
sql.selectFrom(CREDENTIALS)
.where(CREDENTIALS.UUID.eq(id.toString()))
.fetchAny()));
}

You can always call a method inside the apply or accept and pass the DSLContext but that will be a transacted arg. 

Just FWIW

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/FeXUH-yTUQc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

Ed Erwin

unread,
Feb 23, 2018, 7:08:37 PM2/23/18
to jOOQ User Group
Thanks Robert.

That is pretty cool.  But it seems to be hard to use correctly for nested transactions or any other case where the function you pass to "apply" needs to call subroutines.  You could pass your "Transactor" to subroutines giving them access to your original DSLContext, but they wouldn't have access to the special instance of Configuration that was created when you called "ctx.transaction(....)", so they might not be running in the same transaction.

That is the problem I'm trying to solve.  Many of my database manipulation routines can be broken down into a series of smaller actions which I put into subroutines and I want to make sure those happen in the same transaction.

Robert DiFalco

unread,
Feb 23, 2018, 8:44:56 PM2/23/18
to jooq...@googlegroups.com
For those I pass the DSLContext from within the transaction to the method as an argument. That way I don’t nest transactions. 

Sent from my iPhone
--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/FeXUH-yTUQc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages