Re: Strange deadlock in foreign key check

On 08/06/2015 09:29 AM, Sophia Wright wrote:
> On Fri, Aug 7, 2015 at 1:11 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com
> <mailto:alvherre(at)2ndquadrant(dot)com>> wrote:
>
> Sophia Wright wrote:
> > I am seeing some odd locking behaviour when deleting a parent record
> > (Postgres 9.4.4).
>
> Somewhere in the triggers for FK checks we do "SELECT FOR KEY SHARE" of
> the PK tuples when the FK tuples are altered; and conversely when we
> remove tuples from the PK side we need to ensure that there are no
> referencing tuples in the FK side. The code doesn't distinguish between
> indexes used in foreign keys from other indexes that *could* be used in
> foreign keys. Therefore your UNIQUE in the declaration for "x" may be
> making it difficult for you. I don't have the time to go through this
> right now, but please try and see what happens if you remove the UNIQUE
> from that column.
>
> We discussed about only considering indexes actually referenced by
> foreign keys instead of all of them, but there are some fine points to
> keep in mind if you do that, so we never got around to implementing that
> optimization. I don't have any immediate suggestion for what to do to
> work around this issue.
>
>
> Thanks. Removing the UNIQUE constraint prevents this, but I'm still not
> clear on why it happens...
>
> Based on your explanation, I can see how a UNIQUE index on the PK side
> would cause problems. But on the FK side, I'm not sure where this fits
> in. Why lock the UNIQUE field, but not lock the FK field itself? Isn't
> it the only part that's relevant here?

I would also take a look at Alvaro's explanation. My understanding is
that for locking purposes the UNIQUE index is considered sort of like a
FK, as it could be used as a FK. This then leads to the FOR UPDATE lock,
which from Table 13.3 at the link I sent, conflicts with all the other
row locks.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com