Yes, it is important to close your connections when you're done with
them, and this is considered in each of our methods.
The problem we are seeing with the hung connections, however, occurs
before any SQLException has been thrown, anywhere in the servlet
engine. In fact, even if the very first connection attempted at startup
is made to a server which is unavailable, every other connection will
wait until that first connection times out (and then throws its
SQLException).
Thank you for your consideration of the problem.
-Paul
On Monday, July 15, 2002, at 10:25 PM, Jacob Kjome wrote:
> BTW, in the code below, I forgot to create the connection at the
> beginning of the try statment. Just wanted to note that you will need
> to do that.
>
> conn = openConn(); //openConn() grabs the connection from a pool or
> creates a brand new connection
>
> Just insert that and whatever you need to do to open your connection at
> the beginning of the try statement below.
>
> jake
>
>
> At 12:00 AM 7/16/2002 -0500, you wrote:
>> HI Paul,
>>
>> You simply need to make sure that you've let go of your resources such
>> as connections, resultsets, etc....
>>
>> Here is some sample code for that...
>>
>> Connection conn = null;
>> PreparedStatement pstmt = null;
>> ResultSet rs = null;
>>
>> String query = "SELECT * FROM mytable";
>>
>> try {
>> pstmt = conn.prepareStatement(query);
>> rs = pstmt.executeQuery();
>>
>> //do something with the ResultSet...
>>
>> rs.close();
>> pstmt.close();
>> conn.close();
>> }
>> catch(SQLException e) {
>> System.out.println(e.getMessage());
>> throw e;
>> }
>> finally {
>> if (rs != null) try { rs.close(); } catch(Exception e2) {}
>> if (pstmt != null) try { pstmt.close(); } catch(Exception e3) {}
>> if (conn != null) try { conn.close(); } catch(Exception e4) {}
>> }
>>
>>
>> The key here is that if a SQLException is raised in the try statement,
>> then the ResultSet, PreparedStatement, and Connection will not be
>> successfully closed. In order to guarantee that they get closed, you
>> need to have the finally statement which is guaranteed to run no
>> matter what. Try that and I bet your connections won't get locked up
>> until timeout.
>>
>> Jake
>>
>>
>> At 02:27 PM 7/15/2002 -0700, you wrote:
>>> Sorry it's been a week since you sent this message... Too much
>>> traffic here for me to keep up. This problem is the only reason I'm
>>> subscribed to this list at all. I normally only follow -dev (and if
>>> I could point to a specific problem in Tomcat I'd ask there instead).
>>>
>>> I have this problem with all JDBC accesses from Tomcat. At first I
>>> was using a MySQL database call on every page, and found that if I
>>> accessed a second MySQL server which became unavailable, every call
>>> to the first one would be held up until it timed out, even in other
>>> contexts. Then I discovered that we had the same problem when
>>> accessing a MS-SQL server.
>>> If the MS-SQL server was unavailable, every other page on the site
>>> (each of them containing the call to MySQL) would wait until the
>>> MS-SQL connection timed out. Finally, we incorporated an Oracle
>>> connection with the same results. Any JDBC call to a database which
>>> is unavailable holds up all other connections on the servlet engine.
>>>
>>> This is an embarrassing threading issue.
>>>
>>> Although we asked for help on this list (and received some
>>> well-thought-out responses from Bill Barker) we were unable to
>>> resolve the problem.
>>> Our current hack for this is:
>>>
>>> 1) DriverManager.setLoginTimeout(CONNECTION_TIMEOUT);
>>> (The default timeout is extraordinarily long and for us a few seconds
>>> will do for any one of the databases)
>>>
>>> 2) A connection wrapper, so that instead of calling
>>> DriverManager.getConnection(connectionURL,ID,Pass) we call
>>> DBConnWrapper.getConnection(connectionURL,ID,Pass) and the wrapper
>>> remembers when a connection has failed and on each request for a
>>> connection does
>>> if(!databases.containsKey(DB) || (new Date()).getTime() -
>>> ((Date)(databases.get(DB))).getTime() > RETRY_INTERVAL)
>>>
>>> If you come up with a better answer, let me know. Of course, it's
>>> possible that you have a different problem than we do, but it sounds
>>> similar.
>>>
>>> Paul
>>> Unix System Admin/Webmaster
>>> Seattle Public Schools
>>>
>>> For Reference:
>>>
>>> On Monday, July 8, 2002, at 11:05 AM, Bill D wrote:
>>>> Let me first set up my situation. On my server, I
>>>> have two webapps running, webapp A and webapp B.
>>>> Webapp A uses JDBC Thin driver to contact an Oracle
>>>> database at remote location 1. Webapp B uses JDBC
>>>> Thin driver to contact an Oracle database at remote
>>>> location 2.
>>>>
>>>> If the internet connection at remote location 1 goes
>>>> down, the attempt to connect to that database has the
>>>> expected result of a connection to that IP in the
>>>> SYN_SENT state in the netstat output on the server.
>>>>
>>>> While this connection is in the SYN_SENT state, any
>>>> requests to webapp B that need to call the other
>>>> database at location 2 which is still up and running
>>>> are put on hold. Visitors to that site are left
>>>> staring at a blank screen until the first connection
>>>> from webapp A to location 1 times out.
>>>>
>>>> Seeing as how these are 2 seperate webapps connecting
>>>> to 2 seperate remote locations, I did not expect the
>>>> behavior to occur in a fashion where one will block
>>>> the other. The behavior I expected was that they
>>>> would operate independantly of each other, and webapp
>>>> B would continue to work at a normal pace no matter
>>>> what is occurring with webapp A. Am I looking at this
>>>> wrong? Any help would be greatly appreciated.
>>>
>>>
>>> --
>>> To unsubscribe, e-mail: <mailto:tomcat-user-
>>> [EMAIL PROTECTED]>
>>> For additional commands, e-mail: <mailto:tomcat-user-
>>> [EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
