tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hooper, Brian" <brian.hoo...@lmco.com>
Subject RE: Tomcat Deadlock
Date Fri, 09 Jan 2004 17:45:33 GMT
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);
                }
                else {
                    this.dbName = "wipt";
                    this.ds =
(DataSource)ctx.lookup(Globals.DB_WIPT_JNDI_NAME);
                }
            }
        }
        catch (Exception e) {
            cleanUpDatabase();
            throw new Exception(e);
        }
    }
    
    // Initialize the connection
    private void initConnection(boolean transact) throws Exception {
        try {
            this.conn = this.ds.getConnection();
            if (transact) {
                this.conn.setAutoCommit(false);
 
this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                // 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:Yoav.Shapira@mpi.com] 
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:brian.hooper@lmco.com]
>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">
>
><parameter><name>factory</name><value>org.apache.commons.dbcp.BasicData
S
>ourceFactory</value></parameter>
>
>
><parameter><name>driverClassName</name><value>com.jnetdirect.jsql.JSQLD
r
>iver</value></parameter>
>
><parameter><name>url</name><value>jdbc:JSQLConnect://XXXX:1433/WIPT</va
l
>ue></parameter>
>    <parameter><name>username</name><value>XXXX</value></parameter>
>    <parameter><name>password</name><value>XXXX</value></parameter>
>
>    <parameter><name>maxActive</name><value>15</value></parameter>
>    <parameter><name>maxIdle</name><value>15</value></parameter>
>    <parameter><name>minIdle</name><value>2</value></parameter>
>    <parameter><name>maxWait</name><value>10000</value></parameter>
>
>    <parameter><name>validationQuery</name><value>SELECT
>1+1</value></parameter>
>    <parameter><name>testOnBorrow</name><value>true</value></parameter>
>    <parameter><name>testOnReturn</name><value>true</value></parameter>
>
<parameter><name>testWhileIdle</name><value>true</value></parameter>
>
><parameter><name>timeBetweenEvictionRunsMillis</name><value>1800000</va
l
>ue></parameter>
>
<parameter><name>testWhileIdle</name><value>true</value></parameter>
>
><parameter><name>numTestsPerEvictionRun</name><value>3</value></paramet
e
>r>
>
>
><parameter><name>removeAbandoned</name><value>true</value></parameter>
>
><parameter><name>removeAbandonedTimeout</name><value>300</value></param
e
>ter>
>    <parameter><name>logAbandoned</name><value>true</value></parameter>
>  </ResourceParams>
>
>Thanks!
>-Brian
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org




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: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


Mime
View raw message