Subject Re: [firebird-support] Re: update, foreign keys, locking
Author unordained
I believe in Sugi's question, the intention was that the second transaction would attempt to update
the record before the first one had committed -- with the intent that somehow the deadlock could be
ignored (not wait for commit/rollback, not time out, but just wait until commit, then commit
changes despite the record having been updated during the lifetime of the second transaction.) I
think that's a logical problem ... and have no idea why you'd want to do that.

And yes -- we actually re-fetch the record that is about to be edited as the lock is acquired: when
they're editing text, it's always the latest (get the lock first [which is when users find out if
the record is already being updated elsewhere, and the only place we care about deadlock], then
select on the transaction that will commit changes later.) We also have a rather odd refresh
notification system to make any open screens refresh with changes, if the changes are relevant.
(programmer's note: no matter how flexible you think you've made a system to remember who wants
what ... callbacks that can be overriden elsewhere to just decide on the fly what they want are
always even better.)

However, all of this still assumes that we don't want to save changes regardless of any other
changes made by other transactions during our transaction's lifetime. Sugi's question is still
open. I don't believe that using an update statement with a += operator is going to change FB's
opinion about what you're attempting to do -- as the first transaction hasn't committed yet, even
read-committed will still see the old value of the field, and attempt to set it to a new value
based on the old one ...

So, did we just give up on ramdisk stuff again? Is the concensus "we don't know, but it sounds


> Philip, to avoid this without pre-locking you can:
> 1. Use transaction with concurrency isolation level and refresh record
> to be changed in it's context. If so, you'll always have on the screen
> data which were commited before your transaction start and if it will
> be changed during it's life you always will get lock conflict.
> 2. In this example you can use read_commited, but your updates
> should'nt be
> Update Update Accounts Set Money_Left=New_Value
> but
> Update Update Accounts Set Money_Left=Money_Left+:Increment_Decrement