-
1. Re: Pooled Connections Not Releasing
cbrak Sep 10, 2001 9:40 AM (in response to cbrak)I just wanted to clarify one thing that might have been unclear in my previous paragraph. When I stated that pooled connections are not releasing, what I meant is that when a client closes the connection (conn.close()) the connections are not releasing from the database. Just wanted to be clear.
-
2. Re: Pooled Connections Not Releasing
p_d_austin Sep 10, 2001 5:21 PM (in response to cbrak)Welcome to the world of connection pooling. When you close a connection obtained from a pool you do not actually close the connection to the database all you are doing is returning the connection back to the pool so it can be reused. So in the administration for you database the connections will always be there.
The typical reason for the connection pools blocking that I have seen is that the user's application code is not calling conn.close() in the case of an exception being thrown by the sql. You must always have a finally block to close the connection in case of an error.
Connection con = null;
PreparedStatment s = null;
ResultSet rs = null;
try {
// get connection from pool and execute sql statements assigning them to the variables above
} finally {
try {
rs.close();
} catch (Exception e) {
// log the exception here
}
try {
s.close();
} catch (Exception e) {
// log the exception here
}
try {
con.close();
} catch (Exception e) {
// log the exception here
}
}
This type of pattern of code should be used all the time for any jdbc calls to make sure all the resources are cleared properly.
Hope this helps,
Paul -
3. Re: Pooled Connections Not Releasing
cbrak Sep 10, 2001 9:00 PM (in response to cbrak)oh no, no, no...ok, I'm bad at explaining the situation when I want an answer to it like right away. Obviously a connection pool opens so many connections to the database and those connections always stay open (up to the minimum count that is)...my issue is that connections are not being given back to the pool from the session bean even after a client conn.close(). This is evident when turning on blocking and watching the application come to a deadlock once the max number of connections is reached. Any ideas?
-
4. Re: Pooled Connections Not Releasing
p_d_austin Sep 11, 2001 2:38 PM (in response to cbrak)Can you post a copy of the code?
-
5. Re: Pooled Connections Not Releasing
cbrak Sep 11, 2001 6:30 PM (in response to cbrak)Sure...here is a snippet of a DAO object that has been instantiated from a session bean:
try{
InitialContext initialContext = new InitialContext();
DataSource ds = (DataSource)initialContext.lookup("java:/mySQLDB");
Connection conn = ds.getConnection("xxx","xxx");
}catch(Exception e){
cat.error("MailDAO: Not able to get connection: "+e);
}
if (conn == null || !(conn instanceof Connection))
throw new PersistenceException("JDBC connection not available");
try
{
pstmt = conn.prepareStatement(CHECKMAIL);
pstmt.setString(1,user);
rset = pstmt.executeQuery();
rset.next();
currentMessageCount = (Integer)rset.getObject("Current_Message_Count");
}
catch (SQLException e)
{
cat.error("MailDAO:"+e);
}
finally
{
if (pstmt != null)
{
try
{
pstmt.close();
rset.close();
conn.close();
}
catch (SQLException e)
{
cat.error("MailDAO: Error closing connection "+e);
}
}
}
Here also is my pool configuration in jboss.jcml:
<!-- ==================================================================== -->
<!-- JDBC -->
<!-- ==================================================================== -->
org.gjt.mm.mysql.Driver
org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
mySQLDB
jdbc:mysql://localhost/realpeople
12
10
20000
false
2000
true
20000
false
I have also tried moving around the conn.close() to the passivate and remove methods of the stateless session bean. Sometimes it works just fine, but when I load test it while blocking is turned on, the application deadlocks. If I turn blocking off, I get sporadic errors in my counsel stating that a connection could not be established. I attribute these messages to timeouts when trying to get a connection and there are non currently available. When I try the same load test with a connection pool of my own, outside of an EJB(s), it works just fine without error. If there is any other info I can provide you, please let me know. -
6. Re: Pooled Connections Not Releasing
thedug Sep 12, 2001 3:36 PM (in response to cbrak)We are seeing real wierd stuff where we have connection remain open for days even when the application server that oracle is indicating as the owner of the connection is physically turned off!
I am not sure what to make of this... -
7. Re: Pooled Connections Not Releasing
p_d_austin Sep 13, 2001 5:01 AM (in response to cbrak)So your code has just shown that you are only closing the connection if the prepared statement is created correctly and in the case of any errors in the closing of the resultset or prepared stament you are not performing the closing of the connection. Therefore the connection will not be closed if there are errors.
You should use the following structure.
try {
rs.close();
} catch (SQLException sqle) {
// log.error(sqle);
} catch (NullPointerException npe) {
}
try {
s.close();
} catch (SQLException sqle) {
// log.error(sqle);
} catch (NullPointerException npe) {
}
try {
con.close();
} catch (SQLException sqle) {
// log.error(sqle);
} catch (NullPointerException npe) {
} -
8. Re: Pooled Connections Not Releasing
neilmcc Sep 20, 2001 10:11 AM (in response to cbrak)Hi - I'm doing something very similar with mysql but am getting some weird behaviour. Where bloking=true, I'd expect the pool to expand to the MaxSize and then the client should block. This happens fine.
But when the client releases connections back to the pool (in a finally block like the one below), the pool blocks staing that it is full and awaiting a connection to be checked back in. It seems that the connections are not being released correctly back to the pool - any thoughts?Connection connection = null; PreparedStatement statement = null; try { System.out.println("Getting connection..."); connection = getConnection(); System.out.println("Preparing call..."); statement = connection.prepareStatement("insert into mailing_list(id, mailing_id, customer_id, email_address, address_active) values (null,?,?,?,?);"); statement.setString(1, mailingList); if (customer==null) { statement.setNull(2, java.sql.Types.VARCHAR); // null cust id } else { System.out.println("Customer id in new (inserted) mailing entry is : " + customer.getId()); statement.setLong(2, customer.getId()); } if (emailAddress==null) { statement.setString(3, customer.getEmailAddress()); } else { statement.setString(3, emailAddress); } statement.setString(4, "Y"); // address = active System.out.println("Running sql..."); statement.executeUpdate(); System.out.println("sql finished."); statement = ((org.jboss.pool.jdbc.PreparedStatementInPool)statement).getUnderlyingPreparedStatement(); long thisID = ((org.gjt.mm.mysql.PreparedStatement) statement).getLastInsertID(); //original ! long thisID = ((org.gjt.mm.mysql.PreparedStatement) statement).getLastInsertID(); mailingListID=thisID; System.out.println("got id [PUKKA] : " + thisID); MailingListPK mailingPK = new MailingListPK(thisID); return mailingPK; } catch (Exception e) { System.out.println("sql or naming exception in insert mailing list "); e.printStackTrace(System.err); throw new CreateException(e.getMessage()); } finally { try { // maybe not closing? System.out.print("Closing statement & connection..."); statement.close(); connection.close(); System.out.println("done!"); } catch (Exception ee) { System.out.println("close db exception"); ee.printStackTrace(System.out); } }
-
9. Re: Pooled Connections Not Releasing
p_d_austin Sep 20, 2001 11:05 AM (in response to cbrak)Always have a different try catch block around the closing of statements and the connection as if there was an error closing the statement then the connection will never be closed.
Paul -
10. Re: Pooled Connections Not Releasing
neilmcc Sep 20, 2001 12:16 PM (in response to cbrak)Sounds reasonable. In test, though, there have not been any exceptions logged - which would suggets that both the statement & the connection are being closed correctly. What I can't understand is that if the connection is released ok, why the Minerva pool does not see that connection checked in and ready for another bean to use.
-
11. Re: Pooled Connections Not Releasing
thedug Sep 21, 2001 12:59 AM (in response to cbrak)Do you have GCEnabled?
-
12. Re: Pooled Connections Not Releasing
neilmcc Sep 21, 2001 4:00 AM (in response to cbrak)Thanks for the tip.
As I understand it, the GCEnabled just sweeps up connections that have been unused for a (typically) long period of time. If i set this quite short (ie < 1 min) in conjunction with the IdleTimeout, it would be possible to force a rapid clean up of connections. But that wouldn't appear to be as instantaneous as closing a connection, the pool shrinking by 1 connection and then the next client invocation grabbing a new connection up to the max pool size.
What I don't understand is why, when checking a connection back into the pool (by closing it), that the pool doesn't shrink by one connection, thereby allowing another connection to be opened up to the MaxSize. It seems that the pool is reaching max size and stays there, even though other (concurrent) beans are releasing connections to the pool at the same time.
Hmm. Puzzled! Any thoughts very welcome! -
13. Re: Pooled Connections Not Releasing
davidjencks Sep 21, 2001 4:43 PM (in response to cbrak)Try closing the PreparedStatementInPool instead of the underlying statement. If this doesn't work, I would comment out all the code that actually does anything with the connection and make sure the pool is working properly then. Then start adding stuff back in till something breaks, you will know what is the problem.
-
14. Re: Pooled Connections Not Releasing
ronpfeifle Sep 28, 2001 5:03 PM (in response to cbrak)we're experiencing a similar problem with transactions locking up.
basically, all available connections are being consumed.
Are you saying that if prepared statements and/or result sets aren't
properly closed before closing the connection, things could lock up?
I'm pretty sure that we *are* cleaning up right -- but is that a true
statement?
Thanks
Ron