Read DBCP automatically re-connect those things---reprint

http://agapple.iteye.com/blog/791943

Can be compared after another article: http://agapple.iteye.com/blog/772507

The same content, different description of the way, not the same effect.

Hi All:

Recently in doing Offerdetail optimization, replaced the database driver, from C3P0 0.9.1-dbcp 1.4, by the way to study the next DBCP automatic re-connect a set of mechanisms, also do share, we are well known.

Common issues with database links:

1. After the database restarts unexpectedly, the original database connection pool can automatically discard old useless links and establish new database links.

2. After the network abnormal interruption, the original TCP link should be able to switch automatically. For example, a switch restart in a Web drill can cause network transients

3. Distributed database middleware, such as Cobar will be timed to close the idle link unexpectedly, the client will appear half open idle links.

Think about the idea of a solution:

1. SQL heartbeat Check (active)

2. Take the link to try, found that processing failed to discard the link, mine detection of the request will fail a few (sacrifice ego, complete the spirit of my greater)

3. Set a reasonable time-out for idle links, avoid semi-open links (lazy mode, resolve semi-open links)

Let's take a look at how it is implemented in DBCP.

SQL Heartbeat Check

SQL Validate Configuration

<property name= "Testwhileidle" ><value> true </value></property>

<property name= "Testonborrow" ><value> false </value></property>

<property name= "Testonreturn" ><value> false </value></property>

<property name= "Validationquery" ><value>select sysdate from dual</value></property>

<property name= "Validationquerytimeout" ><value>1</value></property>

<property name= "Timebetweenevictionrunsmillis" ><value>30000</value></property>

<property name= "Numtestsperevictionrun" ><value>16</value></property>

Parameter description

DBCP is the use of Commons-pool as its connection pool management, Testonborrow,testonreturn, Testwhileidle is the pool is to provide a number of verification mechanisms, through the external hooks to callback DBCP the relevant database link (VA Lidationquery) checksum, DBCP related external hook class: Poolableconnectionfactory, inherited from Common-pool Poolableobjectfactory, dbcp by Genericobject Pool this entrance, for connection pooling of borrow,return processing.

Specific parameter description:

1. Testonborrow: Gu Ming, that is, in the process of borrowobject processing, to get the connection Validateobject check

2. Testonreturn: Gu Ming, is in the Returnobject to return connection Validateobject check, the personal feel that the management of database connection pool is of little significance

3. Testwhileidle: The focus of attention, genericobjectpool in the pool management, an asynchronous evict TimerTask timed thread control (by setting the parameters timebetweenevictionrunsmillis>0), Validateobject check the link in the thread pool periodically, and when the invalid link is closed, Ensureminidle is called. Establish links appropriately to ensure the minimum number of minidle connections.

4. Timebetweenevictionrunsmillis, set the evict thread time, unit MS, greater than 0 to open the evict check thread

5. Validatequery, representing the checked SQL

6. Validatequerytimeout, representative when performing the check, through statement settings, Statement.setquerytimeout (validationquerytimeout)

7. Numtestsperevictionrun, which represents the number of links per check, the recommended setting is as large as maxactive, so that every link can be checked effectively each time.

SQL Some Thoughts on heartbeat check:

1. Performance issues.

The current application of the Web site most of the bottleneck is still in the I/O this piece, most of I/O or at this level of the database, each request may call 10 Cretin SQL query, if you do not go to the transaction, a request will be repeated to get the link, if each time to get the link, For example, in Testonborrow are validateobject, performance cost is not very acceptable, you can assume that a SQL operation 0.5~1ms (generally go the network request basically this number)

2. Costs and benefits

Website Abnormal database Restart, the frequency of abnormal network disconnection is very low, generally in the database upgrade, exercise maintenance will be carried out, and generally also selected in the evening, the relatively low number of visits to the request, and generally will have personnel on duty attention, so asynchronous validateobject is acceptable, But a premise needs to ensure that the database can be automatically re-linked within a reasonable period of time.

Request Mine detection

Related configuration

