SQL Server 2000 Lock problem
chriscoy May 25, 2002 6:29 PMI have a stateless session bean that atomically creates a reference to a pooled MS JDBC driver connection. I close the resultset early, and close and set null all of the database variables before the function ends. However, once the connection starts, the table that it queries remains locked, I can't query it, I can't even look at my processes in SQL Server Enterprise Manager.
I am using JBoss3.0RC2 and the latest JDBC driver from Microsoft. Why does the pool keep the table locked??
Here are my files: xxservice.xml --
<?xml version="1.0" encoding="UTF-8"?>
MSSQLDbRealm
<depends optional-attribute-name="ManagedConnectionFactoryName">
<!--embedded mbean-->
SQLSRV
<config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=tribes2stats;SelectMethod=cursor</config-property>
<config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>
<config-property name="UserName" type="java.lang.String">jboss</config-property>
<config-property name="Password" type="java.lang.String">gamestats</config-property>
<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper
<depends optional-attribute-name="ManagedConnectionPool">
<!--embedded mbean-->
0
2
500
1
ByContainer
<depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager
<depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager
java:/TransactionManager
<!--make the rar deploy! hack till better deployment-->
jboss.jca:service=RARDeployer
NEXT is the function that calls the pool:
public Vector getServers() {
ResultSet rs=null;
int i=0;
int iCount=0;
String sType="";
clientInfo cI=null;
Vector clients = new Vector();
System.out.println("starting getServers");
try {
jndiContext = new InitialContext();
ds = (DataSource) jndiContext.lookup("java:/SQLSRV");
connection = ds.getConnection();
stmt = connection.createStatement();
System.out.println("getting servers in bean");
rs = stmt.executeQuery("exec spGetServers");
boolean hasNext = true;
while(hasNext) {
hasNext = rs.next();
System.out.println("moved cursor, result was " + hasNext);
if(hasNext == false) {
break;
}
iCount=rs.getInt("countof");
String address = rs.getString("serveraddress");
String port = rs.getString("port");
String password = rs.getString("serverlistenpassword");
String status = rs.getString("status");
String serverid = new Integer(rs.getInt("serverid")).toString();
String servername = rs.getString("servername");
cI = new clientInfo(address,
port,
password,
status,
serverid,
servername);
System.out.println("adding element");
clients.addElement(cI);
}
rs.close();
rs = null;
stmt.close();
stmt = null;
connection.close();
connection = null;
ds = null;
System.out.println("released db");
} catch (Exception e) {
try {
e.printStackTrace();
rs = null;
stmt.close();
connection.close();
} catch(Exception ex) {
e.printStackTrace();
}
} finally {
return (Vector) clients.clone();
}
}