> Suppose I get a connection from a connection pool, and during my use of
> the connection it goes bad (say, the socket it's using to talk to the
> database breaks). When I return that connection to the connection pool,
> will the pool realize it's bad and not reuse it?
No, not usually. In order to do this, the pool would have to be intercepting
and interpreting every possible exception from every call you make, and
given the number of drivers and the wide possibilities of exceptions based
on when the socket could fail during any sort of driver processing, and
the performance implications, no general-purpose pool will do this. However,
a good pool should be configurable to verify a connection is good during
the reserve process, and to replace it if necessary (and posible) at that time.
> Similarly, what if I return a connection with an open transaction? Will
> the pool roll back or commit the transaction before giving it out to
> someone else?
A good pool implementation will do everything it must do to ensure that
the next user gets a connection in the default connection state defined
by JDBC. This would include autoCommit(true) which implies that any hanging
transactional content was dealt with.
> I realize that the answer depends on the connection pool
> implementation, I'm just looking for what I can generally expect with a
> modern commercial implementation (say, JBoss and Oracle using Oracle's
> JDBC drivers). Thanks.
>
> Frank
I have no knowledge of JBoss's pooling, but I can talk at length about
the highly sophisticated pooling available in BEA's WebLogic product,
which completely covers all your given cases, and does much more,
including autogenerating any driver-specific Interfaces to provide
transparent access to any vendor-specific extensions/functionality
offered by the vendor's Interfaces. Beware of any pooling system that
ever gives you direct access to a driver object by default, because
poor or malicious code can use the 'naked' reference to the object to
obtain an independent keepable reference to a pooled connection, and
use it after 'returning the connection to the pool', and possibly
corrupt/interfere with a subsequent pool connection user's work.
Joe Weinstein at BEA