Okay, I finally found in the docs where it says that all locks acquired
continue until rollback or commit (end of txn), which answers my
question in previous email (bottom).
So, it seems like having an UPDATE ... ORDER BY would help this. At
least I could force which order rows are locked that way. As mentioned
earlier, though, I can do that manually as multiple updates in a single
transaction.
So, if I have 2 DB connections, and each one is doing the same update
but on 2 different rows in the same table, and both of those rows have
foreign keys to the same rows in other tables, is there a possibility
that the foreign key locks could be acquired in different orders? In
other words, are there situations with foreign keys where the only way I
can prevent a deadlock with update/insert is to NOT HAVE foreign keys?
The two situations I can think of are:
1) Two FK fields in a single row pointing to 2 different FK tables, for
which any 2 updates get locks in opposite order (seems like a deadlock).
2) Two FK fields pointing to only 1 FK table (both fields are references
to the same table), for which each update gets independent locks instead
of one lock for both fields, so there is a potential that #1 problem
could impact this as well.
Is this the right maillist for this?
Thanks again!
Matt Mello wrote:
> If I do the following, assuming I haven't applied the patch, will it
> still result in a deadlock (in other words, does this only happen with
> multi-record updates, or can it happen with independent single-record
> updates inside transactions)?