Deadlock occurs on my application frequently

Hi, all!

Brief description of system environment:
-----------------------------------------
1. Environment: JDK 5.0, CTP JDBC Driver 1.1, Proxool 0.9.0RC2
(http://proxool.sourceforge.net/), Windows 2000 Server (SP4)
2. Database: SQL Server 2005 (SP1), Windows 2003 Server


Java code:
-----------------------------------------
In order to describe the situation I have simplified all the codes.


CREATE TABLE [dbo].[rss](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[url] [nchar](100) NOT NULL,
[title] [nchar](100) NOT NULL
) ON [PRIMARY]


First, the createRequest method is to fetch all names from the
database.
private void createRequest(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;


try{
while(true){
if(conn == null){
// dbPool is a connection pool by
Proxool
conn = dbPool.getConnection();
conn.setAutoCommit(false);
}


String sql = "SELECT url FROM rss ORDER BY id
ASC";
stmt = conn.createStatement();
stmt.setMaxRows(100);
rs = stmt.executeQuery(sql);
boolean haveData = false;
while(rs.next()){
String url = rs.getString("url");
new Thread(new UpdateRss(url)).start();

haveData = true;
}
if(haveData)
conn.commit();
else
break;


try { Thread.sleep(50); } catch
(InterruptedException e) { }
}


} catch(Exception e){
e.printStackTrace();
try{ conn.rollback(); } catch(SQLException e1) { }
} finally {
if(rs != null){
try { rs.close(); } catch (SQLException e) { }
rs = null;
}
if(stmt != null){
try{ stmt.close(); } catch(SQLException e){ }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) {
}
conn = null;
}
}



}


In the UpdateRss class, the run method code as below:
public void run(){
// Get title from the RSSParser
String title = RSSParser.getTitle(url);

Connection conn = null;
Statement stmt = null;
try{
conn = dbPool.getConnection();
conn.setAutoCommit(false);
String sql = "UPDATE rss SET title='" + title + "')
WHERE url='" +
url + "'";
stmt = conn.createStatement();
stmt.executeUpdate(sql); // Here is Line 343
conn.commit();
} catch (Exception e){
e.printStackTrace();
try{ conn.rollback(); } catch(SQLException e1) { }
} finally {
if(stmt != null){
try{ stmt.close(); } catch(SQLException e){ }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) {
}
conn = null;
}
}



}


Problems:
-----------------------------------------
When I run the application, the exception below will be catched
frequently:

com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process
ID 56) was deadlocked on [lock] resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unkno­wn

Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown

Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse­(Unknown

Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedSt­atement(Unknown

Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementEx­ecutionRequest.execute(Unknown

Source)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown

Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unkno­wn

Source)
at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImp­l.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)
at
org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at
org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at
$java.sql.CallableStatement$$EnhancerByCGLIB$$aa312a36.executeUpdate(<gener­ated>)

at
com.caimogu.rssspider.request.RSSRequest.updateRss(RSSRequest.java:343)

at com.caimogu.rssspider.SpiderWorker.run(SpiderWorker.java:30)

at java.lang.Thread.run(Thread.java:595)


Any ideas? Thanks a lot.


Regards,
Jet Mah

.

Relevant Pages