Re: Deadlock occurs on my application frequently

Jet Mah wrote:
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.

You are using multiple threads that access the same data in different orders (typical deadlock situation). Here's what you can do:

1. make a profiler trace to see exactly what happens.

2. prevent the deadlock

2.a) use another transaction isolation

2.b) (preferred) change your application in a way that those accesses do not occur. One way is to save all urls in a collection and create all those threads *after* you close the result set and statement.

Personally I also prefer more local finally blocks, i.e.

Statement st = conn.createStatement();
try {
ResultSet rs = st.execute("select something");

try {
while (rs.next()) {
...
}
}
finally {
close(rs);
}
}
finally {
close(st);
}

This properly nests cleanup code.

Btw, also I don't see why you switch off autocommit for the SELECT. It's just one statement anyway. Also you don't need a commit for the select as there are no changes.

Kind regards

robert
.

Relevant Pages

  • Re: Deadlock occurs on my application frequently
    ... Jet Mah wrote: ... Connection conn = null; ... Statement stmt = null; ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Deadlock occurs on my application frequently
    ... Connection conn = null; ... Statement stmt = null; ... been chosen as the deadlock victim. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Deadlock occurs on my application frequently
    ... the createRequest method is to fetch all names from the ... Connection conn = null; ... Statement stmt = null; ...
    (comp.lang.java.databases)
  • Deadlock occurs on my application frequently
    ... the createRequest method is to fetch all names from the ... Connection conn = null; ... Statement stmt = null; ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Transaction Help
    ... Its a bit strange as Delphi ADO allows all of this to happen inside the 1 transaction. ... Conn and Conn2 are both created in the Class Constructor. ... Conn2 is ONLY ever used in Exists Functions - and each of them opens and closes the connection each time. ...
    (microsoft.public.dotnet.framework.adonet)