Hi

After reading an interesting thread on the firebird-dev list this
morning, I have figured out the solution to the problem I was
experiencing.

> All threads start their own transaction, created using their own database
> object, with access mode read/write (tmWrite), read committed isolation
> (ilReadCommitted), no wait lock resolution (lrNoWait), and no special flags.
>
> I get a lock conflict on no wait transaction with the threads in the
> following state:
>
> Thread 1:
> INSERT INTO MyTable ( MyKey ) VALUES ( 'A' )
> * IBPP::Query::Execute has not yet returned
>
> Thread 2:
> INSERT INTO MyTable ( MyKey ) VALUES ( 'B' )
> * completed
> SELECT * FROM MyTable WHERE MyKey = 'B'
> * Lock conflict on no wait transaction occurs here
>
> Thread 3:
> INSERT INTO MyTable ( MyKey ) VALUES ( 'C' )
> * IBPP::Query::Execute has returned
>
> Looking at the Firebird book, this situation doesn't seem to match the first
> case for lock conflicts - conflicting UPDATE or DELETE statements in
> multiple transactions. The other case, which is caused by using SNAPSHOT
> TABLE STABILITY or PROTECTED transaction isolation, doesn't seem to match
> either. Changing the transaction isolation to snapshot (ilConcurrency) means
> that the lock conflict doesn't happen.

There is a third case, not mentioned in my edition of the Firebird
book, in which lock conflicts can arise: if you have a read committed
no wait transaction started with NO RECORD_VERSION (and not NO RECORD
VERSION as in the book). NO RECORD_VERSION is the default behaviour
for read committed transactions (according to my reading of the dev
list discussion). It will produce a lock conflict precisely in my kind
of situation - if you try to read from a table with outstanding
updates.

The solution, for me, is to use the IBPP::ilReadDirty transaction
isolation in IBPP - which is equivalent to read committed with record
version at the Firebird API level.

All the best
Gareth Marshall