hi;
I'm writing an BMP entity bean with CMT that insert data into sql server 2000. But I keep on having deadlock problem. This is the message that I got from Jboss:
[INFO,Default] Error in storeRow = com.jnetdirect.jsql.u: Transaction (Process I
D 142) was deadlocked on {lock} resources with another process and has been chos
en as the deadlock victim. Rerun the transaction.

I have used JSQL Connect and database driver and I still experienced the same problem. Could you please any body who has experience on this? (probably JBOSS Developer?). cause this is the MAJOR problem that prevent us from using JBoss as our production application server.
I also have increase the instance-pool size and cache to 1000. Please helppppppppp

This is the jboss.jcml configuration:
=====================================

com.jnetdirect.jsql.JSQLDriver



SQLServerDS
org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

jdbc:JSQLConnect://localhost:1433/test
1200000
tj
arlo97
100
1000
false
false
false
false
120000
200000
true
false
1.0
TRANSACTION_READ_COMMITTED


this is the ejb-jar.xml :
==========================
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE ejb-jar PUBLIC '-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 1.1//EN' 'http://java.sun.com/j2ee/dtds/ejb-jar_1_1.dtd'>

<ejb-jar>
no description
<display-name>TestJAR</display-name>
<enterprise-beans>

no description
<display-name>TestBean</display-name>
<ejb-name>TestBean</ejb-name>
TestHome
Test
<ejb-class>TestEJB</ejb-class>
<persistence-type>Bean</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<resource-ref>
<res-ref-name>jdbc/Test</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

</enterprise-beans>
<assembly-descriptor>

<container-transaction>

<ejb-name>TestBean</ejb-name>
<method-name>*</method-name>

<trans-attribute>Required</trans-attribute>
</container-transaction>

<container-transaction>

<ejb-name>TestBean</ejb-name>
<method-intf>Remote</method-intf>
<method-name>methodD</method-name>

<trans-attribute>RequiresNew</trans-attribute>
</container-transaction>

<container-transaction>

<ejb-name>TestBean</ejb-name>
<method-intf>Home</method-intf>
<method-name>create</method-name>

<trans-attribute>RequiresNew</trans-attribute>
</container-transaction>

<container-transaction>

<ejb-name>TestBean</ejb-name>
<method-intf>Home</method-intf>
<method-name>findByPrimaryKey</method-name>

<trans-attribute>RequiresNew</trans-attribute>
</container-transaction>

</assembly-descriptor>
</ejb-jar>

this is the jboss.xml:
=======================

<enterprise-beans>

<ejb-name>TestBean</ejb-name>
<jndi-name>ejb/Test</jndi-name>

<resource-ref>
<res-ref-name>jdbc/Test</res-ref-name>
<jndi-name>java:/SQLServerDS</jndi-name>
</resource-ref>

</enterprise-beans>

<container-configurations>
<instance-pool>100000</instance-pool>
<instance-cache>100000</instance-cache>
</container-configurations>





this is the bean class:
=======================


import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import java.rmi.RemoteException;
import javax.ejb.*;
import java.util.*;
import javax.transaction.TransactionRolledbackException;



