Process 1 updates A in its transaction, which is still going on when process 2 updates B, requiring a sharelock on A, which it is granted. But when process 2 does its second update of B, also of course requiring a sharelock on A, it is not granted.
I fully agree it must obtain a sharelock on the FK, but I cannot understand why it is granted it the first time, but not the second time?
2010/8/20 Tom Lane
<tgl@sss.pgh.pa.us>> I don't understand exactly why this deadlock occurs, but the one thing I
> cannot understand is why process 2 is not allowed to update the same row,
> which it has already updated in the same transaction.
It *is* allowed to, and in fact has already done so. The problem is
that it now needs a sharelock on the referenced row in order to ensure
that the FK constraint remains satisfied, ie, nobody deletes the
referenced row before we commit the update. In the general case where
the referencing row is new (or has a new FK value) in the current
transaction, such a lock is necessary for correctness. Your case would
work if we could optimize away the FK check, but with only a limited
view of what's happened in the current transaction, it's not always
possible to optimize away the check.
regards, tom lane
--
Best regards,
Joel Jacobson
Glue Finance
E:
jj@gluefinance.comT: +46 70 360 38 01
Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden
Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden