Re: Failed JDBC connection hangs Tomcat
This is so strange... i switched to a commercial driver and deadlocks started to arise (hangs stopped)... i made some changes in the transaction logic and everything went fine... after talking with a JTDS developer I switched to JTDS again to force him to freeze so I could get some debug information for him now it doesn't hang anymore it has been running from yesterday afternoon (almost twenty hours) with full load and it has not presented any problems... I'm just puzzled... On Wed, 2 Feb 2005 08:02:21 +0200, Igor [EMAIL PROTECTED] wrote: Hello! I sent answer to tomcat users yesterday, but it seems not to be accepted ... So I am sending it once more ... I am referring to JTDS 1.0. I think, that the problem is in net.sourceforge.jtds.jdbc.SharedSocket class: it should set timeout for socket, otherwise methods getNetPacket and sendNetPacket may hang. Igor - Original Message - From: Arthur D'Alessandro [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org; Hari Mailvaganam [EMAIL PROTECTED] Cc: Igor [EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:40 PM Subject: Re: Failed JDBC connection hangs Tomcat What version of the JTDS are you referring to, I know they recently released 1.0 code... On Sun, 30 Jan 2005 20:06:07 -0800, Hari Mailvaganam [EMAIL PROTECTED] wrote: The JTDS driver can cause the hanging you described - this has occured in a test we did on Win 2k, SQL Server 2k, 1.5 JDK. reagrds, Hari Mailvaganam On Sun, 30 Jan 2005 08:39:50 +0200, Igor [EMAIL PROTECTED] wrote: We have the same problem, that described in http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg58799.html There is ServletContextListener in our application, that schedule two tasks for repeated fixed rate execution on context initialization (Timer.scheduleAtFixedRate). First task is executed every minute. It queries MS SQL server, second task is executed every hour. If MS SQL server goes down, first task tries to use connection to database. After this Tomcat may hang: when user tries to download a page, he or her will wait for a long time (more that 1 day). At the same time second taks (that is executed every hour) works as it was expected: there are corresponding entries in log file. First task is executed in separate thread, and I do not understand how can it hang whole tomcat (at least one context). We use JDK 1.5, tomcat 5.0.28. The same problem was on 1.4.2. Both for MS and JTDS drivers :-( Does somebody know how can I prevent tomcat from hang in such situations? Thank you in advance, Igor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- -Art D'Alessandro - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
What version of the JTDS are you referring to, I know they recently released 1.0 code... On Sun, 30 Jan 2005 20:06:07 -0800, Hari Mailvaganam [EMAIL PROTECTED] wrote: The JTDS driver can cause the hanging you described - this has occured in a test we did on Win 2k, SQL Server 2k, 1.5 JDK. reagrds, Hari Mailvaganam On Sun, 30 Jan 2005 08:39:50 +0200, Igor [EMAIL PROTECTED] wrote: We have the same problem, that described in http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg58799.html There is ServletContextListener in our application, that schedule two tasks for repeated fixed rate execution on context initialization (Timer.scheduleAtFixedRate). First task is executed every minute. It queries MS SQL server, second task is executed every hour. If MS SQL server goes down, first task tries to use connection to database. After this Tomcat may hang: when user tries to download a page, he or her will wait for a long time (more that 1 day). At the same time second taks (that is executed every hour) works as it was expected: there are corresponding entries in log file. First task is executed in separate thread, and I do not understand how can it hang whole tomcat (at least one context). We use JDK 1.5, tomcat 5.0.28. The same problem was on 1.4.2. Both for MS and JTDS drivers :-( Does somebody know how can I prevent tomcat from hang in such situations? Thank you in advance, Igor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- -Art D'Alessandro - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
Hello! I am referring to JTDS 1.0. I think, that the problem is in net.sourceforge.jtds.jdbc.SharedSocket class: it should set timeout for socket, otherwise methods getNetPacket and sendNetPacket may hang. Igor - Original Message - From: Arthur D'Alessandro [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org; Hari Mailvaganam [EMAIL PROTECTED] Cc: Igor [EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:40 PM Subject: Re: Failed JDBC connection hangs Tomcat What version of the JTDS are you referring to, I know they recently released 1.0 code... On Sun, 30 Jan 2005 20:06:07 -0800, Hari Mailvaganam [EMAIL PROTECTED] wrote: The JTDS driver can cause the hanging you described - this has occured in a test we did on Win 2k, SQL Server 2k, 1.5 JDK. reagrds, Hari Mailvaganam On Sun, 30 Jan 2005 08:39:50 +0200, Igor [EMAIL PROTECTED] wrote: We have the same problem, that described in http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg58799.html There is ServletContextListener in our application, that schedule two tasks for repeated fixed rate execution on context initialization (Timer.scheduleAtFixedRate). First task is executed every minute. It queries MS SQL server, second task is executed every hour. If MS SQL server goes down, first task tries to use connection to database. After this Tomcat may hang: when user tries to download a page, he or her will wait for a long time (more that 1 day). At the same time second taks (that is executed every hour) works as it was expected: there are corresponding entries in log file. First task is executed in separate thread, and I do not understand how can it hang whole tomcat (at least one context). We use JDK 1.5, tomcat 5.0.28. The same problem was on 1.4.2. Both for MS and JTDS drivers :-( Does somebody know how can I prevent tomcat from hang in such situations? Thank you in advance, Igor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- -Art D'Alessandro - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
Hello! I sent answer to tomcat users yesterday, but it seems not to be accepted ... So I am sending it once more ... I am referring to JTDS 1.0. I think, that the problem is in net.sourceforge.jtds.jdbc.SharedSocket class: it should set timeout for socket, otherwise methods getNetPacket and sendNetPacket may hang. Igor - Original Message - From: Arthur D'Alessandro [EMAIL PROTECTED] To: Tomcat Users List tomcat-user@jakarta.apache.org; Hari Mailvaganam [EMAIL PROTECTED] Cc: Igor [EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:40 PM Subject: Re: Failed JDBC connection hangs Tomcat What version of the JTDS are you referring to, I know they recently released 1.0 code... On Sun, 30 Jan 2005 20:06:07 -0800, Hari Mailvaganam [EMAIL PROTECTED] wrote: The JTDS driver can cause the hanging you described - this has occured in a test we did on Win 2k, SQL Server 2k, 1.5 JDK. reagrds, Hari Mailvaganam On Sun, 30 Jan 2005 08:39:50 +0200, Igor [EMAIL PROTECTED] wrote: We have the same problem, that described in http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg58799.html There is ServletContextListener in our application, that schedule two tasks for repeated fixed rate execution on context initialization (Timer.scheduleAtFixedRate). First task is executed every minute. It queries MS SQL server, second task is executed every hour. If MS SQL server goes down, first task tries to use connection to database. After this Tomcat may hang: when user tries to download a page, he or her will wait for a long time (more that 1 day). At the same time second taks (that is executed every hour) works as it was expected: there are corresponding entries in log file. First task is executed in separate thread, and I do not understand how can it hang whole tomcat (at least one context). We use JDK 1.5, tomcat 5.0.28. The same problem was on 1.4.2. Both for MS and JTDS drivers :-( Does somebody know how can I prevent tomcat from hang in such situations? Thank you in advance, Igor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- -Art D'Alessandro - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
The JTDS driver can cause the hanging you described - this has occured in a test we did on Win 2k, SQL Server 2k, 1.5 JDK. reagrds, Hari Mailvaganam On Sun, 30 Jan 2005 08:39:50 +0200, Igor [EMAIL PROTECTED] wrote: We have the same problem, that described in http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg58799.html There is ServletContextListener in our application, that schedule two tasks for repeated fixed rate execution on context initialization (Timer.scheduleAtFixedRate). First task is executed every minute. It queries MS SQL server, second task is executed every hour. If MS SQL server goes down, first task tries to use connection to database. After this Tomcat may hang: when user tries to download a page, he or her will wait for a long time (more that 1 day). At the same time second taks (that is executed every hour) works as it was expected: there are corresponding entries in log file. First task is executed in separate thread, and I do not understand how can it hang whole tomcat (at least one context). We use JDK 1.5, tomcat 5.0.28. The same problem was on 1.4.2. Both for MS and JTDS drivers :-( Does somebody know how can I prevent tomcat from hang in such situations? Thank you in advance, Igor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Failed JDBC connection hangs Tomcat
We have the same problem, that described in http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg58799.html There is ServletContextListener in our application, that schedule two tasks for repeated fixed rate execution on context initialization (Timer.scheduleAtFixedRate). First task is executed every minute. It queries MS SQL server, second task is executed every hour. If MS SQL server goes down, first task tries to use connection to database. After this Tomcat may hang: when user tries to download a page, he or her will wait for a long time (more that 1 day). At the same time second taks (that is executed every hour) works as it was expected: there are corresponding entries in log file. First task is executed in separate thread, and I do not understand how can it hang whole tomcat (at least one context). We use JDK 1.5, tomcat 5.0.28. The same problem was on 1.4.2. Both for MS and JTDS drivers :-( Does somebody know how can I prevent tomcat from hang in such situations? Thank you in advance, Igor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
Here is some sample init() method code that I use to test the db connection on context startup: try { /*- * The driver objects register themselves with the driver manager * at the time of loading, * and the following line forces the loading of the (MySQL or other) * driver * (it registers itself). */ Class.forName(driver).newInstance(); conn = DriverManager.getConnection(url); sc.log(Database connection successfully obtained.); conn.setAutoCommit(false); // Prepare the query: String qString = queryString.getQueryString(); sc.log(\n\n Using this query string: + qString + \n); pstmt = conn.prepareStatement(qString); // Test db connection for integrity: stmt = conn.createStatement(); rs = stmt.executeQuery(highestUniqueId); if (rs.next()) { uniqueEntryId = rs.getInt(1); // Gets the highest unique survey i.d. } sc.log(uniqueEntryId is initialized as +uniqueEntryId); // Close the statement object, which also closes the resultset. stmt.close(); if (uniqueEntryId 0) { sc.log(Problem with database connection if + this is not a new db table. This db table has no + greatest unique entry id number. ); } } catch (ClassNotFoundException e) { String msg = *** Couldn't load database driver.; sc.log(msg, e); throw new UnavailableException(msg); } catch (SQLException e) { String msg = *** Couldn't get db connection.; sc.log(msg, e); throw new UnavailableException(msg); } catch (IllegalAccessException e) { String msg = *** Couldn't get access to db connection.; sc.log(msg, e); throw new UnavailableException(msg); } catch (InstantiationException e) { String msg = *** Couldn't create db connection.; sc.log(msg, e); throw new UnavailableException(msg); } Jacob Kjome wrote: hmm I think in that case you would have to set a Timeout on the connection. You might want to test the connection upon context startup and if the connection times out, then throw an error or redirect to a static page saying that the app is temporarily unavailable. Not sure what else you can do? Anyone? Jake At 01:00 PM 7/16/2002 -0700, you wrote: 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(); }
Re: Failed JDBC connection hangs Tomcat
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
Re: Failed JDBC connection hangs Tomcat
hmm I think in that case you would have to set a Timeout on the connection. You might want to test the connection upon context startup and if the connection times out, then throw an error or redirect to a static page saying that the app is temporarily unavailable. Not sure what else you can do? Anyone? Jake At 01:00 PM 7/16/2002 -0700, you wrote: 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
Re: Failed JDBC connection hangs Tomcat
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:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
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:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Re: Failed JDBC connection hangs Tomcat
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:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
Failed JDBC connection hangs Tomcat
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. __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: Failed JDBC connection hangs Tomcat
That sounds like a threading issue for the JDBC Driver, if you are using the same instance. You could move the Oracle Driver to the WEB-INF\lib directory for each webapp. - Andrew -Original Message- From: Bill D [mailto:[EMAIL PROTECTED]] Sent: Monday, July 08, 2002 2:05 PM To: [EMAIL PROTECTED] Subject: Failed JDBC connection hangs Tomcat 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. __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- 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]
RE: Failed JDBC connection hangs Tomcat
I attempted that, and webapp B still hangs up waiting on webapp A. --- Andrew Conrad [EMAIL PROTECTED] wrote: That sounds like a threading issue for the JDBC Driver, if you are using the same instance. You could move the Oracle Driver to the WEB-INF\lib directory for each webapp. - Andrew __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- To unsubscribe, e-mail: mailto:[EMAIL PROTECTED] For additional commands, e-mail: mailto:[EMAIL PROTECTED]
RE: Failed JDBC connection hangs Tomcat
You probably did this but I'll ask anyways. When you put the jars in the WEB-INF\lib folders, did you make sure to delete the shared versions from %CATALINA%\lib and %CATALINA%\shared\lib ? Again it may be a problem with the JDBC Driver. Check on the Oracle forums to see if anyone is having similar problems. Do you have the same problem with two stand-alone applications? -Andrew -Original Message- From: Bill D [mailto:[EMAIL PROTECTED]] Sent: Monday, July 08, 2002 4:08 PM To: Tomcat Users List Subject: RE: Failed JDBC connection hangs Tomcat I attempted that, and webapp B still hangs up waiting on webapp A. --- Andrew Conrad [EMAIL PROTECTED] wrote: That sounds like a threading issue for the JDBC Driver, if you are using the same instance. You could move the Oracle Driver to the WEB-INF\lib directory for each webapp. - Andrew __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- 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]