pooled connection myth

[David McDivitt]

I want to send an email to people in my unit containing something similar to
the following and I would appreciate comments first:



When putting frameworks together they are made with the idea of being
extensible. Because of that, big nebulous words, concepts, and abstractions
are often used. For the most part Java is plug and play. Objects are
encapsulated. When you ask for something through a method it is given back
to you and you should not care how it comes to exist. If a platform is
enhanced or extended it should make no difference. I do not wish to dispute
the utility of this basic java premise.

When calling the method getPooledConnection, it would imply you are getting
a truly pooled or shared connection. What you are getting is what the
framework wants you to have, and that is within the scope of present
implementation. Java is very robust and it's possible to connect to anything
and everything in the universe from one application. Certainly some of these
resource connections will be grouped together. In my investigation of
existent applications we have, grouping is only done at the resource driver
level. Individual database connections are not pooled or shared.

To support my assertion, if datasource.xml is viewed in the struts
framework, the driver COM.ibm.db2.jdbc.app.DB2Driver can be found. This
driver only works one way. There is no secret, hidden, marvelous, behind the
scenes functionality present in the app server, or the DB2 database, when
this driver is used. There is no reusability of connection objects created
with this driver. If the close method is done on a connection object it
cannot be used again. It is history, toast, gone. Only a new connection can
be instantiated from the DB2 driver, which involves use of a user name and
password, again. If in code you find the close method being called on
connection objects, necessarily, those connection objects go away. Also, on
a connection object, if transaction blocks are used, such apply to the whole
connection object. If threads do somehow share a connection and one starts a
transaction block, it would apply to the other thread as well. There is no
connection within a connection. The same goes for the autocommit property
(dirty writes).

Due to extensibility considerations, and potential grandiose use of
applications, the pooled connection concept might be appropriate, but is
dependent on certain java coding standards within an application and what is
done with connections. It cannot be assumed connections are pooled at a low
level simply because a method name says so.

It is possible to code a singleton, run all connection requests through such
a connection manager, and simply return the same connection object each time
it's asked for. The struts framework does not do this for you. The
application must be coded that way, validity of the connection must be
tested at a timed interval in a separate thread, and no methods can be
called anywhere to modify the connection. A good connection manager might
use three connection objects and return them round robin to callers.

Not understanding connection functionality and assuming certain
functionality exists on faith contributes to database problems.

 

[Malte]

David McDivitt wrote:
connections are pooled at a low

level simply because a method name says so.

It is possible to code a singleton, run all connection requests through such
a connection manager, and simply return the same connection object each time
it's asked for. The struts framework does not do this for you. The
application must be coded that way, validity of the connection must be
tested at a timed interval in a separate thread, and no methods can be
called anywhere to modify the connection. A good connection manager might
use three connection objects and return them round robin to callers.

We have succesfully used Oracle's OCI driver (the thin type 4 driver is
no good for this) by wrapping it in a Singleton and have access methods
return a connection from the pool (in one of our programs we set the
initial number of connections to 50 and increase by 5). Of course, the
RDBMS must also be configured to allow a large number of connections (in
our case 160).

This actually works (with some glitches when Oracle 10G is running on
Linux). I don't know about the DB2 drivers as I have not used DB2 since
1997.

As to the data source stuff: we never got it up and running as a pool.

Alas, YMMV, is our finding.

 

[David McDivitt]

I hope some people comment on my original posting concerning DB2 or pooled
connections in general. Anyway, since you have a singleton, why not return
the same connection to each caller? Why do you need 160 possible
connections? In my connection manager I return one out of three in a round
robin fashion. Another method exists to return a standalone connection for
times when transactions might be used or other modifications done to the
connection object.

 

[Malte]

I hope some people comment on my original posting concerning DB2 or pooled
connections in general. Anyway, since you have a singleton, why not return
the same connection to each caller? Why do you need 160 possible
connections? In my connection manager I return one out of three in a round
robin fashion. Another method exists to return a standalone connection for
times when transactions might be used or other modifications done to the
connection object.

Hmm, the Singleton is for a getInstance of my class which wraps Oracle's
driver. It is not for the connection. Sorry for being unclear on that.

My Singleton returns the wrapper which has a getConnection() method
which returns a connection from the pool. It also sports a
releaseConnection() method that gives back the connection to the pool.

