pooled connection myth

[David McDivitt]

Subject: Re: pooled connection myth

Date: Fri, 18 Mar 2005 13:42:44 -0500

Do not confuse a Driver object (which acts as a factory for Connections)
with a whole JDBC driver, which encompasses at least one implementation
of Driver, Connection, Statement, ResultSet, and several other
interfaces, along with supporting classes. Once a Driver has created a
Connection, it need not have anything to do with communication via that
Connection, so a Driver object's thread safety is unrelated to the
thread safety of the Connections it hands out. If you want to talk
about thread safety of the whole driver (lowercase 'd'), however, then
of course you have to include the Connection implementation(s) in the
discussion.

A key difference here between a Driver and a Connection is that Drivers
are stateless, whereas Connections are explicitly stateful. (The
Connection interface defines methods for manipulation of persistent
state.) Connections' statefulness is what makes them unsuitable for
concurrent use by multiple threads, even if they internally serialize
individual operations. This is much the same reason that the
synchronized collections (Vector, Hashtable,
Collections.synchronizedXXX(foo)) are not safe for concurrent use: the
synchronization / serialization ensures that the objects' internal state
remains consistent, but that's not sufficient to shield one thread from
the effects of other threads' manipulation of the objects.

If you know what you can and cannot safely do, then yes, you can write
code that shares Connections among multiple threads and never have a
problem. There may or may not be performance consequences relative to
using a connection pool (in either direction, but probably not too
significant). The fact that you must know and abide by nontrivial
restrictions on the use of the shared Connections makes that approach
anything but transparent to Connection users, however, and the fact that
the approach provides fertile ground for concurrency bugs should strike
terror into your heart.

Alternatively, your JDBC driver likely comes with a connection pool
implementation that you can use out of the box. If it doesn't then you
can still get one from a third party and thereby not have to maintain
your own connection manager. Users of the Connections provided by such
a pool do not need to worry about any restrictions on their normal use
of the connections, and there are no special concurrency concerns
involved. A connection pool satisfies exactly the same performance
considerations that you shared connection mechanism does, and moreover
can provide resource management hooks that the shared connection
mechanism does not provide. Tell me again, then, what were the relative
advantages of shared connections?

You seem to be very determined to believe that your shared connection
plan is good, so I suspect that none of this has persuaded you. You are
free to believe whatever you want to believe on the topic, and I am
finished trying to tell you otherwise.

To repeat what I said in another post just sent, I appreciate the time you,
John, Lee, and Chris took to answer my questions.

 

[David McDivitt]

Subject: Re: pooled connection myth
Date: Fri, 18 Mar 2005 19:34:24 GMT

So, who wrote baseDAO? The code above is the getPooledConnection() method?
This is homebrewed code? This is what you're complaining about when you say
that pooling is a myth? There's no pooling going on there unless it has been
hidden in db.getConnection(). Why aren't you beating up on the author of
this code rather than decrying the worth of connection pools? That's like me
putting marbles in a gumball machine, you buying a "gumball" and then you
claiming that gumballs are inedible.



I'm not much surprised if the code above is passing for "pooling" code in
your shop.

The BaseDAO class was already there when I came here. I think someone copied
it from a book. I appreciate your comments very much as well as others. I
plan on doing a good connection pool in future but will use connection
sharing for immediate needs. I have plenty of ammunition now to debate this
subject with others and do things better.

 

[Lee Fesperman]

Sorry about the last. Regulars here know I can get a little hot-headed. I'm calmer now
;^)

I know you read it, but Alin makes some really good comments about the job done by
pooling software ... things I ran by with my quick assertion: "also doing necessary
cleanup". If the pooling software does good tracking, the elements of this cleanup can
be skipped incurring no extra penalty.

I will read that thread. The title sounds good. I will also get the code at
SourceForge and look at it.

Good for you. The thread only partially covers it. Earlier, I noted that you were
probably using auto-commit. On many systems, this involves beginning and committing a
(readonly) transaction for each operation. This overhead is also serialized.

With round robin connection sharing, if the previous user of a connection is
done with it by the time it's handed out again, that ends up being the same
as single ownership. Serialization would be a rare fallback.

This is also a 'reverse' benefit for standard pooling. One potential overhead for
pooling is that it may entail extra open connections, pressuring resources on the client
and the server. If the use is scattered like this, it only needs to use a single open
connection.

Another point about connections not discussed is whether to instantiate new
connection objects from the same driver object. If it is not inefficient to
multi-thread a driver, neither is it inefficient to multi-thread a
connection. If serialization is done that would occur at the driver level
behind the connection. If it is said a connection should not be
multi-threaded because that can't be trusted, then neither can the driver,
and a new driver object should be instantiated for each connection as well.

It wasn't mentioned because it is generally not an issue. The driver shouldn't really
need to do serialization. Connection creation can be fully independent. An optimized
driver might do some sharing of physical connections to the server, but it definitely
should do this properly, giving a net benefit. None of this should be a worry for the
DB2 driver, which I assume is written well enough.

If you come up with additional concerns, please voice them now or later.

I can see the advantage of pooling if stuff is really being pooled, which
means connections stay open. I will see about putting together a class to do
that either by writing one or using open source already present. In the
meantime, and until I get that tested, I will use a very simple connection
sharing class where callers do not close the connection.

Do what you must, but well-written pooling software will be all positive and more
performant than the sharing solution, even in your circumstances. It also will reduce
the possibility of errors concomitant with the sharing approach.

Good luck in your efforts!