Subject Re: update, foreign keys, locking
Author Alexander V.Nevsky
--- In, "unordained"
<unordained_00@c...> wrote:
> 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.

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


Update Update Accounts Set Money_Left=Money_Left+:Increment_Decrement

> (btw, Alexander Nevsky -- we do our dummy update on the same
transaction as the one that commits
> the actual changes.

If so, you should'nt get lock conflict after successful locking.

> 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.

IMO editing large texts is proper occurance to use pessimistic

Best regards, Alexander.