Hi,

This is summary of the conversation with the David Hay(?) in the
Firebird-Java list. The original issue assumed that Jaybird did not handle
the TPB correctly.

David tried to reserve table in isc_tpb_concurrent mode following the
example in the Helen's book and tried them in isql and they seemed to work
(I did not check it myself). Same stuff in Jaybird works differently.

I have tried to reproduce his examples in Java and later in C++. The test
execution flow is the following:

a. open two connections to the database
b. start transaction 1
c. execute either SELECT or UPDATE for row with ID=1 in tx 1
d. start transaction 2
e. execute either SELECT or UPDATE for row with ID=2 in tx 2
f. depending on switch commit transaction 2 then 1, or first 1, then 2

Detailed results are presented below. Please note that it does not depend
on isc_tpb_wait / isc_tpb_nowait setting.

- For the isc_tpb_concurrency lock conflict is reported on commit of the
second transaction.

- The isc_tpb_consistency causes conflict when the second transaction is
started and tries to reserve same table (I have also tested without table
reservation, lock conflict is reported on first UPDATE in tx2).

Can somebody please comment whether the behavior is as expected? If
somebody's interested, I can send C++ code for the tests.

Thanks!
Roman

1. - tx1=isc_tpb_consistency, tx2=isc_tpb_consistency,
- both reserve protected lock for write for the same table
- both execute UPDATEs

Result: lock conflict on step c.

2. - tx1=isc_tpb_consistency, tx2=isc_tpb_concurrency
- rest same as in case 1

Result: lock conflict on step c.

3. - tx1=isc_tpb_concurrency, tx2=isc_tpb_concurrency
- both reserve protected lock for write for the same table
- both execute UPDATEs
- first is committed tx2, then tx1

Result: lock conflict when committing tx1

4. - tx1=isc_tpb_concurrency, tx2=isc_tpb_concurrency
- both reserve protected lock for write for the same table
- both execute SELECTs
- first is committed tx1, then tx2 (opposite to test 3)

Result: lock conflict when committing tx2

5. - tx1=isc_tpb_concurrency, tx2=isc_tpb_concurrency
- tx1 reserves protected lock for write, tx2 protected lock for read for
the same table
- both execute SELECTs
- first is committed tx2, then tx1

Result: lock conflict when committing tx1

6. - tx1=isc_tpb_concurrency, tx2=isc_tpb_concurrency
- tx1 reserves protected lock for write, tx2 protected lock for *read*
for the same table
- tx1 executes UPDATE, tx2 executes SELECT
- first is committed tx2, then tx1

Result: lock conflict when committing tx1

7. - tx1=isc_tpb_concurrency, tx2=isc_tpb_concurrency
- tx1 reserves protected lock for write, tx2 *shared* lock for *read*
for the same table
- tx1 executes UPDATE, tx2 executes SELECT
- first is committed tx2, then tx1

Result: lock conflict when committing tx1

8. - tx1=isc_tpb_concurrency, tx2=isc_tpb_concurrency
- both reserve *shared* lock for *read* for the same table
- both execute SELECTs
- first is committed tx2, then tx1

Result: lock conflict when committing tx1