On Sun, 21 Mar 2004, Roman Rokytskyy wrote:

> > 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?
>
> Do you issue commit after each update in isql? Your Java code simply
> does not have any update conflict, so there cannot be any deadlock.
> Move your commits to the end of the code before closing statements and
> you will see what you want (again, do not forget to use
> isc_tpb_nowait, otherwise you will have to wait quite a long time).
>
> What are you trying to test? If you want to know if driver+server
> handles deadlocks correctly, check
> org.firebirdsql.jdbc.TestFBConnection class, there's test case for
> exactly this topic.
>
> Best regards,
> Roman Rokytskyy
>

I appologize asking the same thing again and again, but I would like to
make sure that I understand database transactions well.

I hope I am doing exactly the same operations in isql as in Java, but
results are different. Why?

In the Java example if two transaction performs separately, the result is
different than if they run in parallel. As I know the transaction should
avoid such differences.

Mirek.


Here is the record from my interaction with two isql consoles isql1 and
isql2:


-------- isql1:
SQL> select hmotnost from vozidla where id =1;

HMOTNOST
============

0.00

SQL>

-------- isql2:
SQL> select hmotnost from vozidla where id =1;
HMOTNOST
============

0.00

SQL>


-------- isql1:
SQL> update vozidla set hmotnost=1 where id=1;
SQL> commit;
SQL>

-------- isql2:
SQL> update vozidla set hmotnost=1 where id=1;
Statement failed, SQLCODE = -913

deadlock
-update conflicts with concurrent update
SQL>


Here is the relevant part of code in Java:

// 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();