Fwd: use of PooledConnection and ConnectionPoolDataSource

[Mark Rotteveel]

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