ConnectionProvider API

[Christopher Deckers]

Hi Lukas,
 

There is this new ConnectionProvider type:

http://www.jooq.org/javadoc/latest/org/jooq/ConnectionProvider.html

I don't understand the Javadoc...

acquire:
"The general contract is that a ConnectionProvider is expected to always return the same connection, until this connection is returned by jOOQ through release(Connection)."
release:
"The general contract is that a ConnectionProvider must not generate a new connection in acquire(), before a previous connection is returned."

It seems to say that:

Connection c1 = p.acquire();
Connection c2 = p.acquire();

=> c1 == c2;

And in that case, do we have to release it twice too?

so if a user sets a connection provider for things like the SQL Console (that is multi tabs, etc.) we have no way of acquiring/releasing new connections for different concurrent usages using a (shared) ConnectionProvider. We don't want the user to supply a ConnectionProviderFactory for that :)

Instead, I would expect acquire() to always return a new unused connection (e.g.: creation or taken from a pool), and release() to notify that it is now unused (can be disposed or placed back into a pool).

Or maybe there is something I am missing with regards to how JDBC connections work? Also consider that I expected one ConnectionProvider which multiple threads could call to acquire new connections (and I am not sure the same connection can be used by multiple threads).

Cheers,

-Christopher

[Lukas Eder]

Hi Christopher,

Thanks for asking. This might be a sore spot to be fixed before 3.0.0 GA.

I've been digging in the code base to remember the reason(s) for this contract. There are in fact three that I can remember:

1. The idea was inspired by the various discussions about pooled javax.sql.DataSources, specifically in the context of a javax.transaction.UserTransaction, which (sometimes) also guarantee to return the same connection (c1 == c2) for subsequent calls to DataSource.getConnection(). This is not specified in DataSource, but it is implemented this way by some containers and pools. In that case, Connection.close() doesn't have any effect before a tx.commit() or rollback(). Likewise, DataSource.getConnection() only returns a fresh connection (or the same, pooled one), once the surrounding JTA transaction has finished.

2. (Unfortunately), keeping this behaviour intact lead to easier implementation of jOOQ's internals, when ConnectionProvider was introduced. This might have been the driving force for this contract - thus "unfortunately".

3. Aaron Digulla's original contribution also worked this way. He might as well just have wanted to maintain the contract that was discussed on this user group, though.

Now in fact, there is no reason why the current contract should be maintained this way. It might be wise to loosen it. This would indeed allow for decoupling ConnectionProviders from the lifecycles of a transaction / Executor. jOOQ would guarantee to implementors that both p.acquire() and p.release() be called exactly once per created statement. A case where the number of created statements does not coincide with the number of executed queries is when a Query is executed with keepStatement = true. Then, the call to p.release() is postponed until the Query (and its underlying Statement) is closed - possibly after many subsequent executions:

http://www.jooq.org/doc/3.0/manual/sql-execution/reusing-statements

Please, group, feel free to join this discussion. As long as we have release candidates, these things can be changed easily.

Cheers

Lukas

PS: acquire/release seemed to me the best matching verbs for the task. Any alternatives, specifically for "acquire" are welcome, too :-)

2013/3/11 Christopher Deckers <chr...@gmail.com>

--
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+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Peter Cooner]

I've not used javax.transaction.UserTransaction for my transaction - instead because we make heavy use of Google's Guice library, I created an annotation @Transaction turning any function with a Connection argument into a transaction. Not sure if we'll stick to this, but so far it works well enough, its very flexible. For pooling we're using Apache's DBCP which will pool connections and statements.

Pete

[Lukas Eder]

Hi Peter,

So what would your setup mean for the jOOQ 3.0 ConnectionProvider contract?

Cheers

Lukas

2013/3/12 Peter Cooner <petr...@gmail.com>

[Peter Cooner]

Hah, sorry, thats what I get for writing email late at night - clearly my brain had disengaged.

I intended to express my own confusion over ConnectionProvider.

When I started working with JOOQ I had wanted to create a single Executor instance and @Inject the object whenever I needed to execute queries. Because Executor isn't thread safe I instead @Inject a JooqExecutorFactory which returns my own InjectExecutor object which is basically bound to a single Connection, and Executor instance (and transaction). I still feel like that's probably pretty dumb - like I'm missing something - but I didn't have time to unravel how ConnectionProvider could be useful and what sort of state the Executor maintains.

Just to be clear, what I do know is that JOOQ helps us write better queries and transform those queries for table partitioning - a big win.

[Lukas Eder]

Since I've had no objections to Christopher's claims, I will change
the jOOQ 3.0 contract of the ConnectionProvider. The new contract will
include:

