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]