I couldn't not possibly do with only three connections, as this would
limit the number of concurrent users.

Obviously, I could roll my own pool using a Vector or something similar,
but for this project we opted for Oracle's implementation. I am happy to
report that, so far, our class performs very well.

 

[Lisa]

I want to send an email to people in my unit containing something similar to
the following and I would appreciate comments first:

<snip>
Not understanding connection functionality and assuming certain
functionality exists on faith contributes to database problems.

Connection pooling depends of several things. You need to make it clear
to your folks that you are describing the situation that they are in,
not the general case. I suggest you look at the book
"Java Database Best Practices," George Reese, O'Reilly, 2003, p 237-240.
He describes things pretty well, perhaps you can use some of his wording.

 

[David McDivitt]

You said the magic words: gives back the connection to the pool. There is no
pool. Each time a connection is closed it is gone. To use a connection, a
new one must be instantiated from the driver with user name and password.
What you describe is part of the myth regarding pooled connections. When the
framework pools, it pools drivers only. In other words, all connections made
to your Oracle go through the same instance of the Oracle driver. If you had
Oracle, DB2, Sybase, and SQL Server for instance, connections would be
pooled with regard to the major data source, but individual connections
would not be pooled.

To modify your connection manager, try instantiating a connection object at
class level and after the first connection request, continue returning the
same connection object from the class level. That one connection object
should support multiple threads. Add a timer thread to the connection
manager to read a dummy table each sixty seconds, and if it fails, null the
connection, and recreate on the next request. Finally, put a close method on
the connection manager to stop the timer thread to be used when the app
server stops. This is connection pooling. Anything less is not connection
pooling.

Connection managers may cache the database user name and password so they
don't have to be fetched from resources each time a connection is needed,
and that saves time, but from what I've seen, connection managers
instantiate a new connection object each time one is requested. That is not
connection pooling.

 

[David McDivitt]

Thanks. Part of the problem I have are the many assumptions people have
where they assume the app server does this or that. In an argument I had
today someone said various things are proven and tested and we should only
use stuff that is proven or tested. Obviously, unless one has knowledge of
what is proven or tested, in detail, just saying proven and tested doesn't
mean squat. A monstrosity of cut and paste has been constructed, with each
paste supposedly being what is proven and tested. The DB2 driver does not
support reuse of connection objects at all, and neither does any other
driver I know of, so there is no magic pooling of anything.

 

[John C. Bollinger]

You said the magic words: gives back the connection to the pool. There is no
pool. Each time a connection is closed it is gone. To use a connection, a
new one must be instantiated from the driver with user name and password.

You are making a rather large assumption: that the connection object you
obtain from the pool is an instance provided by the driver you
described. In fact, I would expect this to not be the case. I would
expect the pool to provide a Connection object that _wraps_ the
low-level Connection and, among other things intercepts close()
invocations as signals to release the underlying connection back to the
pool.

What you describe is part of the myth regarding pooled connections. When the
framework pools, it pools drivers only. In other words, all connections made
to your Oracle go through the same instance of the Oracle driver. If you had
Oracle, DB2, Sybase, and SQL Server for instance, connections would be
pooled with regard to the major data source, but individual connections
would not be pooled.

I think you're all wet. It is _possible_ for a DataSource or other
resource pool to operate as you describe, but it is certainly also
possible for them to provide true pooling -- of Connection objects, for
instance -- in such a way that the existence and proper use of the pool
is transparent to users of the pooled objects (once one is obtained).

To modify your connection manager, try instantiating a connection object at
class level and after the first connection request, continue returning the
same connection object from the class level. That one connection object
should support multiple threads. Add a timer thread to the connection
manager to read a dummy table each sixty seconds, and if it fails, null the
connection, and recreate on the next request. Finally, put a close method on
the connection manager to stop the timer thread to be used when the app
server stops. This is connection pooling. Anything less is not connection
pooling.

This is absurd. A typical connection pool holds multiple Connections,
and assigns them to clients on demand for the clients' exclusive use
until done. A pool will use a mechanism, such as I described above, to
reclaim the Connection for reuse once the client signals that it is done
with it. A pool will recognize when one of its Connections has failed,
possibly by a mechanism such as you describe, but more likely by some
other means, and will both avoid handing out dead connections and obtain
new ones at need. Safe use of JDBC connections *requires* that it be
possible for a client to obtain exclusive access to a Connection, and
this is not possible if the pool uses the same instance to support
multiple clients concurrently.