acquire(): Provide jOOQ with a Connection. The ConnectionProvider can
freely choose, whether this connection is a new connection every time
jOOQ calls this method, or if the same connection will be provided
again and again. jOOQ will call this method exactly once per
execution, when the ExecuteContext is initialised

release(): Clean up a Connection that was previously provided to jOOQ.
The ConnectionProvider can freely choose, whether cleaning up needs
any action or not. jOOQ will call this method exactly once per
execution, when the ExecuteContext is disposed (before
ExecuteListener.end())

This will be implemented for jOOQ 3.0 through #2351
https://github.com/jOOQ/jOOQ/issues/2351

Existing ConnectionProvider implementations (on jOOQ 3.0-RC1 or RC2)
will continue to work. Only jOOQ's internals are affected by this
change.

Cheers
Lukas

2013/3/12 Peter Cooner <petr...@gmail.com>:

[Christopher Deckers]

Hi Lukas,

I don't know if the wording is incorrect or if the contract is loose in a way that may cause problems (and does not satisfy what I hoped I could do with it).

acquire(): Provide jOOQ with a Connection. The ConnectionProvider can
freely choose, whether this connection is a new connection every time
jOOQ calls this method, or if the same connection will be provided
again and again.

The "again and again" part is true provided that it was first released, is that correct?

What I expect is acquire to reserve a connection. A new call to acquire would reserve another connection. The only way to release a connection is to release it.

Otherwise, if the same provider is used by 2 different threads, it is unclear whether serving the same connection is a good thing.

 

release(): Clean up a Connection that was previously provided to jOOQ.
The ConnectionProvider can freely choose, whether cleaning up needs
any action or not. jOOQ will call this method exactly once per
execution, when the ExecuteContext is disposed (before
ExecuteListener.end())

I don't see why the connection provider javadoc mentions jOOQ execution lifecycle. In my view, connection provider should be a generic utility, with a Javadoc that reads:

acquire(): Reserve and return a connection for dedicated use to the caller. The caller must release this connection when finished. Note that this method could be implemented to always return a new connection or take one from a pool.

release(Connection): Indicate that a connection previously obtained through acquire() is not needed anymore and can be disposed or made available to future calls to acquire().

This way, other tools/APIs could make use of that Connection Provider.

Hope this helps,

-Christopher

[Lukas Eder]

Hi Christopher,

2013/3/24 Christopher Deckers <chr...@gmail.com>:

> Hi Lukas,
>
> I don't know if the wording is incorrect or if the contract is loose in a
> way that may cause problems (and does not satisfy what I hoped I could do
> with it).
>
>> acquire(): Provide jOOQ with a Connection. The ConnectionProvider can
>> freely choose, whether this connection is a new connection every time
>> jOOQ calls this method, or if the same connection will be provided
>> again and again.
>
> The "again and again" part is true provided that it was first released, is
> that correct?

That is entirely up to the implementation. jOOQ will acquire() a
connection every time it needs one. jOOQ will release() a connection
every time it has finished with one. An example where this might be
non-trivial for implementations:

1. Query Q1 is executed with fetchLazy()
2. Query Q2 is executed from the same executor with execute()
3. Query Q2 releases the connection
4. Query Q1's result is completely fetched, and releases the connection

As you can see, there are two logical connections involved. Both are
acquired from the same ConnectionProvider, but they are not released
in the same order. The order is this:

1. acquire C1
2. acquire C2
3. release C2
4. release C1

Whether C1 and C2 need to be distinct physical connections, or whether
they may be the same is up to the implementation (your
implementation). This allows to externalise various connection pooling
/ transaction models, without jOOQ needing to know about them.

> What I expect is acquire to reserve a connection. A new call to acquire
> would reserve another connection. The only way to release a connection is to
> release it.
> Otherwise, if the same provider is used by 2 different threads, it is
> unclear whether serving the same connection is a good thing.

This means that you will have to keep an identity map in your
ConnectionProvider, remembering which connection was already acquired
by jOOQ. If you're using a connection pool, you will likely be able to
just delegate the handling to that pool, as many pools implement
precisely what you've stated above.

>> release(): Clean up a Connection that was previously provided to jOOQ.
>> The ConnectionProvider can freely choose, whether cleaning up needs
>> any action or not. jOOQ will call this method exactly once per
>> execution, when the ExecuteContext is disposed (before
>> ExecuteListener.end())
>
> I don't see why the connection provider javadoc mentions jOOQ execution
> lifecycle. In my view, connection provider should be a generic utility, with
> a Javadoc that reads:
>
> acquire(): Reserve and return a connection for dedicated use to the caller.
> The caller must release this connection when finished. Note that this method
> could be implemented to always return a new connection or take one from a
> pool.
>
> release(Connection): Indicate that a connection previously obtained through
> acquire() is not needed anymore and can be disposed or made available to
> future calls to acquire().

