Recently, I noticed that my PostGreSQL / Hibernate / C3P0 / JBoss / EJB framework hangs on to connections even after work is complete. Further more, when using C3P0, after cleanup (set to a small time interval) , the connection count still remains above the minimum pool size. Over time, the pools seem to grow.
.
Anyway, to simplify the scope of my question, ..
I've whittled things down as much as possible to merely the PostGreSQL /Hibernate / DAO portion i.e. disabled C3P0 and working outside the app server.
Even after completing a simple database operation and closing the session, .. I still see the connection hanging around as idle indefinitely. Any suggestions, fixes etc ?
For starters, is there a way to dump current connections / sessions open on the java side? I already know how to do this from the db side.
Here's a dump of the postgresql stats
Code:
postgres=> select datname, count(datname) from pg_stat_activity group by datname;
datname | count
---------------+-------
postgres | 1
admindb | 1
case1db | 1 ===========> this hangs around even after I close the session
(3 rows)
postgres=> select datname, current_query, waiting from pg_stat_activity ;
datname | current_query | waiting
---------------+-----------------------------------------------------------------+---------
postgres | select datname, current_query, waiting from pg_stat_activity ; | f
admindb | <IDLE> | f
case1db | <IDLE> | f
(3 rows)
(3 rows)
Code:
@Test
public void testDAO() throws Exception {
DataBaseSession dbSession = DataBaseSessionFactory.GetCaseDatabaseInstance(userSession.getCaseId());
Session sess = (Session) dbSession.getSession();
SessionFactory sf = sess.getSessionFactory();
System.err.println("start: " + sf.getStatistics());
AttributeDataAccessFactory.GetInstance().getAttribute(dbSession, 5); //some db related work
System.err.println("working: " + sf.getStatistics());
sess.connection().close(); //i don't normally use this, but just trying out stuff here
dbSession.closeSession(false);
Thread.sleep(5000);
System.err.println("end: " + sf.getStatistics());
System.err.println("enter rip van winkle mode");
Thread.sleep(1000000000);
}
and the relevant configuration code
Code:
public HibernateSessionFactoryBuilder() {
config = new AnnotationConfiguration();
config.setProperty("hibernate.connection.driver_class", "org.postgresql.Driver");
config.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
config.setProperty("hibernate.connection.shutdown", "true");
config.setProperty("hibernate.transaction.flush_before_completion", "true");
config.setProperty("hibernate.current_session_context_class", "org.hibernate.context.JTASessionContext");
config.setProperty("hibernate.transaction.flush_before_completion", "true");
config.setProperty("hibernate.jdbc.batch_size", "50");
//debug
config.setProperty("hibernate.show_sql", "false");
config.setProperty("hibernate.use_sql_comments", "false");
config.setProperty("hibernate.generate_statistics", "true");
}
resulting in
Code:
start: Statistics[start time=1274396118250,sessions opened=1,sessions closed=0,transactions=0,successful transactions=0,optimistic lock failures=0,flushes=0,connections obtained=0,statements prepared=0,statements closed=0,second level cache puts=0,second level cache hits=0,second level cache misses=0,entities loaded=0,entities updated=0,entities inserted=0,entities deleted=0,entities fetched=0,collections loaded=0,collections updated=0,collections removed=0,collections recreated=0,collections fetched=0,queries executed to database=0,query cache puts=0,query cache hits=0,query cache misses=0,max query time=0]
working: Statistics[start time=1274396118250,sessions opened=1,sessions closed=0,transactions=0,successful transactions=0,optimistic lock failures=0,flushes=0,connections obtained=1,statements prepared=1,statements closed=1,second level cache puts=0,second level cache hits=0,second level cache misses=0,entities loaded=3,entities updated=0,entities inserted=0,entities deleted=0,entities fetched=0,collections loaded=1,collections updated=0,collections removed=0,collections recreated=0,collections fetched=0,queries executed to database=1,query cache puts=0,query cache hits=0,query cache misses=0,max query time=16]
end: Statistics[start time=1274396118250,sessions opened=1,sessions closed=1,transactions=0,successful transactions=0,optimistic lock failures=0,flushes=0,connections obtained=1,statements prepared=1,statements closed=1,second level cache puts=0,second level cache hits=0,second level cache misses=0,entities loaded=3,entities updated=0,entities inserted=0,entities deleted=0,entities fetched=0,collections loaded=1,collections updated=0,collections removed=0,collections recreated=0,collections fetched=0,queries executed to database=1,query cache puts=0,query cache hits=0,query cache misses=0,max query time=16]
enter rip van winkle mode
Login
FAQ
Search