DBCP self-default support, no configuration required

Principle Description

Common-pools through Borrowobject, Returnobject to complete the acquisition and release of the connection, the normal situation is a request in borrow and return is a pair, there is a loan is also.

But in preparation for returnobject, DBCP will do one thing, is to see if this object is already broken, if it is broken directly lost, directly to discard.

Code level:

1. When Poolingdatasource (implement DataSource Interface) calls Poolableconnection (DBCP connnection related pool delegate operation) in DBCP, check _ Conn.isclosed (), for DataSource if IsClosed returns True, does not call returnobject and discards the link directly.

2. _conn.isclosed () is insured from the JDK's API description: A connection is closed if the method close have been called on it or if certain fatal Errors has occurred. There are two kinds of cases, one is called the closed method, the other is the occurrence of some anomalies, said the more ambiguous.

Idle link Check

Related configuration

<property name= "Minevictableidletimemillis" ><value>18000000</value></property>

<property name= "removeabandoned" ><value>true</value></property>

<property name= "Removeabandonedtimeout" ><value>180</value></property>

Parameter description

1. Minevictableidletimemillis dbcp default is 30 points, need to open asynchronous thread evict, otherwise does not take effect. The simple principle is that, with an asynchronous thread, each time you check the timestamp of the last use of connnection, see if the timeout time setting has been exceeded.

2. removeabandoned, removeabandonedtimeout, mainly used in the presence of link tension, will scan some links not more than Removeabandonedtimeout time has not been released, will actively close the link.

Where applicable

1. The Cobar backend we use will have timed off idle links, the default idle link timeout time is 1 hours, and other Oracle, MySQL is different, so it is very important to set the timeout time for this idle link.

2. In general, there are several situations that require removeabandoned:

* Code not in finally release connection, but we all use sqlmapclienttemplate, the bottom has the link release process

* Database deadlock encountered. Previously encountered after the end of the stored procedure to do a lock table operation, resulting in the front cluster connection pool are all blocked, the subsequent business processing because not to get the link all processing failed.

Talk c3p0 Configuration

There are 3 parameters of the C3P0 called Auto-re-connect that we configured,

<prop key= "Acquireretryattempts" >30</prop>

<prop key= "Acquireretrydelay" >1000</prop>

<prop key= "Breakafteracquirefailure" >false</prop>

Personally think that is a misleading, these few configurations just get the link from the connection pool, get the failure to try a few more times, because we from the pool from the Get link will only wait for a fixed timeout time.

If you want to achieve the effect of automatic re-connection, you must c3p0 support request detection or the SQL heartbeat check function, can automatically eliminate invalid links.

Visible C3P0 Official Document Description: Http://www.mchange.com/projects/c3p0/index.html#configuring_recovery

At last:

DBCP will be the trend of our database-driven selection in the future, and finally how we choose how to re-connect automatically, which depends on our application scenario. For example, a read-only web system, a backend business system, the task system may be handled differently.

Read-only web system: can take the policy of requesting mine detection, but also the number of failed connection pool requests, failed to refresh the page a good time.

Back-Office Business system: The general business involves the write operation of the database, many data can not be re-entered, once processing failure can only rely on manual intervention processing. Consider whether you need to use a SQL heartbeat check, such as Testonborrow or Testwhileidle.

Validationquery of different databases:

Http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases

There is not a validationquery for all databases. On the different validationquery.

After few hours of Googling and testing I had collected this table:

Database Validationquery Notes

• Hsqldb -select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle -select 1 from dual
• DB2 -select 1 from sysibm.sysdummy1
• MySQL -select 1
• Microsoft SQL Server - select 1 (Tested on Sql-server 9.0, 10.5 [2008])
• PostgreSQL -select 1
• Ingres -select 1
• Derby -values 1
• H2 -select 1
• Firebird -select 1 from rdb$database

I wrote about it in my blog-validation query for various databases.

In advance there is a example of class, which return validationquery according to JDBC driver.

Or does anybody have better solution?

Read DBCP automatically re-connect those things---reprint