Re: pooled connection myth

From: Lee Fesperman (firstsql_at_ix.netcom.com)
Date: 03/17/05 

Date: Thu, 17 Mar 2005 19:58:04 GMT

David McDivitt wrote:
>
> 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. 

-- 
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)