use of PooledConnection and ConnectionPoolDataSource

Tue Aug 11 16:17:29 UTC 2015

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.

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.

The spec says that there is no dependence on local state across 
requests. 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.

Douglas

At 08:13 AM 8/11/2015, Mark Rotteveel wrote:
>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