Search code examples
javajdbcwebserverconnection-pooling

JDBC connection validation performance using auto-commit, meta-data or table?

All,

I have been researching connection validation options on several web server technologies.

They are

  • Iplanet 7 (aka Sun One Webserver)
  • Apache Tomcat 7
  • Glassfish

All these servers provide JDBC connection validation however in their documentation I cannot find any specifics about what type of query is used for connection validation and what performance impacts they will have.

The three options are

  • auto-commit. In this mode, query statements are executed and committed as individual transactions. When auto-commit is disabled, query statements are grouped into transactions that can be terminated by either commit or roll back mechanisms.
  • meta-data. In this mode, a connection's database is able to provide meta-information describing its tables, its stored procedures, and so on. Each instance of the meta-data object will have a particular query associated with it. The meta-data object will execute that query and cache the results.
  • table. This method requires the Web server to perform a query on a user-specified table.

For auto-commit it seems fairly explanatory, however I am not sure how the other two mechanisms work.

In particular for table validation, what kind of query is used (is it just a simple SELECT * FROM TABLE) or is it even more simpler than that?

Should I create a basic table for use in this validation with 1 row and 1 column?

What is the best performing method of validation?

Bonus question Why can my Servlet not force the web server to reset all JDBC connections in the pool. It seems to me that the servlet is limited to only requesting JDBC connections but cannot directly effect the JDBC resource itself, is this for security reasons?

Solution

  • Basic idea behind connection validation is to ensure the connection held by the pool is still valid - to say, the connection is still actively connected to the database. Many times what may happen in a pool situation is - pool believes connection is active, but the database server may have dropped the connection. Sometimes devices between the web app (say, router or firewall) can also drop the connection and the pool may not know about it. Sometimes when the application uses the connection it may have errored out and the application may have returned the connection in a bad state to the pool causing problems later when some part of the application uses that connection from the pool. Therefore it is necessary to test the validity of connections in the pool. It can be done at different times:

    • When the application is acquiring a connection from the pool so that the application gets a connection in good state.
    • When the application returns the connection to the pool so that only connections in good condition are returned to the pool.
    • When the application acquires a connection from the pool as well as when it returns the connection to pool.

    Apart from that it is also a good practice to test idle connections in the pool periodically so that pool does not continue to hold bad connections.

    A good connection pool will provide you all of the above ways of testing the connections.

    Now the question of how to test the connection. You can do anything to ensure the connection is still connected to the database. Typically it means running a query. Query need not hit any real table or any other object in the database. For example in case of oracle it may be selecting something from dual:

    SELECT SYSDATE FROM DUAL;

    In case of postgresql it can be:

    SELECT 1+1;

    Any query that hits the database and gets something from the database is good enough. Avoid hitting a real table or something like that to keep the connection testing overhead minimum.

    About bonus question: Server administrators want to have control on the pool for various reasons (for example - to restrict number of connections an application can consume). Therefore pool configuration has to decide how many connections to acquire, how many to drop from the pool and when to do that. Therefore application should not be allowed to fiddle with the pool. Application should only use the pool but not tamper the pool.