Java JDBC connections and Oracle

Question

I have a scenario and the question follows

Application server has two connections pools to DB. A and B

A points to -> DatabaseA -> has 128 connections

A has Stored Procedures which access tables residing in DatabaseB over the DB link

B points to -> DatabaseB -> has 36 connections

Now lets say that Java code calls Stored Proc in DatabaseA by using connection pool A. This stored proc is getting data over the DB link from DatabaseB

Question:

Based on this scenario if we get connection closed errors on the front end. Is it viable to say that even though java is calling the SP (in DatabaseA) from pool A (128) but since the SP is bringing data from DatabaseB it has less amount of connections (36).

Basically I want to know when the data is brought over the DB link like this...does it take away from 36 connections assigned to pool B pointint to DatabaseB?

Exact Exception Exact exception I get is: --- Cause: java.sql.SQLException: Closed Connection

Some Stack trace:

Caused by: java.sql.SQLException: Closed Connection at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:614) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:588) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118) at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:268) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:193) at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:219) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:266)

Also, I am using iBatis ...so don't have try..catch..finally blocks

Answer 1

This exception indicates a resource leak, i.e. the JDBC code is not properly closing connections in the finally block (to ensure that it's closed even in case of an exception) or the connection is been shared among multiple threads. If two threads share the same connection from the pool and one thread closes it, then this exception will occur when the other thread uses the connection.

The JDBC code should be written so that connections (and statements and resultsets) are acquired and closed (in reversed order) in the very same method block. E.g.

Connection connection = null;
// ...
try {
     connection = database.getConnection();
     // ...
} finally {
     // ...
     if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}

Another possible cause is that the pool is holding connections too long idle and not testing/verifying them before releasing. This is configureable in a decent connection pool. Consult its documentation.

Answer 2

"Basically I want to know when the data is brought over the DB link like this...does it take away from 36 connections assigned to pool B pointint to DatabaseB?"

No. The database server will make a distinct connection to the other database server irrespective of any connection pool.

I have to suffer a firewall that cuts of connections after a period of inactivity so I see this error quite a lot. Look into dbms_session.close_database_link, since the database link connection would generally remain for the duration of the session (and since you have a connection pool, that session probably sits around for a very long time).

Answer 3

The stored procedure is running in the database; when it makes the connection to the other database it makes a direct connection and doesn't go through the app server's pool. In fact, it could make a connection to any database that is linked to A, regardless whether or not there's a connection pool to that database maintained by the app server.