RE: Tomcat Deadlock
Howdy, Can you share your code that serves the page that locked up? Yoav Shapira Millennium ChemInformatics -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:19 AM To: Tomcat Users List Subject: Tomcat Deadlock I'm having a weird problem with Tomcat locking up. I have a couple of functions on my site that rely heavily on transactions. To do a simple load test, I picked the function that hits the database the most and opened that same page in two different browser windows. I hit the submit button at roughly the same time, and waited. It appeared that both browser windows stalled. I looked at the processes in SQL Server (2000) and it said that all of the processes for the app were sleeping, and the two being used were sitting on insert statements for the same table (database deadlock?). I let both browsers sit trying to load for a lot longer than it should have taken for the page to finish, then just closed the windows. After that, the site was no longer accessible. The only way to get it working again was to restart the Tomcat service. Looking at the various log files, the only entry that appears interesting is the following from the site's log: 2004-01-09 10:43:18 StandardWrapper[/WIPT:action]: Waiting for 2 instance(s) to be deallocated I'm using tomcat 4.1.27 with SQL Server 2000 on a Win2K box. I've been having similar problems off and on for the last couple of weeks and have tried a number of different things to fix it. I made sure I closed all connections, statements, and result sets. I put all of the close code in a finally block. Another quick question - if the browser is closed in the middle of an operation, is the code in the finally block executed? Here is the JNDI data source definition from server.xml: Resource name=jdbc/WIPTDataSource auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/WIPTDataSource parameternamefactory/namevalueorg.apache.commons.dbcp.BasicData S ourceFactory/value/parameter parameternamedriverClassName/namevaluecom.jnetdirect.jsql.JSQLD r iver/value/parameter parameternameurl/namevaluejdbc:JSQLConnect://:1433/WIPT/va l ue/parameter parameternameusername/namevalue/value/parameter parameternamepassword/namevalue/value/parameter parameternamemaxActive/namevalue15/value/parameter parameternamemaxIdle/namevalue15/value/parameter parameternameminIdle/namevalue2/value/parameter parameternamemaxWait/namevalue1/value/parameter parameternamevalidationQuery/namevalueSELECT 1+1/value/parameter parameternametestOnBorrow/namevaluetrue/value/parameter parameternametestOnReturn/namevaluetrue/value/parameter parameternametestWhileIdle/namevaluetrue/value/parameter parameternametimeBetweenEvictionRunsMillis/namevalue180/va l ue/parameter parameternametestWhileIdle/namevaluetrue/value/parameter parameternamenumTestsPerEvictionRun/namevalue3/value/paramet e r parameternameremoveAbandoned/namevaluetrue/value/parameter parameternameremoveAbandonedTimeout/namevalue300/value/param e ter parameternamelogAbandoned/namevaluetrue/value/parameter /ResourceParams Thanks! -Brian - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] This e-mail, including any attachments, is a confidential business communication, and may contain information that is confidential, proprietary and/or privileged. This e-mail is intended only for the individual(s) to whom it is addressed, and may not be saved, copied, printed, disclosed or used by anyone else. If you are not the(an) intended recipient, please immediately delete this e-mail from your computer system and notify the sender. Thank you. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Tomcat Deadlock
Can just answer the easy one: If a browser is closed in the middle of the request doesn't change the way the request is processed. If the application is waiting on a lock, it will keep waiting. There is just one way to find out if the browser has been closed: write to the output stream (but that isn't very precise due to buffers and caches). Sorry for the rest. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 5:19 PM To: Tomcat Users List Subject: Tomcat Deadlock After that, the site was no longer accessible. The only way to get it working again was to restart the Tomcat service. Looking at the various log files, the only entry that appears interesting is the following from the site's log: 2004-01-09 10:43:18 StandardWrapper[/WIPT:action]: Waiting for 2 instance(s) to be deallocated I'm using tomcat 4.1.27 with SQL Server 2000 on a Win2K box. I've been having similar problems off and on for the last couple of weeks and have tried a number of different things to fix it. I made sure I closed all connections, statements, and result sets. I put all of the close code in a finally block. Another quick question - if the browser is closed in the middle of an operation, is the code in the finally block executed? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Tomcat Deadlock
); // Set the LOCK_TIMEOUT this.stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); this.stmt.executeUpdate(SET LOCK_TIMEOUT + Globals.DB_LOCK_TIMEOUT + ; ); // Commit the LOCK_TIMEOUT //commit(); } else { this.conn.setAutoCommit(true); this.conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTE D); // Set the LOCK_TIMEOUT this.stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); this.stmt.executeUpdate(SET LOCK_TIMEOUT + Globals.DB_LOCK_TIMEOUT + ; ); } } catch (Exception e) { cleanUpDatabase(); throw new Exception(e); } } public ResultSet runSQL(String sqlstmt) throws Exception { try { return this.stmt.executeQuery(sqlstmt); } catch (Exception e) { cleanUpDatabase(); throw new Exception(e); } } public int runUpdateSQL(String sqlstmt) throws Exception { try { return this.stmt.executeUpdate(sqlstmt); } catch (Exception e) { cleanUpDatabase(); throw new Exception(e); } } public ResultSet runStoredProc(String sqlstmt) throws Exception { try { this.cstmt = this.conn.prepareCall(sqlstmt, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); return this.cstmt.executeQuery(); } catch (Exception e) { cleanUpDatabase(); throw new Exception(e); } } public int runUpdateStoredProc(String sqlstmt) throws Exception { try { this.cstmt = this.conn.prepareCall(sqlstmt, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); return this.cstmt.executeUpdate(); } catch (Exception e) { cleanUpDatabase(); throw new Exception(e); } } // Commit the database connection public void commit() throws Exception { try { if (this.transaction) { this.conn.commit(); } } catch (Exception e) { cleanUpDatabase(); throw new Exception(e); } } // End the database connection public void end() { try { if (this.transaction) { this.conn.commit(); } } catch (Exception e) {} finally { cleanUpDatabase(); } } // Rollback the database connection public void rollbackDB() { try { if (this.transaction) { this.conn.rollback(); } } catch (Exception e) {} finally { cleanUpDatabase(); } } // Set the database objects to null. Only done on object creation!! private void nullObjects() { this.ds = null; this.conn = null; this.stmt = null; this.pstmt = null; this.cstmt = null; } // Clean up database objects private void cleanUpDatabase() { if (this.stmt != null) { try { this.stmt.close(); } catch (SQLException e) { ; } this.stmt = null; } if (this.pstmt != null) { try { this.pstmt.close(); } catch (SQLException e) { ; } this.pstmt = null; } if (this.cstmt != null) { try { this.cstmt.close(); } catch (SQLException e) { ; } this.cstmt = null; } if (this.conn != null) { try { this.conn.close(); } catch (SQLException e) { ; } this.conn = null; } if (this.ds != null) this.ds = null; } public void destroy() { cleanUpDatabase(); this.dbName = null; } } -Original Message- From: Shapira, Yoav [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:23 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock Howdy, Can you share your code that serves the page that locked up? Yoav Shapira Millennium ChemInformatics -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:19 AM To: Tomcat Users List Subject: Tomcat Deadlock I'm having a weird problem with Tomcat locking up. I have a couple of functions on my site that rely heavily on transactions. To do a simple load test, I picked the function that hits the database the most and opened that same page in two different browser windows. I hit the submit button at roughly the same time, and waited. It appeared that both browser windows stalled. I looked at the processes in SQL Server (2000) and it said that all of the processes for the app were sleeping, and the two being used were sitting on insert statements
RE: Tomcat Deadlock
First off, if you have the option you might try using a database pool instead of using 1 connection for multiple threads (As in Tomcat). At one company I worked for we had some problems with using 1 connection. Data would be read/written incorrectly. Results from one query would be returned to another etc. Bad driver...probably, but it's still a safer and better use of resources to let Tomcat give you a connection from a pool and then release it back when you are done. -Steve -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:46 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock The code itself is pretty long. Maybe it would be better if I explain how I handle database connectivity (which I'm guessing has some flaws), and I know I should be encapsulating my queries in EJB's, but for now I just have a lot of inline SQL in my Actions. I use a DatabaseManager class (at the end of the e-mail) to connect to my database. If an action needs to connect, it makes a new DatabaseManager object. It uses the methods in the class, then at the end it calls a function to clean up the connection, etc. Here is the code for the database manager class: package WIPT; import java.sql.*; import javax.sql.*; import java.util.*; import java.lang.*; import java.io.*; import javax.naming.*; public class DatabaseManager { // Data Members // Keep track of the current database in use for transactions private String dbName; // The data source private DataSource ds; // The connection private Connection conn; // The statement private Statement stmt; // The prepared statement private PreparedStatement pstmt; // The callable statement (for stored procedures) private CallableStatement cstmt; // If transactions are being used or not private boolean transaction; // Empty constructor public DatabaseManager() throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { // Default the database to WIPT and not to use transactions this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified public DatabaseManager(boolean transact) throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the database to be used (wipt or user) public DatabaseManager(String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified and the database to be used (wipt or user) public DatabaseManager(boolean transact, String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Return the dbName being used public String getDBName() { return this.dbName; } // Initialize the data source private void initDataSource(String dbName) throws Exception { try { Context ctx = new InitialContext(); if (ctx == null) throw new Exception(Unable to initialize the WIPT database.); else { if (dbName != null dbName.toLowerCase().equals(user)) { this.dbName = user; this.ds = (DataSource)ctx.lookup(Globals.DB_USER_JNDI_NAME
RE: Tomcat Deadlock
I thought I was by defining the data source (using org.apache.commons.dbcp.BasicDataSourceFactory) in my server.xml file and using JNDI to access it? -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock First off, if you have the option you might try using a database pool instead of using 1 connection for multiple threads (As in Tomcat). At one company I worked for we had some problems with using 1 connection. Data would be read/written incorrectly. Results from one query would be returned to another etc. Bad driver...probably, but it's still a safer and better use of resources to let Tomcat give you a connection from a pool and then release it back when you are done. -Steve -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:46 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock The code itself is pretty long. Maybe it would be better if I explain how I handle database connectivity (which I'm guessing has some flaws), and I know I should be encapsulating my queries in EJB's, but for now I just have a lot of inline SQL in my Actions. I use a DatabaseManager class (at the end of the e-mail) to connect to my database. If an action needs to connect, it makes a new DatabaseManager object. It uses the methods in the class, then at the end it calls a function to clean up the connection, etc. Here is the code for the database manager class: package WIPT; import java.sql.*; import javax.sql.*; import java.util.*; import java.lang.*; import java.io.*; import javax.naming.*; public class DatabaseManager { // Data Members // Keep track of the current database in use for transactions private String dbName; // The data source private DataSource ds; // The connection private Connection conn; // The statement private Statement stmt; // The prepared statement private PreparedStatement pstmt; // The callable statement (for stored procedures) private CallableStatement cstmt; // If transactions are being used or not private boolean transaction; // Empty constructor public DatabaseManager() throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { // Default the database to WIPT and not to use transactions this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified public DatabaseManager(boolean transact) throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the database to be used (wipt or user) public DatabaseManager(String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified and the database to be used (wipt or user) public DatabaseManager(boolean transact, String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Return the dbName being used public String getDBName() { return this.dbName; } // Initialize the data source private void initDataSource(String dbName) throws Exception { try { Context ctx = new InitialContext
RE: Tomcat Deadlock
Take a look at this. http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-how to.html Every time you load a page open the connection, then close it to release it to the pool. That way each thread should get 1 connection. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:20 PM To: Tomcat Users List Subject: RE: Tomcat Deadlock I thought I was by defining the data source (using org.apache.commons.dbcp.BasicDataSourceFactory) in my server.xml file and using JNDI to access it? -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock First off, if you have the option you might try using a database pool instead of using 1 connection for multiple threads (As in Tomcat). At one company I worked for we had some problems with using 1 connection. Data would be read/written incorrectly. Results from one query would be returned to another etc. Bad driver...probably, but it's still a safer and better use of resources to let Tomcat give you a connection from a pool and then release it back when you are done. -Steve -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:46 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock The code itself is pretty long. Maybe it would be better if I explain how I handle database connectivity (which I'm guessing has some flaws), and I know I should be encapsulating my queries in EJB's, but for now I just have a lot of inline SQL in my Actions. I use a DatabaseManager class (at the end of the e-mail) to connect to my database. If an action needs to connect, it makes a new DatabaseManager object. It uses the methods in the class, then at the end it calls a function to clean up the connection, etc. Here is the code for the database manager class: package WIPT; import java.sql.*; import javax.sql.*; import java.util.*; import java.lang.*; import java.io.*; import javax.naming.*; public class DatabaseManager { // Data Members // Keep track of the current database in use for transactions private String dbName; // The data source private DataSource ds; // The connection private Connection conn; // The statement private Statement stmt; // The prepared statement private PreparedStatement pstmt; // The callable statement (for stored procedures) private CallableStatement cstmt; // If transactions are being used or not private boolean transaction; // Empty constructor public DatabaseManager() throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { // Default the database to WIPT and not to use transactions this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified public DatabaseManager(boolean transact) throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the database to be used (wipt or user) public DatabaseManager(String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified and the database to be used (wipt or user) public DatabaseManager(boolean transact, String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction
RE: Tomcat Deadlock
I followed the example for Tomcat 4.x when I set it up. The only reason I have a separate object is to hide some of the complexity. I'm pretty sure it's using a connection pool right now. -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:25 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock Take a look at this. http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples -how to.html Every time you load a page open the connection, then close it to release it to the pool. That way each thread should get 1 connection. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:20 PM To: Tomcat Users List Subject: RE: Tomcat Deadlock I thought I was by defining the data source (using org.apache.commons.dbcp.BasicDataSourceFactory) in my server.xml file and using JNDI to access it? -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock First off, if you have the option you might try using a database pool instead of using 1 connection for multiple threads (As in Tomcat). At one company I worked for we had some problems with using 1 connection. Data would be read/written incorrectly. Results from one query would be returned to another etc. Bad driver...probably, but it's still a safer and better use of resources to let Tomcat give you a connection from a pool and then release it back when you are done. -Steve -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:46 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock The code itself is pretty long. Maybe it would be better if I explain how I handle database connectivity (which I'm guessing has some flaws), and I know I should be encapsulating my queries in EJB's, but for now I just have a lot of inline SQL in my Actions. I use a DatabaseManager class (at the end of the e-mail) to connect to my database. If an action needs to connect, it makes a new DatabaseManager object. It uses the methods in the class, then at the end it calls a function to clean up the connection, etc. Here is the code for the database manager class: package WIPT; import java.sql.*; import javax.sql.*; import java.util.*; import java.lang.*; import java.io.*; import javax.naming.*; public class DatabaseManager { // Data Members // Keep track of the current database in use for transactions private String dbName; // The data source private DataSource ds; // The connection private Connection conn; // The statement private Statement stmt; // The prepared statement private PreparedStatement pstmt; // The callable statement (for stored procedures) private CallableStatement cstmt; // If transactions are being used or not private boolean transaction; // Empty constructor public DatabaseManager() throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { // Default the database to WIPT and not to use transactions this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified public DatabaseManager(boolean transact) throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the database to be used (wipt or user) public DatabaseManager(String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified and the database to be used (wipt or user) public DatabaseManager(boolean
RE: Tomcat Deadlock
Hrmm... Do you create a new instance of the DatabaseManager class during each load of the page? For some strange, wacky reason I thought you were using it almost as a singleton. If that's not the case then you probably are doing the right thing with that, and I am just talkin fluff. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: Tomcat Users List Subject: RE: Tomcat Deadlock I followed the example for Tomcat 4.x when I set it up. The only reason I have a separate object is to hide some of the complexity. I'm pretty sure it's using a connection pool right now. -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:25 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock Take a look at this. http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples -how to.html Every time you load a page open the connection, then close it to release it to the pool. That way each thread should get 1 connection. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:20 PM To: Tomcat Users List Subject: RE: Tomcat Deadlock I thought I was by defining the data source (using org.apache.commons.dbcp.BasicDataSourceFactory) in my server.xml file and using JNDI to access it? -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock First off, if you have the option you might try using a database pool instead of using 1 connection for multiple threads (As in Tomcat). At one company I worked for we had some problems with using 1 connection. Data would be read/written incorrectly. Results from one query would be returned to another etc. Bad driver...probably, but it's still a safer and better use of resources to let Tomcat give you a connection from a pool and then release it back when you are done. -Steve -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:46 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock The code itself is pretty long. Maybe it would be better if I explain how I handle database connectivity (which I'm guessing has some flaws), and I know I should be encapsulating my queries in EJB's, but for now I just have a lot of inline SQL in my Actions. I use a DatabaseManager class (at the end of the e-mail) to connect to my database. If an action needs to connect, it makes a new DatabaseManager object. It uses the methods in the class, then at the end it calls a function to clean up the connection, etc. Here is the code for the database manager class: package WIPT; import java.sql.*; import javax.sql.*; import java.util.*; import java.lang.*; import java.io.*; import javax.naming.*; public class DatabaseManager { // Data Members // Keep track of the current database in use for transactions private String dbName; // The data source private DataSource ds; // The connection private Connection conn; // The statement private Statement stmt; // The prepared statement private PreparedStatement pstmt; // The callable statement (for stored procedures) private CallableStatement cstmt; // If transactions are being used or not private boolean transaction; // Empty constructor public DatabaseManager() throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { // Default the database to WIPT and not to use transactions this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the transaction level to be specified public DatabaseManager(boolean transact) throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { this.dbName = wipt; this.transaction = transact; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow the database to be used (wipt or user) public DatabaseManager(String dbName) throws Exception { try { if (dbName != null dbName.toLowerCase().equals(user)) this.dbName = user; else
RE: Tomcat Deadlock
I do create a new instance, but each instance grabs a connection from the pool instead of making a new connection. I think this is ok. Maybe my problem is with SQL Server locks instead of anything on the tomcat side. It just seems like something on the tomcat side should timeout instead of locking up the server. If I assume that it's a deadlock in SQL Server (and for some reason SQL Server isn't reporting it), can anyone think of a reason why tomcat would not respond? The connection pool still has connections available. It could be that tomcat isn't really hanging. I have a filter set up that checks to see if the user is authenticated, so the database is hit before every page request. -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:59 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock Hrmm... Do you create a new instance of the DatabaseManager class during each load of the page? For some strange, wacky reason I thought you were using it almost as a singleton. If that's not the case then you probably are doing the right thing with that, and I am just talkin fluff. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: Tomcat Users List Subject: RE: Tomcat Deadlock I followed the example for Tomcat 4.x when I set it up. The only reason I have a separate object is to hide some of the complexity. I'm pretty sure it's using a connection pool right now. -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:25 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock Take a look at this. http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples -how to.html Every time you load a page open the connection, then close it to release it to the pool. That way each thread should get 1 connection. -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:20 PM To: Tomcat Users List Subject: RE: Tomcat Deadlock I thought I was by defining the data source (using org.apache.commons.dbcp.BasicDataSourceFactory) in my server.xml file and using JNDI to access it? -Original Message- From: Steve Nelson [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:50 PM To: 'Tomcat Users List' Subject: RE: Tomcat Deadlock First off, if you have the option you might try using a database pool instead of using 1 connection for multiple threads (As in Tomcat). At one company I worked for we had some problems with using 1 connection. Data would be read/written incorrectly. Results from one query would be returned to another etc. Bad driver...probably, but it's still a safer and better use of resources to let Tomcat give you a connection from a pool and then release it back when you are done. -Steve -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 11:46 AM To: Tomcat Users List Subject: RE: Tomcat Deadlock The code itself is pretty long. Maybe it would be better if I explain how I handle database connectivity (which I'm guessing has some flaws), and I know I should be encapsulating my queries in EJB's, but for now I just have a lot of inline SQL in my Actions. I use a DatabaseManager class (at the end of the e-mail) to connect to my database. If an action needs to connect, it makes a new DatabaseManager object. It uses the methods in the class, then at the end it calls a function to clean up the connection, etc. Here is the code for the database manager class: package WIPT; import java.sql.*; import javax.sql.*; import java.util.*; import java.lang.*; import java.io.*; import javax.naming.*; public class DatabaseManager { // Data Members // Keep track of the current database in use for transactions private String dbName; // The data source private DataSource ds; // The connection private Connection conn; // The statement private Statement stmt; // The prepared statement private PreparedStatement pstmt; // The callable statement (for stored procedures) private CallableStatement cstmt; // If transactions are being used or not private boolean transaction; // Empty constructor public DatabaseManager() throws Exception { // Initialize the database objects to null initially nullObjects(); // Initialize the database objects to their real values try { // Default the database to WIPT and not to use transactions this.dbName = wipt; this.transaction = false; initDataSource(this.dbName); initConnection(this.transaction); } catch (Exception e) { cleanUpDatabase(); throw new Exception(Unable to initialize the WIPT database); } } // Overloaded constructor to allow
RE: Tomcat Deadlock
Using SQL Enterprise manager, find out what process is holding a lock, and what it's last statement was. You do know that you have to rollback failed update statements don't you? -Original Message- From: Hooper, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 9:19 AM To: Tomcat Users List Subject: Tomcat Deadlock I'm having a weird problem with Tomcat locking up. I have a couple of functions on my site that rely heavily on transactions. To do a simple load test, I picked the function that hits the database the most and opened that same page in two different browser windows. I hit the submit button at roughly the same time, and waited. It appeared that both browser windows stalled. I looked at the processes in SQL Server (2000) and it said that all of the processes for the app were sleeping, and the two being used were sitting on insert statements for the same table (database deadlock?). I let both browsers sit trying to load for a lot longer than it should have taken for the page to finish, then just closed the windows. After that, the site was no longer accessible. The only way to get it working again was to restart the Tomcat service. Looking at the various log files, the only entry that appears interesting is the following from the site's log: 2004-01-09 10:43:18 StandardWrapper[/WIPT:action]: Waiting for 2 instance(s) to be deallocated I'm using tomcat 4.1.27 with SQL Server 2000 on a Win2K box. I've been having similar problems off and on for the last couple of weeks and have tried a number of different things to fix it. I made sure I closed all connections, statements, and result sets. I put all of the close code in a finally block. Another quick question - if the browser is closed in the middle of an operation, is the code in the finally block executed? Here is the JNDI data source definition from server.xml: Resource name=jdbc/WIPTDataSource auth=Container type=javax.sql.DataSource/ ResourceParams name=jdbc/WIPTDataSource parameternamefactory/namevalueorg.apache.commons.dbcp.BasicDataS ourceFactory/value/parameter parameternamedriverClassName/namevaluecom.jnetdirect.jsql.JSQLDr iver/value/parameter parameternameurl/namevaluejdbc:JSQLConnect://:1433/WIPT/val ue/parameter parameternameusername/namevalue/value/parameter parameternamepassword/namevalue/value/parameter parameternamemaxActive/namevalue15/value/parameter parameternamemaxIdle/namevalue15/value/parameter parameternameminIdle/namevalue2/value/parameter parameternamemaxWait/namevalue1/value/parameter parameternamevalidationQuery/namevalueSELECT 1+1/value/parameter parameternametestOnBorrow/namevaluetrue/value/parameter parameternametestOnReturn/namevaluetrue/value/parameter parameternametestWhileIdle/namevaluetrue/value/parameter parameternametimeBetweenEvictionRunsMillis/namevalue180/val ue/parameter parameternametestWhileIdle/namevaluetrue/value/parameter parameternamenumTestsPerEvictionRun/namevalue3/value/paramete r parameternameremoveAbandoned/namevaluetrue/value/parameter parameternameremoveAbandonedTimeout/namevalue300/value/parame ter parameternamelogAbandoned/namevaluetrue/value/parameter /ResourceParams Thanks! -Brian - 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]