Connection managers may cache the database user name and password so they
don't have to be fetched from resources each time a connection is needed,
and that saves time, but from what I've seen, connection managers
instantiate a new connection object each time one is requested. That is not
connection pooling.

A DataSource or reliable connection pool needs to be configured with
enough information to obtain connections, and this will include username
and password if they are required. DataSources do not necessarily
perform connection pooling, and if they don't then they will operate as
you describe. Some DataSources certainly DO perform connection pooling,
however, as do various other connection pools.

 

[John C. Bollinger]

Thanks. Part of the problem I have are the many assumptions people have
where they assume the app server does this or that. In an argument I had
today someone said various things are proven and tested and we should only
use stuff that is proven or tested. Obviously, unless one has knowledge of
what is proven or tested, in detail, just saying proven and tested doesn't
mean squat. A monstrosity of cut and paste has been constructed, with each
paste supposedly being what is proven and tested. The DB2 driver does not
support reuse of connection objects at all, and neither does any other
driver I know of, so there is no magic pooling of anything.

I'm sure you're right that the Connection objects provided by the DB2
driver are not usable after they have been close()d, but that doesn't
mean that connection pooling cannot be enabled by a layer on top of the
driver. As I described in my other response, the pool would not
directly expose the Driver's native Connection objects, so they could
not be inappropriately closed.

Connection pooling is not a myth. Connection pools are real, and
generally work as advertised. One should, however, be careful about
assuming that connections are being pooled in any particular scenario.
You generally have to _configure_ a pool for an application that wants
to use one -- simply running in an application server does not
automatically get you connection pooling, for instance. Most
application servers do provide connection pool implementations, however,
and there are others available for standalone applications.

 

[Virgil Green]

You said the magic words: gives back the connection to the pool.
There is no pool. Each time a connection is closed it is gone. To use
a connection, a new one must be instantiated from the driver with
user name and password. What you describe is part of the myth
regarding pooled connections. When the framework pools, it pools
drivers only. In other words, all connections made to your Oracle go
through the same instance of the Oracle driver. If you had Oracle,
DB2, Sybase, and SQL Server for instance, connections would be pooled
with regard to the major data source, but individual connections
would not be pooled.

To modify your connection manager, try instantiating a connection
object at class level and after the first connection request,
continue returning the same connection object from the class level.
That one connection object should support multiple threads. Add a
timer thread to the connection manager to read a dummy table each
sixty seconds, and if it fails, null the connection, and recreate on
the next request. Finally, put a close method on the connection
manager to stop the timer thread to be used when the app server
stops. This is connection pooling. Anything less is not connection
pooling.

Connection managers may cache the database user name and password so
they don't have to be fetched from resources each time a connection
is needed, and that saves time, but from what I've seen, connection
managers instantiate a new connection object each time one is
requested. That is not connection pooling.

For the most part, I agree with John Bollinger. As I read your original
post, I found myself asking questions like "what framework?" and "what
getPooledConnection() method?".

I couldn't understand why you thought connections would be closed rather
than being returned to a pool to be reused later. Even if the connection you
are given supports a close() method, I'd expect it to be (as John described)
intercepted by whatever wrapper object was implementing the Connection
interface and treated as a "return to pool" operation instead.

In fact, I found my self asking "why is there a getPooledConnection() method
at all?" I wouldn't expect to ask specifically for a pooled connection. I'd
expect that whatever infrastructure was being used would have a class,
possibly a Singleton, that was responsible for handing out connections and
only that class would have knowledge of where the connection came from and
whether it was pooled. I'd expect to just call a getConnection() method and
not worry about anything other than "closing" it via a close method that the
supporting class would handle by doing a real close or by returning it to
the pool.

In other words, most of my code should be oblivious to whether pooling is
being used. It should just ask for connections and "close" them when
finished with them. The class that is handing out connections should be free
to change between pooled and non-pooled connections as needed. It might even
used pooled connections when they are available over an internal network
interface and then used non-pooled connections when another route has to be
used to establish a connection.

