use of PooledConnection and ConnectionPoolDataSource

[Mark Rotteveel]

On Tue, 11 Aug 2015 09:17:29 -0700, Douglas Surber
<douglas.surber at oracle.com> wrote:
> The request boundary methods notify the core driver of when a 
> connection is in use and when it is in a pool. Ideally 
> PooledConnection.getConnection and Connection.close should have 
> provided that information but that's not how it worked out. Some 
> pools don't use PooledConnection at all. Some use PooledConnection 
> but actually pool the Connection handle returned by 
> PooledConnection.getConnection and never call close. In either case 
> the core driver doesn't know when a physical Connection is in use by 
> an app and when it is sitting idle in the pool. Request boundaries 
> are a substitute for PooledConnection.getConnection and 
> Connection.close for those pools that don't use them.

I know that connection pools are a weird and magic place (in part caused
by lack of clarity and detail in present (or past) specifications. I think
this should be addressed primarily by improving the specification of
existing interfaces, methods and expected behaviors.

I can also see why a beginRequest and endRequest might be useful for
connection pools that don't use ConnectionPoolDataSource (or use it
incorrectly), but the current proposal lacks in detail, clarity and
specification expected or discouraged behavior. This will only serve the
make JDBC connection pools a weirder and more magical place, because each
and every driver will do what they please (or do nothing at all), leading
to more and not less inconsistent behavior between drivers and connection
pools.

I think at minimum the driver should specify the expected outcome and
failure conditions of a call to these methods, in addition to specifying
prohibited/discouraged behavior.

> How a driver makes use of this information is totally up to the 
> driver. There is no intent to specify that a driver do anything in 
> particular. What the spec does require is that pools and other 
> frameworks call these methods at specific points so that the driver 
> can do whatever it wants.

JDBC users (if they are connection pools, or users of connections
themselves), need to be able to have a common understanding and mental
model of JDBC connections, irrespective of the underlying implementation. I
think leaving everything up to the driver is a bad idea, because it doesn't
allow users to build that model, and will cause pain.

> The spec says that there is no dependence on local state across 
> requests. 

And exactly what does that mean?

> As a result the core driver could close the physical 
> connection on the first endRequest and open a new one on the first 
> beginRequest or other action. That's just an example of what a driver 
> might do. What it actually does is intentionally unspecified.

I propose that at the beginRequest should specify something like "After a
call to beginRequest, the Connection must be in the same state and
configuration as if it was just created." (which based on my reading of the
JDBC spec includes resetting the autoCommit state to true, and closing any
open resources, and maybe resetting things like transaction isolation,
etc), and endRequest something like "After a call to endRequest the
connection will release all resources (Statements, ResultSets, etc), and
rollback any active transactions" (ie: do everything short of actually
closing the connection). For error conditions, I'd think that participating
in a distributed transaction should lead to an error for calling either
method, as would calling on a really closed connection, and it should
propagate errors that are the result of the actions to bring the connection
in the state specified above.

Mark