Date: 2004-09-27T12:59:46
Editor: DavidTonhofer <[EMAIL PROTECTED]>
Wiki: Jakarta Commons Wiki
Page: DBCP
URL: http://wiki.apache.org/jakarta-commons/DBCP
Simplified text, added a comment about Connector/J Connections and DBCP
Change Log:
------------------------------------------------------------------------------
@@ -113,16 +113,15 @@
Before a new Connection is obtained from a pool or before an existing one is returned
to a pool it may be ''validated'': a
check is run to see whether the Connection has become stale - for example because the
database unilaterally decided to close the underlying socket.
-To validate the Connection, a ''validationQuery'' (e.g. 'SELECT 1') is passed to the
constructor of org.apache.dbcp.!PoolableConnectionFactory.
+To validate the Connection, a ''validationQuery'' (e.g. 'SELECT 1') is passed to the
constructor of ''org.apache.dbcp.!PoolableConnectionFactory''.
It is used in the following way:
-org.apache.dbcp.!PoolableConnectionFactory implements the interface method
-org.apache.commons.pool.!PoolableObjectFactory.validateObject() whose description is:
{{{
+According to the pooling implementation that underlies DBCP, objects in a pool are
validated through a call to the interface method
''org.apache.commons.pool.!PoolableObjectFactory.validateObject()''. Its description
is: {{{
Ensures that the instance is safe to be returned by the pool. Returns false if this
object should be destroyed.
}}}
-Let's make clear what this means by taking a peek at the implementation in
!PoolableConnectionFactory: {{{
+''org.apache.dbcp.!PoolableConnectionFactory'' implements that interface method: {{{
public boolean validateObject(Object obj) {
if(obj instanceof Connection) {
try {
@@ -137,10 +136,9 @@
}
}}}
-Note the call to !PoolableConnectionFactory.validateConnection().
+Note the call to ''!PoolableConnectionFactory.validateConnection()''.
-!PoolableConnectionFactory.validateConnection() tries to run the ''validationQuery''
passed in the constructor. If this
-fails, it throws an SQLException and validateObject() will return false.
+''!PoolableConnectionFactory.validateConnection()'' tries to run the
''validationQuery'' passed in the constructor. If this fails, it throws an
''SQLException'' and ''validateObject()'' will return ''false''.
'''Q: When are Connections validated? In other words, when you have a
!PoolableConnectionFactory, when is its validateObject() method called on a
Connection?'''
@@ -150,43 +148,59 @@
'''''Case 1: Your Pool is a org.apache.commons.pool.!StackObjectPool'''''
Connections are '''not''' validated when you borrow them from the pool. They
-are only validated - unconditionally - in returnObject(), which is
-called by addObject(). In other words, they are only validated if they
-are put into the pool, either immediately after creation or when return them to the
pool by a call to Connection.close().
+are only validated - unconditionally - in ''returnObject()'', which is
+called by ''addObject()''. In other words, they are only validated if they
+are put into the pool, either immediately after creation or when return them to the
pool by a call to ''Connection.close()''.
The consequence of this is that you will get Exceptions if you
borrow a stale Connection because immediately after the Object has been
-pop()-ed from the stack, the factory's activateObject() is called on
-the Connection and the first thing it does is: set the autocommit default
+pop()-ed from the stack, the factory's ''activateObject()'' is called on
+the Connection and the first thing it does is set the autocommit default
value. This will throw on a stale Connection.
'''''Case 2: Your Pool is a org.apache.commons.pool.!GenericObjectPool'''''
-Connections are validated in the following three !GenericObjectPool methods:
-evict(), borrowObject() and addObjectToPool(). Thus, !GenericObjectPool gives
+Connections are validated in the following three ''!GenericObjectPool'' methods:
+''evict()'', ''borrowObject()'' and ''addObjectToPool()''. Thus,
''!GenericObjectPool'' gives
you the possibility to also validate the Connections at 'borrow' time.
''!GenericObjectPool.borrowObject()''
- Only calls validateObject() if 'testOnBorrow' has been set. This parameter
+ Only calls ''validateObject()'' if 'testOnBorrow' has been set. This parameter
is passed in the pool's constructor.
- If validation fails the Connection has not been newly created, Connection is
- discarded and a new one is created in its place.
+ If validation fails and the Connection has not been newly created, the
Connection is
+ discarded and a new one is created in its place. However, if the Connections has
+ just been newly created, the Exception is left up the stack.
Note that if the database goes away, you will see Exceptions coming out of
- borrowObject() as newly created Connections will fail their validation.
+ ''borrowObject()'' as newly created Connections will fail their validation.
''!GenericObjectPool.evict()''
- This method is called by the 'Evictor' which clears out idle Objects in the
- pool. It unconditionally runs validateObject() on a pooled Object. If that method
+ This method is called by the ''Evictor'' which clears out idle Objects in the
+ pool. It unconditionally runs ''validateObject()'' on a pooled Object. If that
method
returns false, the pooled Object is removed.
''!GenericObjectPool.addObjectToPool()''
- Only calls validateObject() if 'testOnReturn' has been set. This parameter
+ Only calls ''validateObject()'' if 'testOnReturn' has been set. This parameter
is passed in the pool's constructor. If the (returned) Object fails validation,
it is immediately destroyed.
+'''Q: What if the pooled Connections are com.mysql.jdbc.Connection instances'''
+This works perfectly well if you are using a ''!GenericObjectPool'' that has
'testOnBorrow' set.
+Connections will be validated at borrow time. Any stale ones will be discarded, and
new
+ones created in their place. The MySQL Connector/J Connection allows to set the
parameter 'autoReconnect',
+but it is useless in this case: 'autoReconnect' just means that you will get an
Exception
+when you run a query on a stale Connection, but that you can run the query ''again''
and the
+Connection will try to reconnect on this second try. As DBCP does not do second tries
on
+the same Connection instance, the 'autoReconnect' case will not arise. Retrying twice
before
+giving up is for example what a Tomcat 4 JDBCRealm does. (The above is not clear from
the
+MySQL documentation and the behaviour one would intuitively give to 'autoReconnect' is
+misleading and so this is a potential headache.)
+There is a 'autoReconnectForPools' starting from MySQL-Connector-J which I haven't
looked
+into yet.
+See [http://dev.mysql.com/doc/connector/j/en/index.html#id2425656 Connector-J
Connection Properties]
+for additional details, and also the source for
''com.mysql.jdbc.Connection.execSQL()''.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
