Subject RE: [firebird-support] update, foreign keys, locking
Author unordained

If transaction A has a record locked for update at the time when your transaction, B, attempts to
update it ... chances are that you don't want to commit your changes if A commits. The standard
examples usually include some sort of financial example: transaction A decreases the amount of
money left in your account by $50, then B attempts to take another $25 out ... A commits, B manages
to make its changes anyway ... and total, your account went down by $25, not $75. In our
application, we use inserted rows of financial data -- we have no summary row of "amount left", so
it's not a problem, -except- for something like the following: transaction A looks at how much you
have left (by sum() of existing row) and decides you have plenty, and withdraws $50; transaction B
looks at rows, sees you have plenty, and withdraws $25. both commit. problem is that you had $55
left: because of transaction isolation, B didn't see what was going on. there was no record lock to
worry about. both inserts happened. A solution to -that- problem is to lock a parent record (like
an account # record, a person record, or several) to make sure the reasoning behind a decision is
protected by write-locks for the duration of your transaction. Slows things down, but ... eh ...
it's safer.

Your choices in FB are to wait for the conflict to go away, or not to. It does not (that i know of)
give you the option of -ignoring- the lock and automatically posting your changes even if the other
transaction commits. You can fail (with NOWAIT) and keep retrying ever X milliseconds, if you must.
Seems like you'd pound the db too much though.

You could also submit updates of this sort to a central mechanism for doing updates ... let them be
done serially, as soon as the previous update commits. No multi-connection locks, no problem. That,
however, just sounds like a disaster waiting to happen.

Can you give us your particular example to look at?

(btw, Alexander Nevsky -- we do our dummy update on the same transaction as the one that commits
the actual changes. we do this sort of locking because our program is primarily an online system
with lots of little data-entry ladies slowly typing in things like address changes. they're not
good about coordinating their changes ... and they'd be really angry at us if they were entering
their changes and -then- got a warning about someone else updating the record. then they'd have to
see the changes made to find out if their changes are still relevant ... which is just nasty on the
GUI. "old stuff, their stuff, my stuff" ... we prefer to just not let them make changes unless they
get the exclusive lock. they're irritated when someone leaves it locked and walks off for lunch,
but that's rather rare. we could add a notification system so they know who to call and yell at
[when they get back from lunch, that is.]! if we were doing UPDATES as they typed into fields on
the screen, it'd come down to the same sort of thing. the first keystroke would lock the record for
update. in our case, they push a button to start updating it ... then get to type. i figure,
transactionally, it comes down to the same thing.)


> And what would be the best course of action if the application wants to
> permit the user to make that update anyway? The obvious answer is
> probably a 'hard refresh', meaning close the dataset, commit the
> transaction, reopen the transaction, reopen the dataset, and retry the
> operation.
> But is there something easier? Will a refresh of the current (modified)
> row do?
> Thanks in advance,
> Salam,
> sugi.