Deadlock with concurrent transactions

[Ting Ning]

I got the problem when I run multiple concurrent
transactions on SQL Server through MS JDBC Driver. The
error is:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC][SQLServer]Transaction (Process ID 54) was
deadlocked on {lock} resources with another process and
has been chosen as the deadlock victim. Rerun the
transaction.
at com.microsoft.jdbc.base.BaseExceptions.createException
(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getException
(Unknown Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorTok
en(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyTok
en(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReply
Token(Unkn
own Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply
(Unknown Sour
ce)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNext
ResultType
(Unknown Source)
at
com.microsoft.jdbc.base.BaseStatement.commonTransitionToSta
te(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.postImplExecute
(Unknown Source)

at
com.microsoft.jdbc.base.BasePreparedStatement.postImplExecu
te(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.commonExecute
(Unknown Source)
at
com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal
(Unknown S
ource)
at
com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate
(Unknown S
ource)
at TestObject.process(TestObject.java:53)
at Main$TestThread.run(Main.java:62)
at java.lang.Thread.run(Thread.java:536)

My test env:
OS: Window 2000
JDK 1.4
JDBC Driver: MS JDBC Driver (latest release)
DB: MS SQL Server with service pack 2

The test is very simple. The Main test creates multiple
threads to do: select, delete, insert, select and update.
Each thread repeats the above instructions.

Usually, the test runs well in the first. After a while
(couple of minutes for 5 threads), one of the thread dead.
And deadlock error comes out. The same test runs well
aginst Oracle db.

Would someone see this before?

I attach the files here for your references.

Thanks in advance,
Ting
ting...@yahoo.com

PS. You can run the test in command line.
java Main <num of threads>

Main.java
---------------------------------------------

import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.util.*;

/**
* Author: Ting Ning
*/

public class Main
{
public static void main(String[] args)
{
try
{
int num = 3;

if (args.length > 0)
num = Integer.parseInt(args[0]);

Main test = new Main();

ArrayList list = new ArrayList();
for (int i=0; i<num; i++)
{
Thread t = new Thread(test.new TestThread(i));
t.start();
list.add(t);
Thread.sleep(100);
}

Iterator it = list.iterator();
while (it.hasNext())
{
Thread t = (Thread) it.next();
t.join();
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}

private class TestThread implements Runnable
{
private int ind;

public TestThread(int ind)
{
this.ind = ind;
}

public void run()
{
try
{
TestObject testObj = new TestObject(ind);
while (true)
{
testObj.process();
Thread.sleep(100);
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}
}

----------------------------------------
End of Main.java

[Joseph Weinstein]

Hi. This issue is DBMS-related. JDBC and the driver may not
be involved. There are major differences between MS SQLServer
and Oracle in how and what the DBMS will lock during a transaction.

You should ask SQLServer experts, but it seems that all your threads
are logically separate, to in theory all should be fine. My concerns
are:

1 - Historcally MS SQLServer was page-level locking, so
a given transaction might inadvertantly lock some non-related
data that happened to reside on the same page. I would
hope that MS SQLServer 2000 is beyond this by default,
but you should verify the details.
2 - This driver requires cursor mode queries if you are to have
multiple statements open on a given connection. There may
be extra locking contention on index pages in the DBMS,
owing to the maintenance of DBMS-side cursors.

Try altering your test class to close each statement as soon
as you're done with it, and don't use selectMode=cursor,
and let us know if it behaves differently.

Joe Weinstein at B.E.A.

Ting Ning wrote:

> ----------------------------------------------------------------------------------------------------
> Name: TestObject.java
> TestObject.java Type: Plain Text (text/plain)
> Encoding: quoted-printable

[Ting Ning]

This is a DBMS related. If there are two threads, the test
usually runs well. If the number of threads increase, the
deadlock most likely happens. It may be some thing wrong
inside SQL Server when there are lost of concurrent
transaction going on.

I did small modifications to close the statement
immediately after it is done. But the test result is same.

I can not remove selectMode=cursor, otherwise the
transaction does not work.

Ting

>> Would somÍ{ wÀ D ô G¤. G$[Ä
> ì¹ eone see this before?

[Joseph Weinstein]

Ting Ning wrote:

> This is a DBMS related. If there are two threads, the test
> usually runs well. If the number of threads increase, the
> deadlock most likely happens. It may be some thing wrong
> inside SQL Server when there are lost of concurrent
> transaction going on.
>
> I did small modifications to close the statement
> immediately after it is done. But the test result is same.
>
> I can not remove selectMode=cursor, otherwise the
> transaction does not work.

I see where you use selStmt twice, so try this:

import java.io.*;
import java.sql.*;
import java.util.*;

public class TestObject
{
Connection con;
private int ind;

public TestObject(int ind)
throws Exception
{
this.ind = ind;

String driverClass = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String dbUrl = "jdbc:microsoft:sqlserver://localhost:1433";

String dbUser = "test";
String dbPassword = "test";

Class.forName(driverClass);
con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
}

public void process()
throws Exception
{
System.out.println(Thread.currentThread().getName()+":begin");

con.setAutoCommit(false);

// Select
PreparedStatement selStmt = con.prepareStatement("select id, text from test where id =?");
selStmt.setInt(1, ind);
System.out.println(Thread.currentThread().getName()+":select 1");
ResultSet rs = selStmt.executeQuery();
int id = 0;
String text = null;
while (rs.next())
{
id = rs.getInt("id");
text = rs.getString("text");
System.out.println("id = " + id + ", text = " +text);
}
rs.close();
selStmt.close();

// Delete
PreparedStatement delStmt = con.prepareStatement("delete test where id = ?");
delStmt.setInt(1, ind);
System.out.println(Thread.currentThread().getName()+":delete");
delStmt.executeUpdate();
delStmt.close();

// Insert
PreparedStatement insStmt = con.prepareStatement("insert into test(id, text) values (?, ?)");
insStmt.setInt(1, ind);
insStmt.setString(2, "Test " + ind);
System.out.println(Thread.currentThread().getName()+":insert");
insStmt.executeUpdate();
insStmt.close();

// Select
System.out.println(Thread.currentThread().getName()+":select 2");
selStmt = con.prepareStatement("select id, text from test where id =?");
selStmt.setInt(1, ind);
rs = selStmt.executeQuery();
while (rs.next())
{
id = rs.getInt("id");
text = rs.getString("text");
System.out.println("id = " + id + ", text = " +text);
}
rs.close();
selStmt.close();

// Update
PreparedStatement updStmt = con.prepareStatement("update test set text= ? where id = ?");
updStmt.setString(1, "Update text " + ind);
updStmt.setInt(2, ind);
System.out.println(Thread.currentThread().getName()+":update");
updStmt.executeUpdate();
updStmt.close();

System.out.println(Thread.currentThread().getName()+":commit");
con.commit();
}
}

[Ting Ning]

I change the codes as below to do the test. For 3 threads,
the deadlock error come out later. For 5 threads, I don't
see the difference. For two threads, there is no deadlock
error for a long time.

So, the more threads (concurrent transactions), the more
easy to get deadlock error.

>.
>

[Ting Ning]

I change the codes as below to do the test. For 3 threads,
the deadlock error come out later. For 5 threads, I don't
see the difference. For two threads, there is no deadlock
error for a long time.

So, the more threads (concurrent transactions), the more
easy to get deadlock error.

>-----Original Message-----
>
>

[Joseph Weinstein]

Ok! This means the cursor mode does contribute, but is
not the sole cause. Now you need to ask the SQLServer
experts about what you need to do to configure the
table and indexes so logically independent transactions
will never lock each others data.
Joe

[Ting Ning]

This looks like a SQL server bug for me. For small number
of thread (2 or 3), it runs for a while to get the
deadlock. At least, the first couple of transactions pass.
After some time running, the SQL server internal detect
the deadlock. One possible reason is that it is a SQL
server bug.

Can an expert from Microsoft response this problem?

>> > SysteÍ{ wÀ Q ì OÌ- OOµz ìL m.out.println