Mailing-List: contact commons-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Jakarta Commons Users List" <commons-user@jakarta.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----_=_NextPart_001_01C7B309.7B147087"
Subject: RE: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not
 reuse/pool the db connections
Date: Wed, 20 Jun 2007 00:06:10 -0700
Message-ID: 
 <83E753BE7B6A324ABB336245BAF1DAAC03FB4B5A@mailserver.metatv-ds.metatv.com>
Thread-Topic: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not
 reuse/pool the db connections
Thread-Index: Acey4Bm0Ps73M6DOTfa+GIZOdyDcNgAIadZ+
References: 
 <8a81b4af0706171532s672d3157i83b600bf3b10f36e@mail.gmail.com><83E753BE7B6A324ABB336245BAF1DAAC06A4E725@mailserver.metatv-ds.metatv.com>
 <8a81b4af0706191909td129c1ga42935e6a582cfac@mail.gmail.com>
From: "Seva Popov" <seva_popov@tvworks.com>
To: "Jakarta Commons Users List" <commons-user@jakarta.apache.org>

------_=_NextPart_001_01C7B309.7B147087
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Phil,
=20
First of all, let me say thank you for your time and for the most useful =
detailed responses. I've just realized that you are one of the DBCP's =
project developers and it's nice to know that you guys care about the =
project and its users.
=20
Now, I understand how the DBCP's InstanceKeyDataSource pool is =
communicating with the CPDS implementation. My previous understanding of =
the contract between a pool manager and a CPDS was wrong but I guess I =
got it right now. The provided links to the Oracle documentation proved =
to be very useful and explanatory as well.
=20
Yes, I am using an older Oracle version 9i with the ojdbc14-9i client =
driver jar. The main source of confusion I believe stems from the fact =
that in this version of Oracle the OracleDataSource and the =
OracleConnectionPoolDataSource both return the object of type =
OracleConnection when the client calls ds.getConnection(). However, in =
the former case the OracleConnection object represents a physical =
database connection but in the latter case the same OracleConnection =
object is a logical connection i.e. the temporary handle to the physical =
database connection. I guess Oracle guys realized that this was not very =
good and cleaned up their jdbc implementation in the later releases. =
Yet, it'd be interesting to decompile the OracleConnection and see how =
it's working, for example, look at its close() method.
=20
Anyway, I've tried to check the number of physical connections open in =
my test. I do not have Oracle monitor installed nor could I use the ps =
since I am using the Oracle jdbc thin driver in my configuration (I =
guess you used oci driver + oci client but that does not replicate my =
test configuration).
=20
But, concluding from the results of netstat -apn | grep 1521, I did =
verified that there are only 2 physical connections open to the database =
after I've run the test.
=20
So, the pool is working correctly and all my questions are resolved. =
Thanks again for support.
=20
--Seva

________________________________

From: Phil Steitz [mailto:phil.steitz@gmail.com]
Sent: Tue 6/19/2007 7:09 PM
To: Jakarta Commons Users List
Subject: Re: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does =
not reuse/pool the db connections


On 6/18/07, Seva Popov <seva_popov@tvworks.com> wrote:
> Hi Phil,
>
> Here is the config for the physical connections:
>
>          <Resource name=3D"jdbc/MyCPDS"
>             auth=3D"Container"
>             type=3D"oracle.jdbc.pool.OracleConnectionPoolDataSource"
>             factory=3D"oracle.jdbc.pool.OracleDataSourceFactory"
>             url=3D"jdbc:oracle:thin:@some_url:1521:some_sid"/> >
> Yes, I know about the differences between PerUserPoolDataSource and
> SharedPoolDataSource and I agree that the test for =
PerUserPoolDataSource
> was not valid as you've indicated and I will try it again.
>
Actually, looking at the example again, even with the defaults it
should only open two connections.  See comments below.

> However, I've used the same configuration for the SharedPoolDataSource
> with the same negative results.
>
> Also, in both cases when I've used ds.getConnection() I've obtained =
the
> raw Oracle connection but I've expected to get
> org.apache.tomcat.dbcp.dbcp.PoolableConnection as with the
> BasicDataSource.  Note, that I am not expecting to get
> OraclePooledConnection as you've indicated as this would be wrong as =
the
> OraclePooledConnection (that implements javax.sql.PooledConnection) is
> supposed to be used internally by the Pool Manager implementation. The
> OraclePooledConnection does not implement java.sql.Connection.  So, =
what
> the DBCP's Pool Manager should do is to wrap the =
OraclePooledConnection
> with the org.apache.tomcat.dbcp.dbcp.PoolableConnection (or some other
> wrapper that implements java.sql.Connection interface) and return the
> latter to the client.
>
> Is it not the case with DBCP?

InstanceKeyDataSource (which both PerUserPoolDataSource and
SharedPoolDataSource extend) does not wrap connection handles returned
by ConnectionPoolDataSource impls.  The pool maintains
PooledConnections obtained from the configured
ConnectionPoolDataSource and the InstanceKeyDataSource getConnection
method returns the result of getConnection applied to
PooledConnections in the pool.  The client should get back connection
handles provided by the driver.  The pool (actually the factory)
implements the ConnectionEventListener interface.

Tomcat 5.5.9 bundles (repackaged) commons dbcp 1.2.1.  You can see the
sources for this version of dbcp either by downloading the 1.2.1
source distribution here:
http://archive.apache.org/dist/jakarta/commons/dbcp/source/
or online via the svn viewer:
http://svn.apache.org/viewvc/jakarta/commons/proper/dbcp/tags/DBCP_1_2_1/=


I am by no means an Oracle expert, but using Oracle Database 10g
Express Edition Release 10.2.0.1.0 and the client driver jar it ships
with on Fedora Core 4, tomcat 5.5.9,  I think I was able to verify
correct functioning of both PerUserPoolDataSource and
SharedPoolDataSource with your example jsp.  My output for
PerUserPoolDataSource looks like this:

DataSource: =
org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource@15bdc50
Connection 1: oracle.jdbc.driver.LogicalConnection@e020c9
Connection 2: oracle.jdbc.driver.LogicalConnection@39e5b5
Connection 3: oracle.jdbc.driver.LogicalConnection@117f31e

Note the difference in types from your output.  The LogicalConnections
are handles.  Looking at the Oracle monitor and also ps, I can see
that there are in fact only 2 physical connections open to the
database (at least that what it looks like to me - as I said, I am not
an Oracle expert, I am concluding this from looking at "sessions"
identified in the web monitor and ps -aux | grep Oracle on the command
line).

What version of Oracle and Oracle drivers are you using?  From the docs =
here:
http://download-east.oracle.com/docs/cd/A87861_01/NT817EE/java.817/a83724=
/connpoc2.htm
it looks like previous versions somehow used OracleConnection
instances as handles (as your output above indicates).  Can you check
the number of physical connections open somehow?

Phil

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


------_=_NextPart_001_01C7B309.7B147087
Content-Type: text/plain; charset=us-ascii

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
------_=_NextPart_001_01C7B309.7B147087--