use of PooledConnection and ConnectionPoolDataSource

Tue Aug 11 15:13:32 UTC 2015

I am not sure if the addition of beginRequest and endRequest make things
clearer; I think it muddles the water even further. I have read the javadoc
multiple times, but I'm not sure I understand the use of these methods nor
its semantics. It says "Use of these methods is optional. Their use should
largely be transparent, however if used at all their use must conform to
this spec."; I don't think this is clear at all.

What should/could I do in a driver implementation in response to a
beginRequest (or endRequest), should I be thinking of things like
* Close all result sets
* Clear/close pooled statements (or open statements)
* Commit (or rollback?) any active transactions on the connection
* Reset the auto commit status to true

or of something else.

The doc also says "In particular implementations may detect conditions
that indicate dependence on other work such as an open transaction.", and
what should be the action if such conditions are detected?

The lack of clarity and guidance (and "The exact behavior of this method
is vendor specific.") will increase driver divergence, instead of leading
to a common standard that allows users (and driver implementers) to have a
common understanding of the behavior of all JDBC drivers without having to
wonder why calling this method with an open transaction throws an
SQLException with driver X, while it commits the transaction with driver Y
and rolls back the transaction with driver Z (and driver Y leaving
autoCommit = false and driver Z resetting it to true).

I think that a common standard of behavior should be the leading design
principle for JDBC, and I am not really seeing that with this feature. I'd
like to see guidance on error behavior (eg what conditions should or should
not throw an Exception) and expected and explicitly forbidden behavior (say
around resetting autoCommit and closing statements/result sets).

I am also afraid people are going to call beginRequest out of some
cargo-cult, based on the "Use of these methods may improve performance or
provide other benefits.".

I'd like to see more clarification on ConnectionPoolDataSource and what it
should be doing, and how it should be used.

Mark

On Tue, 11 Aug 2015 10:41:39 -0400, Lance Andersen
<lance.andersen at oracle.com> wrote:
> You can find the proposed clarification as well as the javadocs for the
> for Connection.beginRequest & Connection.endRequest
> 
> You can find the changes at
> http://cr.openjdk.java.net/~lancea/8085984/specdiffsjdbc43/
> 
> 
> On Dec 16, 2014, at 1:11 PM, Mark Rotteveel <mark at lawinegevaar.nl>
wrote:
> 
>> I agree that additional clarification in the specification and the
>> javadoc would be nice. I have seen several implementations (including
>> within Jaybird), that had entirely missed the point of what a
>> `ConnectionPoolDataSource` should be.
>> 
>> Mark
>> 
>> On 16-12-2014 19:04, Lance Andersen wrote:
>>> fyi
>>> 
>>> Begin forwarded message:
>>> 
>>>> From: Douglas Surber <douglas.surber at oracle.com>
>>>> Subject: [jsr-221-eg] use of PooledConnection and
>>>> ConnectionPoolDataSource
>>>> Date: December 15, 2014 8:07:31 PM EST
>>>> To: "jsr-221-eg at jcp.org" <jsr-221-eg at jcp.org>
>>>> Reply-To: jsr-221-eg at jcp.org
>>>> 
>>>> Hi,
>>>> 
>>>> The javax.sql.PooledConnection class JavaDoc contains the following:
>>>> 
>>>> 
>>>>> When an application calls the method DataSource.getConnection, it
>>>>> gets back a Connection object. If connection pooling is being done,
>>>>> that Connection object is actually a handle to a PooledConnection
>>>>> object, which is a physical connection.
>>>>> 
>>>>> The connection pool manager, typically the application server,
>>>>> maintains a pool of PooledConnection objects. If there is a
>>>>> PooledConnection object available in the pool, the connection pool
>>>>> manager returns a Connection object that is a handle to that
physical
>>>>> connection. If no PooledConnection object is available, the
connection
>>>>> pool manager calls the ConnectionPoolDataSource method
>>>>> getPoolConnection to create a new physical connection. The JDBC
driver
>>>>> implementing ConnectionPoolDataSource creates a new PooledConnection
>>>>> object and returns a handle to it.
>>>>> 
>>>>> When an application closes a connection, it calls the Connection
>>>>> method close. ...
>>>>      -- from
>>>>     
http://docs.oracle.com/javase/8/docs/api/javax/sql/PooledConnection.html
>>>> 
>>>> PooledConnection also defines the following method:
>>>> 
>>>> 
>>>>> Connection getConnection()
>>>>>                  throws SQLException
>>>>> Creates and returns a Connection object that is a handle for the
>>>>> physical connection that this PooledConnection object represents.
The
>>>>> connection pool manager calls this method when an application has
>>>>> called the method DataSource.getConnection and there are no
>>>>> PooledConnection objects available. See the interface description
for
>>>>> more information.
>>>>> 
>>>>> Returns:
>>>>> a Connection object that is a handle to this PooledConnection object
>>>> 
>>>> 
>>>> Note that the class JavaDoc says "that Connection object is actually
a
>>>> handle to a PooledConnection object" and getConnection returns "a
>>>> Connection object that is a handle to this PooledConnection object".
I
>>>> believe the intent was that the pool manager gets a "handle to a
>>>> PooledConnection object" by calling PooledConnection.getConnection.
The
>>>> class JavaDoc doesn't say this specifically, saying only that it
>>>> returns "a handle". The Oracle database team is of the opinion that
>>>> some pool managers do not construct the returned handle by calling
>>>> PooledConnection.getConnection. If so, this is a problem.
>>>> 
>>>> A pool manager configured with a ConnectionPoolDataSource gets
>>>> physical connections by calling
>>>> ConnectionPoolDataSource.getPooledConnection. This returns a
>>>> PooledConnection. When the application requests a connection from the
>>>> pool manager I believe the intent was that the pool manager call
>>>> PooledConnection.getConnection and return that (logical) Connection
or
>>>> a wrapper around it. When the app is done with the Connection it
calls
>>>> Connection.close on the logical Connection or on the wrapper and then
>>>> the wrapper calls close on the logical Connection.
>>>> 
>>>> The benefit of this sequence is that the driver that provided the
>>>> ConnectionPoolDataSource is notified of the getConnection and close
>>>> calls. The PooledConnection object is constructed by the
>>>> ConnectionPoolDataSource and so can be of any class the
>>>> ConnectionPoolDataSource wishes. In particular it can be a class
>>>> provided by the same driver as provided ConnectionPoolDataSource.
>>>> PooledConnection.getConnection can execute any code desired by the
>>>> ConnectionPoolDataSource vendor, effectively notifying the underlying
>>>> driver if desired. Similarly the object constructed by
>>>> PooledConnection.getConnection can be of any class and so can execute
>>>> any code desired by the ConnectionPoolDataSource vendor. In
particular
>>>> the Connection.close method can notify the underlying driver that the
>>>> logical Connection is no longer in use.
>>>> 
>>>> Use of PooledConnection.getConnection and Connection.close as
>>>> described above permits the underlying driver to know when a physical
>>>> connection is being used and when it is idle. This is critical
>>>> information in some circumstances.
>>>> 
>>>> After working with various pool managers, the Oracle database team
>>>> believes that some pool managers do not work this way. (I'm not going
>>>> to say which ones as their research is preliminary.) Their work
>>>> suggests that some pool managers call PooledConnection.getConnection
>>>> and then effectively pool the returned logical Connection object. The
>>>> pool managers wrap that logical connection in a proxy and return the
>>>> proxy in response to a connection request. The proxy handles
>>>> Connection.close without calling Connection.close on the logical
>>>> Connection. The logical Connection is returned to the pool. In this
>>>> sequence of events neither PooledConnection.getConnection nor
>>>> Connection.close are called on the PooledConnectionDataSource
provided
>>>> objects. The result is that the underlying driver is not notified
that
>>>> the physical Connection is in use or idle. This is a problem.
>>>> 
>>>> While the PooledConnection class comment is anything but clear, I
>>>> believe that the behavior of these pool managers does not conform to
>>>> the intent of the specification. I propose that we clarify the spec
so
>>>> that it clearly requires that any pool manager respond to a
>>>> getConnection request by calling PooledConnection.getConnection and
>>>> that if it wraps the resulting logical Connection object that it (or
>>>> the wrapper) call Connection.close on the resulting logical
Connection
>>>> in response to the app calling Connection.close on the wrapper or any
>>>> other time a vended connection is closed. As this is purely a
>>>> clarification of existing documentation I would like it to be
included
>>>> in Java 9.
>> 
>> -- 
>> Mark Rotteveel
> 
> 
> 
> Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
> Oracle Java Engineering 
> 1 Network Drive 
> Burlington, MA 01803
> Lance.Andersen at oracle.com