Subject Re: update, foreign keys, locking
Author Alexander V.Nevsky
--- In, "unordained"
<unordained_00@c...> wrote:
> Unless I'm just off my rocker, I seem to remember that if you start
updating a row, rows referenced
> by foreign key values will also get locked on that same transaction
(or at least attempt to do so --
> some sort of read-lock?) In our case, this meant that if you're in
the middle of updating
> someone's SSN, and you try to also update their address
(popup-screen of dependent record on a
> different transaction) you'll get a deadlock (we use NOWAIT, so it's
at least not too irritating.)
> (when someone starts editing, we explicitely do 'update {tablename}
set id = id where id =
> {value}' -- this conflicts with parent records already being edited.
as we've already selected the
> data, we don't bother reselecting 'for update'.)

Philip, can't say this is good techigue in general, but in
particular circuimstances it is appropriate. If you are sure you
really need pessimistic locking, I can suggest variants:

1. Make your "dummy update" in the same transaction which you'll use
to make reak updates
2. If you are trying to lock "header" of some complex (distributed
among several tables) object each time you have intention to work with
any of it's properties and you need different transactions to handle
properties, create additional table referenced 1:1 on "header" and
perform initial locking in this table, not in real "header".

Best regards, Alexander.