Hi all,

I´m having some deadlocks in my application, so i created a test case to try to reproduce the problem, and i would like to know if the behaviour i´m getting is expected and what i can do to prevent it.

The test consists of creating 150 threads, where each thread does the following steps:

1- connect to a database in autocommit mode
2- update 1 random row in a 50 rows table, setting field "valor = valor + 1"
3- close the statement
4- close the connection
5- sleep between 0 and 5 seconds
6- restart over step 1

After the test begins, i start to receive deadlocks exceptions (even if i change "statement.setQueryTimeout()" to some seconds).

The table is created as below, pre-filled with 50 rows.

create table teste (
id integer not null primary key,
valor integer);

This article (http://tutorials.jenkov.com/java-concurrency/deadlock-prevention.html) says deadlocks can occur when you try to lock more than 1 record/resource in different order by more than 1 thread, but in my case i'm updating just 1 record each connection, so if two threads tries to update the same record, i expected the second thread to wait the first thread to release the row and go on, without causing a deadlock (is it possible to cause a deadlock updating a single record?).

Can someone help me to figure out what is happening?

By the way, i´m using Jaybird-2.1.6, FirebirdSS-2.1.3, Windows 7 Home and Java 1.6.0.18.

Regards,

Fabiano

Here follows the Thread code:

for (int i = 0; i < 150; i++)
new Thread() {
public void run() {
// dorme entre 0 e 10 segundos
try {
sleep(new Random().nextInt(10000));
} catch (InterruptedException e) {
e.printStackTrace();
return;
}

while (true) {
int i = new Random().nextInt(50) + 1;
try {
Connection connection = DriverManager
.getConnection(
"jdbc:firebirdsql://127.0.0.1/teste",
"SYSDBA", "masterkey");
connection.setAutoCommit(true);
PreparedStatement statement = connection
.prepareStatement("update teste set valor = valor + 1 where id = ?");
statement.setInt(1, i);
statement.execute();
statement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}

// dorme entre 0 e 5 segundos
try {
sleep(new Random().nextInt(5000));
} catch (InterruptedException e) {
e.printStackTrace();
break;
}
}
}
}.start();