Java two tier applications running outside a java web server or java web container using SQL Server as the database may show symptoms of connection leak . This applies to new applications as well as the ones migrated from Oracle/DB2 or other databases.
If the command netstat –anob is run from command prompt there will be a lot of connection in the TCP TIME_WAIT state. Which might make the developer think that he is facing a connection leak.
As we know, It’s a sign of new connections being created every time application opens a connection(and the connections are not reused). What may be surprising is that the same application implements connection pooling and works fine when run under the context of a Java web server.
Actually The Microsoft SQL Server 2005 JDBC Driver provides support for J2EE connection pooling. The JDBC driver implements the JDBC 3.0 required interfaces to enable the driver to participate in any connection-pooling implementation that is provided by middleware. The JDBC driver will participate in pooled connections in environments mentioned in http://msdn.microsoft.com/en-us/library/ms378484(SQL.90).aspx ,
Microsoft SQL Server JDBC driver does not implement the connection pooling algorithm in its own code, it only expose the required interfaces which when implemented make the driver hook onto the connection pooling mechanism of a third party application server.
The interfaces are that are used to hook onto the connection pooling mechanism of a third party application server are-:
com.microsoft.sqlserver.jdbc.SQLServerXADataSource
OR
com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
For example the JDBC driver does not currently call the sp_reset_connection stored procedure when it returns the connection to the pool. Instead, the driver relies on third-party Java Application Servers to return the connections back to their original states.
If the JDBC application is a web application and is hosted on a J2EE application servers( that provide JDBC 3.0 API specifications connection pooling), The web server takes care of the connection pooling and not the web application itself. But if it’s an application running outside the context of a web server it will not pool connection even if the connection pooling classes are implemented.
To give you some indepth knowledge- the SQLServerXADataSource class contains a method getPooledConnection () which is inherited from the base class SQLServerConnectionPoolDataSource. This is the base class that is typically used in Java Application Server(that support built-in connection pooling) environments. It actually looks for a ConnectionPoolDataSource object for the physical connections which is provided by the J2EE application servers( that provide JDBC 3.0 API specifications connection pooling)
But there are few open source programs that allow using connection pooling even outside the Application Server (for example Apache DBCP)
In case of JDBC driver from Oracle there is a slightly different mechanism for maintaining connect ion pool(which it implements through the oracle.jdbc.pool APIs ). This is also sometimes called as a two tier connection pooling because this driver provides both the implementation of DataSource which is visible to the client and the underlying ConnectionPoolDataSource implementation.
I haven’t investigated the DB2 JDBC driver but should be the on similar lines.
SQL JDBC from Microsoft doesn’t provide a two tire connection pooling as it’s not mandated by the SUN JDBC 3.0 specifications.
Connection Pooling in a Three-tier Environment (As Per SUN JDBC 3.0 Specification)
======================================================================
The following sequence of steps outlines what happens when a JDBC client requests a connection from a DataSource object that implements connection pooling:
1. The client calls DataSource.getConnection.
2.
The application server providing the DataSource implementation looks in its connection pool to see if there is a suitable PooledConnection object— a physical database connection—available. Determining the suitability of a given PooledConnection object may include matching the client’s user authentication information or application type as well as using other implementation-specific criteria. The lookup method and other methods associated with managing the connection pool are specific to the application server.
3. If there are no suitable PooledConnection objects available, the application server calls the ConnectionPoolDataSource.getPooledConnection method to get a new physical connection. The JDBC driver implementing ConnectionPoolDataSource creates a new PooledConnection object and returns it to the application server. Chapter 11 Connection Pooling 72 Regardless of whether the PooledConnection was retrieved from the pool or was newly created, the application server does some internal bookkeeping to indicate that the physical connection is now in use.
4. The application server calls the method PooledConnection.getConnection to get a logical Connection object. This logical Connection object is actually a “handle” to a physical PooledConnection object, and it is this handle that is returned by the DataSource.getConnection method when connection pooling is in effect.
5. The application server registers itself as a ConnectionEventListener by calling the method PooledConnection.addConnectionEventListener. This is done so that the application server will be notified when the physical connection is available for reuse.
6.
The logical Connection object is returned to the JDBC client, which uses the same Connection API as in the basic DataSource case. Note that the underlying physical connection cannot be reused until the client calls the method Connection.close.
So we comply with SUN specifications demand for a JDBC 3.0 driver for a Three-Tier Environment.
It can be downloaded from https://cds.sun.com/is-bin/INTERSHOP.enfinity/WFS/CDS-CDS_Developer-Site/en_US/-/USD/ViewProduc...
So when we run an application outside a Java Web Server it will open a new connection every time the application asks for one. Now when the application calls the Close() method on the connection object the connection is closed and it releases the handle it have on the physical TCP connection. But by nature the TCP connection will be in a Time_Wait state for 4 minutes before it can be reused.
Here are the avenues that are at our disposal.
a) If the application is not heavily used and there is no connection related error as such. Time_Wait shouldn’t be a concern as the physical connection will be closed after four minutes and returned to be reused by the system.
b) Run the code under the context of a Java Web Server that provides JDBC 3.0 API spec connection pooling.
c) Use it outside a Web Server and decrease the Time_Wait delay to 30 seconds and increase the number of ports. This way the application will have access to more ports and they can be reused faster. You can get more information on MaxUserPort and TcpTimedWaitDelay on the article http://support.microsoft.com/kb/328476
d) Use a third party program that implements connection pooling algorithm like Apache DBCP when the application is not being run under an application/web server environment.
Apache DBCP: http://commons.apache.org/dbcp/
Author : Angshuman(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Bindesh(MSFT) , SQL Developer Engineer , Microsoft