Tomcat connections not returned?

[siberian]

I'm using Tomcat connection pooling. I've abstracted the get db and release db functions into an object that looks like this :

import java.sql.*; 
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class DBManager {
	
	static public Connection getDB(){
		Connection conn = null;
		try {
			Context initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");
			DataSource ds = (DataSource)envCtx.lookup("jdbc/TEST");
			conn = ds.getConnection();
		} catch (SQLException e){
			System.out.println("<HR>SQL Problem<HR>");
			System.out.println("<HR>"+e.getMessage()+"<HR>");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		System.out.println("DB CREATED");
		return conn;
	}

	static public void releaseDB(Connection conn){
		try{ 
			conn.close();
			System.out.println("DB RELEASED");
		} catch (SQLException e){
			System.out.println("releasingDB"+e.getMessage());
		}
	}
	
}

This works fine but my problem is that many of my classes get and release database handles using this class. When the class using the db handle releases it tomcat appears to keep it open until the request finishes.

So as an example, if I set my max connections to 3 I can open up to 3 database handles in my assorted classes. Even if I open/close them in sequence I can open no more then maxconnections.

Using Firebird 1.5b6 and Tomcat 4.1 with Struts.

Any ideas? Is this normal Tomcat behavior? I am under the impression that conn.close() means 'return it to the pool immediately'. Are there some better connection pooling technologies available?

Thanks!

 

[vulcand4]

The whole idea behind connection pooling is that you have a bunch of open connections available to you. And they stay open until you explicitly close them or until tomcat is stopped.

It's kind of like a secretarial typing pool. You have a bunch of typists sitting there doing nothing until someone gives them an assignment. They do their task and when they are done they are returned to the typing pool for someone else to use.

Openning a connection is a relatively expensive transaction, so openning a "pool" of connections right away and leaving them open for your programs to use is much more efficient.

The close() method doesn't actually close the database connection, instead what it does is return the connection to the pool in order to make it available for another use.

it's usually best for any given transaction to acquire a connection from the pool and hang onto it until it is finished processing. As long as your max connections is set high enough, you can have manny concurrent transactions being processed at the same time - all using a different connection from the pool.

If you want to actually open a new connection for each transaction and then close it (lose connection to the database) then you shouldn't be using a connection pool.

 

[sedj]

siberian :

are you saying thst in a JSP/servlet, you do this :

some processing
open a db conn (from conn pool)
get data
close conn (return to pool)
do some more processing
(and now only the db conn is returned to the pool)

This is odd behaviour (knowing my *unimpressed* level with tomcat conn pooling though I wouldn't be surprised ...)

 

[siberian]

Poly guy - I understand connection pooling, thanks (Running cumulative mod_perl based sites accounting for tens of millions of hits a month currently). Just seeing some wierd results with my tomcat stuff.

sedj: That is what occurs. It seems really stupid.

I also discovered that with tomcat and firebird some of my preparedstatements have started to fail. Fixing those seems to have helped resolve some problems but I still have the core issue of only being able to open/close a handle 'maxtimes' within a single request cycle.

Anyone have any ideas on that? When I close the handle should immediately be made available to the pool again and they are not.

Anyone have any good 3rd party pooling libs to share?

Maybe its firebird, I know i am using the standard jbird jdbc libs. There is a pooled jar file that comes with firebird and this FBWrappedDataSource class that I can not figure out how to use..



John