At 02:23 PM 17-04-02 -0400, Fred Wilson wrote:

>Ok, we have some confusion regarding transaction isolation level modes
>(IB5.6/NT4.0/SP5).
>
>Using Snapshot or Repeatable Read, it's assumed that when txnA starts it
>get's a "snapshot" of the current database and can not see changes made by
>other transactions (also snapshot mode). For the most part this seems to
>work as expected.
>- TxnA starts a transaction
>- TxnB starts a transaction
>- TxnA reads from a table (Select * from ..... ). All is well
>- TxnB reads from the same table (Select * from ... ). All is well
>- TxnB deletes a row from the same table and commits
>- TxnA reads from the table again (Select * from ...), and all is as
>expected. TxnA still sees the "deleted" row.
>- Ok commit everthing and do, basically the same, except instead of deleting
>a row, TxnB adds a row. TxnA won't see it, as expected.
>- Ok, lastly,
>- TxnA starts a transaction
>- TxnB starts a transaction
>- TxnB updates a single row in the table but doesn't commit
>- TxnA updates the same row in the same table and the excute fails with a
>"deadlock". We would have expected the second person that calls commit to
>fail, not to fail at execution time. It doesn't matter who start their
>transaction first, the first one to actually "execute" the SQL statement
>wins (even without committing).
>What are we missing. This doesn't seem "correct"

What you described as actual behaviour seems correct. TxnB was the first
to post an update so it got the lock. When TxnA came to request an update,
it encountered the lock. Now it will have to wait until TxnB commits or
rolls back, in order to get a chance at securing a lock. Until that
happens (with NOWAIT), txnA will continue to encounter the deadlock or
(with WAIT) will wait, with no lock conflict reported, until txnB releases
the lock...not recommended.

IB's locking is optimistic - a lock is acquired by posting an update, not
by merely viewing the row. Regardless of who was first to get a snapshot,
neither gets any priority for acquiring the lock - it's first come, first
served.

cheers,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________