On Sun, 21 Mar 2004, Roman Rokytskyy wrote:

> > I have problem using transaction with JayBird for 1.5-beta-3-jdk14.
> > FireBird version is 1.5 for Windows. There are two connections in
> > the example, where one perfroms a select and then an update. The
> > second one updates the table between the select and update of the
> > first connection. I think such a code should cause deadlock
> > exception, but it succeeds. Please can you help me?
>
> In Firebird select statement does not obtain write lock on the row
> unless explicitly stated (SELECT ... FROM ... FOR UPDATE WITH LOCK).
> In your example you have one transaction (connection 2) doing update
> and then commit, and then another one (connection 1) doing update and
> commit. There's no conflict, therefore no deadlock is reported.
>
> Also note, that by default isc_tpb_wait mode is used, this means that
> deadlock will be reported after quite long timeout (if I'm not wrong,
> after 10 min.). If you wish to be notified about deadlock immediately,
> you have to change TPB mapping to isc_tpb_nowait.
>
> Best regards,
> Roman Rokytskyy
>
>

Thanks for explanation. I did some more experiments, and I found another
problem, which I don't understand. I'am doing two concurrent selects from
two connections and then I update the selected field to be one more than
the selected value. This is again from both connections. The total
increment should be of two but it is just one. If I try the same thing
from two isql consoles in the same order, it reports a deadlock, but
JayBird does not. Is it a bug?

Regards
Mirek.

Here is the code:

import java.sql.*;

public class TestTransact {
static String databaseUrl = "jdbc:firebirdsql://127.0.0.1/piskovna";
static void init() {
try {
Class.forName("org.firebirdsql.jdbc.FBDriver");
Connection conn1 = DriverManager.getConnection(databaseUrl, "SYSDBA", "masterkey");
Connection conn2 = DriverManager.getConnection(databaseUrl, "SYSDBA", "masterkey");
Statement stmt1 = conn1.createStatement();
Statement stmt2 = conn2.createStatement();
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
ResultSet rs;

// set value "hmotnost" to zero
stmt1.execute("UPDATE vozidla SET hmotnost=0 WHERE id=1;");
conn1.commit();

// select from the first connection
rs = stmt1.executeQuery("select * from vozidla where id = 1;");
rs.next();
float m1 = rs.getFloat("hmotnost");

// select from the second connection
rs = stmt2.executeQuery("select * from vozidla where id = 1;");
rs.next();
float m2 = rs.getFloat("hmotnost");

// increment hmotnost from the first connection
stmt1.execute("update vozidla set hmotnost=" + (m1+1) + " where id = 1;");
conn1.commit();

// increment hmotnost from the second connection
// this should fail, but it succeeds
stmt2.execute("update vozidla set hmotnost=" + (m2+1) + " where id = 1;");
conn2.commit();

// print the changed value
rs = stmt1.executeQuery("select * from vozidla where id = 1;");
while (rs.next()) {
System.out.println(rs.getFloat("hmotnost")); //should be 2 but it is 1
};

stmt1.close();
stmt2.close();
conn1.close();
conn2.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static void main(String[] args) {
init();
}
}