When using a pooled db connection you should always set your connection object = NULL
after closing the connection. In a connection pool, calling close() returns the
connection
back to the pool so that it can be reused. It could get reused immediately by another
request.
Here is the sequence of events you were seeing:
Request 1 running in Thread 1 gets a db connection.
Request 1 closes the db connection.
The JVM switches the running thread to Thread 2
Request 2 running in Thread 2 gets a db connection
(the same db connection just closed by Request 1)
The JVM switches the running thread back to Thread 1
Request 1 closes the db connection again in your finally block.
The JVM switches the running thread back to Thread 2
Request 2 Thread 2 tries to use the db connection but fails
because Request 1 closed it.
A better way would be as follows:
Connection con;
try {
con = datasource.getConnection();
// Do some db stuff
con.close()
con = NULL;
} finally {
if (con != NULL) {
con.close();
con = NULL;
}
}
Regards,
Glenn
Jakarta Tomcat Newsgroup (@Basebeans.com) wrote:
> Subject: Re: Commons DBCP and closing connections
> From: Matt Raible <[EMAIL PROTECTED]>
> ===
> I changed my closeConnection method (see below). It seems to work better
> (no close connection error), but I am wondering about the open connections
> to mysql. When I monitor them (show status; watch Threads_connected), there
> are 3 at first (I'm guessing from my monitor connection, JDBCRealm and
> Connection pool). It gets up to 5, how can I tell if connection pooling is
> working? Especially since my open connection says "non-pooled?"
>
>
> /** Closes a connection from the connection pool */
> public void closeConnection(Connection con) throws
> ServiceLocatorException
> {
> try {
> con.close();
> } catch (SQLException sqle) {
> logger.error("SQLException: " + sqle.getMessage());
> throw new ServiceLocatorException(sqle);
> } finally {
> //if (!con.isClosed()) {
> // try again
> try {
> if (!con.isClosed()) {
> con.close();
> }
> } catch (SQLException csqle) {
> // ignore
> }
> //}
> }
> }
>
> Thanks,
>
> Matt
>
> On 8/23/02 9:03 PM, in article
> [EMAIL PROTECTED], "Andrew Conrad"
> <[EMAIL PROTECTED]> wrote:
>
>
>>That's what I saw.
>>
>>- Andrew
>>
>>
>>>-----Original Message-----
>>>From: Peter Davison [mailto:[EMAIL PROTECTED]]
>>>Sent: Friday, August 23, 2002 10:56 PM
>>>To: Tomcat Users List
>>>Subject: Re: Commons DBCP and closing connections
>>>
>>>
>>>Correct me if I'm wrong but if the first con.close() call
>>>succeeds your code will still execute the second con.close()
>>>call in the finally block won't it?
>>>
>>>Closing the connection won't necessarily dereference the
>>>"con" variable, so it's trying to close a connection that is
>>>already closed, which would explain the exception you're getting.
>>>
>>>P.
>>>
>>>On Fri, 23 Aug 2002 19:10:09 -0700
>>>Jakarta Tomcat Newsgroup (@Basebeans.com)
>>><[EMAIL PROTECTED]> wrote:
>>>
>>>
>>>>Subject: Commons DBCP and closing connections
>>>>From: Matt Raible <[EMAIL PROTECTED]>
>>>> ===
>>>>I am trying to upgrade from using Tyrex 0.9.7 to DBCP (from
>>>
>>>Struts 1.1
>>>
>>>>b2) - and I have the following method that used to work fine:
>>>>
>>>> /** Closes a connection from the connection pool */
>>>> public void closeConnection(Connection con) throws
>>>>ServiceLocatorException
>>>> {
>>>> try {
>>>> con.close();
>>>> } catch (SQLException sqle) {
>>>> logger.error("SQLException: " + sqle.getMessage());
>>>> throw new ServiceLocatorException(sqle);
>>>> } finally {
>>>> if (con != null) {
>>>> // try again
>>>> try {
>>>> con.close();
>>>> } catch (SQLException csqle) {
>>>> // ignore
>>>> }
>>>> }
>>>> }
>>>> }
>>>>
>>>>But now it causes a "connection closed" error?? I thought when you
>>>>were using a connection pool, "closing" the connection just
>>>
>>>releases
>>>
>>>>it back to the pool. Am I doing this right??
>>>>
>>>>Matt
>>>>
>>>>
>>>>--
>>>>To unsubscribe, e-mail:
>>>
>>><mailto:tomcat-user-> [EMAIL PROTECTED]>
>>>
>>>>For
>>>
>>>additional commands,
>>>e-mail:
>>>
>>>><mailto:[EMAIL PROTECTED]>
>>>>
>>>
>>>--
>>>Peter Davison
>>>[EMAIL PROTECTED]
>>>
>>>Don't everyone thank me at once!
>>>-- Han Solo
>>>
>>>--
>>>To unsubscribe, e-mail:
>>><mailto:tomcat-user-> [EMAIL PROTECTED]>
>>>For
>>>additional commands,
>>>e-mail: <mailto:[EMAIL PROTECTED]>
>>>
>>
>>--
>>To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
>>For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>>
>
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