I have an application where we use a leased line for access to a remote
system, but we have internet access as an automatic (within my code)
failover if the leased line is down for some reason. I don't, but I might
pool connections when the leased line is up and simply hand out new
connections for each request when I have to hit the internet instead.

I think the "myth" you are describing is something that is specific to your
infrastructure and has little to do with traditional connection pooling in a
Java environment.

 

[Lee Fesperman]

I want to send an email to people in my unit containing something similar to
the following and I would appreciate comments first:

I'm not sure where you're getting your information, but I'd suggest you revise the
email....

When calling the method getPooledConnection, it would imply you are getting
a truly pooled or shared connection. What you are getting is what the
framework wants you to have, and that is within the scope of present
implementation. Java is very robust and it's possible to connect to anything
and everything in the universe from one application. Certainly some of these
resource connections will be grouped together. In my investigation of
existent applications we have, grouping is only done at the resource driver
level. Individual database connections are not pooled or shared.

To support my assertion, if datasource.xml is viewed in the struts
framework, the driver COM.ibm.db2.jdbc.app.DB2Driver can be found. This
driver only works one way. There is no secret, hidden, marvelous, behind the
scenes functionality present in the app server, or the DB2 database, when
this driver is used. There is no reusability of connection objects created
with this driver. If the close method is done on a connection object it
cannot be used again. It is history, toast, gone. Only a new connection can
be instantiated from the DB2 driver, which involves use of a user name and
password, again. If in code you find the close method being called on
connection objects, necessarily, those connection objects go away. Also, on
a connection object, if transaction blocks are used, such apply to the whole
connection object. If threads do somehow share a connection and one starts a
transaction block, it would apply to the other thread as well. There is no
connection within a connection. The same goes for the autocommit property
(dirty writes).

