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 <t...@sss.pgh.pa.us> > Joel Jacobson <j...@gluefinance.com> writes: > > 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: j...@gluefinance.com T: +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