public class TestEJB implements EntityBean {

private Connection con;
private EntityContext context;
private String TestID;
private String TestName;
private int TestNumber;

//
private String dbName="java:SQLServerDS";
//

public String getTestID() {
return TestID;
}

public void setTestID(String TestID) {}

public String getTestName() {
return TestName;
}

public void setTestName(String TestName) {}

public int getTestNumber() {
return TestNumber;
}

public void setTestNumber(int TestNumber) {}



public void methodA() throws RemoteException {

try {
insertRow("1", "1", 1);
} catch (Exception ex) {
System.out.println("Error in methodA() = " + ex);
//throw new EJBException("methodA: " + ex.getMessage());
throw new RemoteException ("methodA: " + ex.getMessage());
//context.setRollbackOnly();
//throw new TransactionRolledbackException ("methodA: " + ex.getMessage());

}


}

public void methodB() throws RemoteException {
try {
insertRow("2", "2", 2);
} catch (Exception ex) {
System.out.println("Error in methodB() = " + ex);
throw new RemoteException("methodB: " + ex.getMessage());
//context.setRollbackOnly();
//throw new TransactionRolledbackException("methodB: " + ex.getMessage());

}



}

public void methodC() throws RemoteException {
try {
insertRow("3", "3", 3);
} catch (Exception ex) {
System.out.println("Error in methodC() = " + ex);
throw new RemoteException("methodC() = " + ex.getMessage());
//context.setRollbackOnly();
//throw new TransactionRolledbackException("methodC() = " + ex.getMessage());

}


}

public void methodD() throws RemoteException {
try {
methodA();
methodB();
methodC();
} catch (Exception ex) {
System.out.println("Error in methodD() = " + ex);
throw new RemoteException("methodD() = " + ex.getMessage());
//context.setRollbackOnly();
//throw new TransactionRolledbackException("methodD() = " + ex.getMessage());
}


}




public String ejbCreate (String TestID, String TestName, int TestNumber) throws RemoteException, CreateException {
System.out.println("Entering ejbCreate, TestID = " + this.TestID);
try {
insertRow(TestID, TestName, TestNumber);
} catch (Exception ex) {
throw new RemoteException("ejbCreate: " + ex.getMessage());
//throw new EJBException("ejbCreate: " + ex.getMessage());
}



this.TestID=TestID;
this.TestName=TestName;
this.TestNumber=TestNumber;
System.out.println("Leaving ejbCreate, TestID = " + this.TestID);
return TestID;

}

public String ejbFindByPrimaryKey(String TestID) throws FinderException {
boolean result;
System.out.println("Entering ejbFindByPrimaryKey, TestID = " + TestID);



try {
result = selectByPrimaryKey(TestID);
} catch (Exception ex) {
throw new EJBException("ejbFindByPrimaryKey: " +
ex.getMessage());
}

if (result) {
System.out.println("Leaving ejbFindByPrimaryKey, result = " + result + "TestID = " + TestID);
return TestID;
}
else {
throw new ObjectNotFoundException
("Row for id " + TestID + " not found.");
}




}

public void ejbRemove() {
System.out.println("Entering ejbRemove, TestID = " + TestID);
try {
deleteRow(TestID);
} catch (Exception ex) {
throw new EJBException("ejbRemove: " +
ex.getMessage());
}
System.out.println("Leaving ejbRemove, TestID = " + TestID);
}

public void setEntityContext(EntityContext context) {
System.out.println("Entering setEntityContext, TestID = " + this.TestID);
this.context = context;

try {
//makeConnection();
} catch (Exception ex) {
throw new EJBException("Unable to connect to database. " +
ex.getMessage());
}

System.out.println("Leaving setEntityContext, TestID = " + this.TestID);
}

public void unsetEntityContext() {
System.out.println("Entering unsetEntityContext, TestID = " + TestID);

try {


//JPool.returnConnection("mydemo", con);
//con.close();
} catch (Exception ex) {
throw new EJBException("unsetEntityContext: " + ex.getMessage());
}
System.out.println("Leaving unsetEntityContext, TestID = " + TestID);

}

public void ejbActivate() {
System.out.println("Entering ejbActivate, TestID = " + TestID);
TestID = (String) context.getPrimaryKey();
System.out.println("Leaving ejbActivate, TestID = " + TestID);
}

public void ejbPassivate() {
System.out.println("Entering ejbPassivate, TestID = " + TestID);
TestID = null;
System.out.println("Leaving ejbPassivate, TestID = " + TestID);
}

public void ejbLoad() {
System.out.println("Entering ejbLoad, TestID = " + TestID);
try {
loadRow();
} catch (Exception ex) {
throw new EJBException("ejbLoad: " +
ex.getMessage());
}

System.out.println("Leaving ejbLoad, TestID = " + TestID);
}

public void ejbStore() {
System.out.println("Entering ejbStore, TestID = " + TestID);
try {
storeRow();
} catch (Exception ex) {
throw new EJBException("ejbLoad: " +
ex.getMessage());
}
System.out.println("Leaving ejbStore");
}

public void ejbPostCreate (String TestID, String TestName, int TestNumber) {
System.out.println("Entering and leaving ejbPostCreate, TestID = " + TestID);
}



/***************************Database Routine ***********************************/



private void insertRow (String TestID, String TestName, int TestNumber) throws SQLException, RemoteException {
Connection con = null;
Statement st = null;
System.out.println("Entering insertRow, TestID = " + TestID);
try {

InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();

String insertStatement =
"insert into Test values (?, ? , ? )";
PreparedStatement prepStmt =
con.prepareStatement(insertStatement);

prepStmt.setString(1, TestID);
prepStmt.setString(2, TestName);
prepStmt.setInt(3, TestNumber);


prepStmt.executeUpdate();

prepStmt.close();

//st = con.createStatement();
//st.executeUpdate("INSERT INTO TEST ( TestID, TestName, TestNumber ) VALUES ( " + TestID + ", " + TestName + ", " + TestNumber + " )" );
System.out.println("Leaving insertRow, TestID = " + TestID);



} catch(Exception ex) {
System.out.println("Error in insertRow = " + ex);
throw new RemoteException(ex.getMessage());

} finally {
con.close();
}

}

private boolean selectByPrimaryKey(String TestID)
throws SQLException {
boolean result=false;
Statement st = null;
Connection con = null;
ResultSet rs = null;
try {
System.out.println("Entering selectByPrimaryKey, TestID = " + TestID);

InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();


String selectStatement =
"select TestID " +
"from Test where TestID = ? ";
PreparedStatement prepStmt =
con.prepareStatement(selectStatement);
prepStmt.setString(1, TestID);

rs = prepStmt.executeQuery();
//System.out.println("connection count in selectByPrimaryKey= " + JPool.getPool().getConnectionCount("mydemo"));
result = rs.next();
prepStmt.close();

result = rs.next();

// st = con.createStatement();
// rs = st.executeQuery(" Select TestID from Test where TestID = " + TestID);
// result = rs.next();


} catch (Exception ex) {
System.out.println("Error in selectByPrimaryKey" + ex);

} finally {
con.close();
}
System.out.println("Leaving selectByPrimaryKey, TestID = " + TestID);

return result;
}

private void deleteRow(String TestID) throws Exception {
Statement st = null;
Connection con = null;
try {
System.out.println("Entering deleteRow, TestID = " + TestID);

InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();

String deleteStatement =
"delete from Test where TestID = ? ";
PreparedStatement prepStmt =
con.prepareStatement(deleteStatement);

prepStmt.setString(1, TestID);
prepStmt.executeUpdate();

prepStmt.close();




// st = con.createStatement();
// st.executeUpdate("DELETE FROM TEST WHERE TESTID = " + TestID);


System.out.println("Leaving deleteRow, TestID = " + TestID);
} catch (Exception ex) {
System.out.println("Error in deleteRow = " + ex);

} finally {
con.close();

}

}
/*
private void makeConnection() throws NamingException, SQLException {
System.out.println("makeConnection, TestID = " + TestID);
try {
//Direct connection to database
//Class.forName("com.inet.tds.TdsDriver").newInstance();

//Class.forName("com.inet.tds.XDataSource").newInstance();
//con = DriverManager.getConnection("jdbc:inetdae7:localhost:1433?database=test", "tj", "arlo97");

//Using JPool
con = JPool.getConnection("mydemo");

} catch(Exception ex) {
System.out.println("Error in makeConnection()=" + ex);
}
//Connection using JNDI
//InitialContext ic = new InitialContext();
// DataSource ds = (DataSource) ic.lookup(dbName);
// con = ds.getConnection();
System.out.println("Leaving makeConnection, TestID = " + TestID);
}

*/
private void loadRow() throws SQLException {
Connection con = null;
ResultSet rs = null;
Statement st = null;
try {
System.out.println("Entering loadRow, TestID = " + TestID);

InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();

String selectStatement =
"select TestName, TestNumber " +
"from Test where TestID = ? ";
PreparedStatement prepStmt =
con.prepareStatement(selectStatement);

prepStmt.setString(1, this.TestID);

rs = prepStmt.executeQuery();

// st = con.createStatement();
// rs = st.executeQuery(" Select TestID, TestName, TestNumber from Test where TestID = " + TestID);




if (rs.next()) {
this.TestName = rs.getString(1);
this.TestNumber = rs.getInt(2);

}
else {

throw new NoSuchEntityException("Row for TestID " + TestID +
" not found in database.");
}


System.out.println("Leaving loadRow, TestID = " + TestID);
} catch (Exception ex) {
System.out.println("Error in loadRow = " + ex);
} finally {
con.close();

}


}


private void storeRow() throws SQLException {
Connection con = null;
Statement st = null;
try {
System.out.println("Entering storeRow");

InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
con = ds.getConnection();

String updateStatement =
"update Test set TestName = ? ," +
"TestNumber = ? " +
"where TestID = ?";
PreparedStatement prepStmt =
con.prepareStatement(updateStatement);

prepStmt.setString(1, TestName);
prepStmt.setInt(2, TestNumber);
prepStmt.setString(3, TestID);
int rowCount = prepStmt.executeUpdate();
prepStmt.close();

if (rowCount == 0) {
throw new EJBException("Storing row for TestID " + TestID + " failed.");
}


// st = con.createStatement();
// st.executeUpdate("update Test set TestName = " + TestName + ", TestNumber = " + TestNumber + " where TestID = " + TestID);


System.out.println("Leaving storeRow");

} catch(Exception ex) {
System.out.println("Error in storeRow = " + ex);

} finally {
con.close();

}//finally

} //storeRow

}

