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

> Joel Jacobson <> 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

T: +46 70 360 38 01

Postal address:
Glue Finance AB
Box  549
114 11  Stockholm

Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm

Reply via email to