The general concept you're pushing is incorrect. First, connection pooling does work
(I'll explain in a moment). Sharing a connection concurrently doesn't work, for the
reasons you mentioned. Sharing it serially does work and is the basis of connection
pooling.

You're right that a regular JDBC driver doesn't support connection pooling at all.
However, connection pooling software, like in an app server, solves this by wrapping the
objects (Connection, Statement, ResultSet, ...) of the driver. This is easy because the
important pieces of the driver are all implementations of interfaces.

For instance, the connection pooling software uses its Connection wrapper to control
calls to close(), so that close() doesn't close the connection. Instead, the wrapper
close() returns the connection to the pool ... also doing necessary cleanup. There some
other details, but that is the crux of it. The connection pooling software can pool
connections but only allows one application to use it at a time. When the connection
pooling software is through with the connection, it calls close() in the actual
connection. A well-written set of wrappers prevents premature closing.

The driver provider can provide more support for connection pooling by implementing
javax.sql.ConnectionPoolDataSource and javax.sql.PooledConnection. In this case, the
driver provider does its own wrapping in so many words and gives control to the app
server through a listener mechanism, so the app server can do the actual pooling. JCA
provides an enhanced form of this.

This allows for a variety of implementations of the actual pooling mechanism.

Due to extensibility considerations, and potential grandiose use of
applications, the pooled connection concept might be appropriate, but is
dependent on certain java coding standards within an application and what is
done with connections. It cannot be assumed connections are pooled at a low
level simply because a method name says so.

Yes, it can as I explained.

It is possible to code a singleton, run all connection requests through such
a connection manager, and simply return the same connection object each time
it's asked for. The struts framework does not do this for you. The
application must be coded that way, validity of the connection must be
tested at a timed interval in a separate thread, and no methods can be
called anywhere to modify the connection. A good connection manager might
use three connection objects and return them round robin to callers.

This is not necessary.

Not understanding connection functionality and assuming certain
functionality exists on faith contributes to database problems.

Sorry, you're off the track here.

If my explanation was not clear enough, feel free to ask for clarification.

 

[Malte]

mean squat. A monstrosity of cut and paste has been constructed, with each
paste supposedly being what is proven and tested. The DB2 driver does not
support reuse of connection objects at all, and neither does any other
driver I know of, so there is no magic pooling of anything.

To me this sound more like bad OO practice than a conn pooling question.
In our case, ALL applications use the same code since we put it in with
our other utilities in a library.

Cut and paste is, arguably, what caused the Y2K problem.

 

[David McDivitt]

I do not agree the struts framework we have in use is doing anything behind
the scenes with connection objects. Classes have to be manually coded and
the names of those classes placed in various struts config files. Struts
merely calls the classes you make for you. Big deal. All this does is add
unnecessary complexity. Trying to read a table results in a series of
objects instantiating objects five or six layers deep. Trying to trace
what's happening is difficult or impossible. Connections are being closed at
low level. What use does this have? People call objects which call other
objects just because they think they should, because such is in vogue,
because someone saw a gimmick package, but all these do is waste time. I
agree with scalability and extensibility concepts. Sometimes objects are
used to support potential scalability, but that scalability will never
happen unless the app is used at Yahoo and gets millions of hits.

I do not see any advantage to the pooling concept nor do I agree most
pooling schemes actually do anything worthwhile.

 

[David McDivitt]

Thanks John for this point. To do pooling, everything must be geared towards
that, including the way connections are used. Just being on an app server in
itself means nothing.

 

[David McDivitt]

There is much to be gained by reusing known open connection objects, rather
than issuing single use connection objects for each request. If connection
objects are not modified by using transaction blocks, there's no reason not
to share them between threads. The word "pooling" implies this, but pooling
itself doesn't do very much. For the most part, all that happens is caching
the database user name and password.

 

[John C. Bollinger]

Thanks John for this point. To do pooling, everything must be geared towards
that, including the way connections are used.

No, you have mistaken my point. Only the means to obtain a Connection
must be geared toward supporting pooling. Application servers (and
other contexts) provide for named DataSources as one means to decouple
applications from the details of whether or not Connections are actually
pooled. For applications that use this mechanism, the pooling and
connection details can be configured (and reconfigured) without touching
Java code. The _use_ of Connection objects can be quite standardized:
use it normally until done, then close() it. Pooling happens (or
doesn't) under the control of the object that provides Connections, and
the application code doesn't need to care about it. DataSources are not
the only way to accomplish this, but they are standard.

Just being on an app server in
itself means nothing.

I think that's too strong. App servers don't _automatically_ give you
connection pooling, as I said before, but they do generally give you a
straightforward framework for setting up and using connection pooling.
Once done for one application, setting up pooling for another is
trivial. Some servers' tools may also make configuring pooling very easy.

 

[John C. Bollinger]

There is much to be gained by reusing known open connection objects, rather
than issuing single use connection objects for each request. If connection
objects are not modified by using transaction blocks, there's no reason not
to share them between threads.

This may vary from driver to driver. In any case, however, you cannot
assume that a user of the Connection will *not* use transaction blocks,
so even if that were the only consideration you could not safely share
Connections concurrently among threads.

The word "pooling" implies this, but pooling
itself doesn't do very much.

I'd say that "pooling" implies the opposite of sharing Connections
concurrently among threads. If it were safe to share Connections in
that way then there would be no need for a pool. Pooling implies need
(or at least use) for multiple interchangeable instances.

For the most part, all that happens is caching
the database user name and password.

You can't have it both ways. You earlier defined what you mean by
connection pooling, and very emphatically excluded simple credential
caches that do not reuse Connections. I think we all agree with you
that such mechanisms are not connection pools. When used appropriately
pooling does what it's advertised to do, which is reduce the number of
separate connections made to the database by using a few connections to
support multiple connection users.

So, do some applications make use of non-pooled connections? Of course.
Do other applications make use of connection pools? Absolutely. Can
junior developers to be confused about whether connection pooling is in
use or not? Yes. Is connection pooling useful? Sometimes.

Do you have a further point?

 

[Virgil Green]

I want to send an email to people in my unit containing something
similar to the following and I would appreciate comments first:



When putting frameworks together they are made with the idea of being
extensible. Because of that, big nebulous words, concepts, and
abstractions are often used. For the most part Java is plug and play.
Objects are encapsulated. When you ask for something through a method
it is given back to you and you should not care how it comes to
exist. If a platform is enhanced or extended it should make no
difference. I do not wish to dispute the utility of this basic java
premise.

I've gathered from other posts that you are using Struts framework. Correct?

When calling the method getPooledConnection, it would imply you are
getting a truly pooled or shared connection. What you are getting is
what the framework wants you to have, and that is within the scope of
present implementation. Java is very robust and it's possible to
connect to anything and everything in the universe from one
application. Certainly some of these resource connections will be
grouped together. In my investigation of existent applications we
have, grouping is only done at the resource driver level. Individual
database connections are not pooled or shared.

To what class does the getPooledConnection() method you are calling belong?

To support my assertion, if datasource.xml is viewed in the struts
framework, the driver COM.ibm.db2.jdbc.app.DB2Driver can be found.
This driver only works one way. There is no secret, hidden,
marvelous, behind the scenes functionality present in the app server,
or the DB2 database, when this driver is used. There is no
reusability of connection objects created with this driver. If the
close method is done on a connection object it cannot be used again.
It is history, toast, gone. Only a new connection can be instantiated
from the DB2 driver, which involves use of a user name and password,
again. If in code you find the close method being called on
connection objects, necessarily, those connection objects go away.
Also, on a connection object, if transaction blocks are used, such
apply to the whole connection object. If threads do somehow share a
connection and one starts a transaction block, it would apply to the
other thread as well. There is no connection within a connection. The
same goes for the autocommit property (dirty writes).

What app server are you using? I've never seen or expected a driver to be
responsible for managing a connection pool, but the supplier of the driver
will often supply a class that will manage a connection pool for you. The
data access package I use for accessing IBM AS/400 databases supplied both
the driver and a connection pool manager. All is supplied by the vendor.

Due to extensibility considerations, and potential grandiose use of
applications, the pooled connection concept might be appropriate, but
is dependent on certain java coding standards within an application
and what is done with connections. It cannot be assumed connections
are pooled at a low level simply because a method name says so.

What method (meaning full context... name the class and provider)?

It is possible to code a singleton, run all connection requests
through such a connection manager, and simply return the same
connection object each time it's asked for. The struts framework does
not do this for you. The application must be coded that way, validity
of the connection must be tested at a timed interval in a separate
thread, and no methods can be called anywhere to modify the
connection. A good connection manager might use three connection
objects and return them round robin to callers.

A bad connection manager would round-robin three connection objects. A good
one would supply them from a known pool and grow that pool as needed, up to
a configurable limit.

Not understanding connection functionality and assuming certain
functionality exists on faith contributes to database problems.

This is true, but it's not connection pools that are to blame... More like
an improper configuration.

 

[David McDivitt]

What I'm trying to do is establish some arguments to do things differently.
Just because a package says something does not mean it really does that. The
package may only be presenting an abstract idea. Some people believe
applications should be built with ultimate conceivable robustness in mind,
always, with however many unused pieces to facilitate the idea. I do not
agree with that. Just because a new gimmick infers robustness does not mean
it should be used on that basis alone. The applications we have where I am
have too many of these and code cannot be followed for maintenance.

From what I've seen I see no reason for connection pooling under any
circumstance, even if it was working. A connection manager should return one
of three or four shared connections. An additional method should be present
which returns a standalone connection for times when such might be needed
for tran blocks, etc. Anything beyond this is just more bloated java crap.

 

[Lee Fesperman]

There is much to be gained by reusing known open connection objects, rather
than issuing single use connection objects for each request. If connection
objects are not modified by using transaction blocks, there's no reason not
to share them between threads. The word "pooling" implies this, but pooling
itself doesn't do very much. For the most part, all that happens is caching
the database user name and password.

As John said, the usefulness of concurrent sharing for connection objects varies by the
driver. In addition, the Connection object does contain state information. Take a look
at the various setXXX() methods in java.sql.Connection.

Your assumptions about pooling are not very valid. Other types of resources, including
threads, are pooled in Java but rarely concurrently. Concurrent use of pooled resources
would tend to imply that all state be read-only, thus no pooling as such is not needed.
All you need to do is to keep a common reference (GC will take care of disposing of the
object when no longer needed). While this isn't absolutely true in all cases, it covers
most of the interesting ones.

Also, caching of connection properties is just one aspect and will normally include more
than just user/password. Then there is the very important feature of avoiding the
overhead of making (and ending) a connection to the db and resultant pressure on
resources on the server. In addition, some connection pooling software support
(automatic, hidden) pooling of prepared statements.

Finally, please do not top-post. It's just basic netiquette.