Re: Deadlock bug

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>

> Joel Jacobson <joel@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: jj@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