this is the jsp page that access the bean
=========================================


<%@ page import="javax.naming.InitialContext,
javax.naming.Context,
java.util.*,
Test,TestHome, java.lang.*"%>
<%

long t1 = System.currentTimeMillis();
String time = "";
String TestID = "";
String TestName = "";
int TestNumber = 0;
StringBuffer buffer = new StringBuffer();
if(request.getParameter("TestID") != null) {
TestID = request.getParameter("TestID");
TestName = request.getParameter("TestName");
TestNumber = Integer.parseInt(request.getParameter("TestNumber"));


Properties props = new Properties();
props.put(Context.INITIAL_CONTEXT_FACTORY,
"org.jnp.interfaces.NamingContextFactory");
props.put(Context.PROVIDER_URL, "localhost:1099");

Context ctx = new InitialContext(props);
TestHome home = (TestHome)ctx.lookup("ejb/Test");


System.out.println("Test home = " + home.toString());
String a= TestID + "";
String b = TestName + "";


Test bean = null;
try{
bean = home.create(a , b, TestNumber);
}
catch(Exception e){ throw new ServletException(e.getMessage()); }
//Test bean = home.create("1");

Test test1 = home.findByPrimaryKey(a);
System.out.println("TestID = " + test1.getTestID());
System.out.println("Test Name = " + test1.getTestName());
System.out.println("TestNumber = " + test1.getTestNumber());

buffer.append("TestID = " + test1.getTestID() + "\n" );
buffer.append("Test Name = " + test1.getTestName() + "\n");
buffer.append("TestNumber = " + test1.getTestNumber() + "\n");



ctx.close();

}
long t2 = System.currentTimeMillis();



%>


p { font-family:Verdana;font-size:12px; }




Test ID :

Test Name:

Test Number:


 

Result
======
<%=buffer.toString()%>


Message received from bean = "<%= time %>".Time taken :
<%= (t2 - t1) %> ms.