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@2ndquadrant.com
> <mailto:alvherre@2ndquadrant.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.klaver@aklaver.com