Re: Strange deadlock in foreign key check

On Fri, Aug 7, 2015 at 2:46 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

Like I said, I think it would make sense for a UNIQUE index in pk_rel, i.e.
the fk_rel insert would try to lock pk_rel.id with KEY SHARE, and would end
up locking any other UNIQUE fields as a result.

But I can't see why the pk_rel deletion would want a KEY SHARE lock on
fk_rel. It must be using FOR KEY SHARE rather than FOR UPDATE, since it
does not conflict with the update of fk_rel.pk_id in the first example. So
why lock fk_rel at all, if the lock doesn't include fk_rel.pk_id? Isn't
that the only bit that matters to a pk_rel deletion?