I think that mentioning the jOOQ execution lifecycle will help users
implement this interface correctly. Saying that jOOQ will call
acquire() exactly once per execution will help predict things when
running more complex transactions with 1-n Executor instances in jOOQ.
Your correction will make the contract a bit vague. "Dedicated use"
does not indicate *when* a connection is released by the "caller"
(jOOQ).

I think that explicitly or implicitly tying acquire() to the creation
of an ExecuteContext and release() to ExecuteListener.end() will make
the general jOOQ API more concise and predictable. Maybe, however, it
is time to create a couple of drawings for the manual, explaining the
various actors involved in jOOQ's various object lifecycles...

The various implementation possibilities are already mentioned in the
Javadoc here:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/ConnectionProvider.java

> This way, other tools/APIs could make use of that Connection Provider.

What tools / APIs did you have in mind?

[Christopher Deckers]

Hi Lukas,

What tools / APIs did you have in mind?

I was thinking about the jOOQ console: the editor needs a different connection for every tab. Because of that level of isolation, I need different connections. I can't use a connection provider and would have to define a different connection provider where the contract is that acquire gives a dedicated connection.

But ignore the console for a moment. My problem is that conceptually there are 2 needs:

1. A way to plug a connection factory (which reserves and releases connections) that various places of the library can use.

2. The way that factory is used to acquire a connection, use it once or many times, and release it, in various places of the library.

So as a user I would expect to define my pooling logic:

ConnectionProvider cp = new PoolConnectionProvider();

And use it in different places:

a. Guarantee of a dedicated connection for 2 calls even if they use fetchLazy, immune to rollbacks, etc. (pseudo-code):
Executor create1 = new Executor(cp, SQLDialect.MYSQL);

R1 r1 = create1.fetchLazy();

Executor create2 = new Executor(cp, SQLDialect.MYSQL);

R2 r2 = create2.xxx();
// here r2 is fully consumed, connection 1 is released.
// here r1 is fully consumed, connection 2 is released.

b. Connection re-use:
Executor create = new Executor(cp, SQLDialect.MYSQL);

R1 r1 = create.fetchLazy();

R2 r2 = create.xxx();

// here r2 is fully consumed.
// here r1 is fully consumed, connection is released.

For me, Executor is one place of the library that needs a connection. It can keep a counter internally for that special case of fetchLazy to only release when it reaches 0 so that the same connection is re-used (and maybe this counter is thread local so that 2 different threads are guaranteed to have their own connection if it is something you feel is beneficial). Using 2 different Executor instances with the same provider should guarantee using 2 different connections, though they can use the same connection pool by sharing the same pool-based connection provider.

Going further, the connection provider set in the Configuration can be used by various places of the library without having to wonder whether there are risks of connection sharing.

I am also thinking that the execute listener context should expose both the connection as well as the connection provider, so that users can execute requests in the context of the connection or acquire a new connection if there are unrelated requests they want to run, etc.

Have I missed anything? Do you get a feel for the needs I am trying to express? What do you think?

Cheers,

-Christopher

[Lukas Eder]

Hi Christopher,

I'm sorry, this mail was lying around unanswered for a while.

2013/3/24 Christopher Deckers <chr...@gmail.com>:

I don't agree with these thoughts. The Executor should have no impact
whatsoever on the behaviour of the connection provider. The whole
point of introducing a connection provider is to externalise *all*
responsibility of when connections are created and when pre-existing
ones are returned to a single entity.

In both cases a) and b), it is client code who knows the "ultimate
truth" about transactional contexts. It should be client code who is
in control of telling jOOQ how to behave. So if you want a) and b) to
behave differently, then you will need to implement different
connection providers.

> Going further, the connection provider set in the Configuration can be used
> by various places of the library without having to wonder whether there are
> risks of connection sharing.
> I am also thinking that the execute listener context should expose both the
> connection as well as the connection provider, so that users can execute
> requests in the context of the connection or acquire a new connection if
> there are unrelated requests they want to run, etc.

I can see how this is relevant to the jOOQ Console. Here's how to
achieve the above:

// Get a hold of the JDBC Connection being used in the
// current execution context. This may lazy-initialise the
// Connection, acquiring it from the ConnectionProvider
ExecuteContext.connection();

// Get a hold of the ConnectionProvider from the
// surrounding Configuration:
ExecuteContext.configuration().getConnectionProvider();

> Have I missed anything? Do you get a feel for the needs I am trying to
> express? What do you think?

Maybe we should start discussing about concrete use cases of the jOOQ
Console, instead? As soon as I've heard "ThreadLocal", I thought that
this discussion isn't about general API and contracts any longer...

Note, it is good to challenge an API / contract from the point of view
of a very concrete use-case. So, feel free to shed some light on your
use-case's details :-)

Cheers
Lukas