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(at)sss(dot)pgh(dot)pa(dot)us>

> Joel Jacobson <joel(at)gluefinance(dot)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(at)gluefinance(dot)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