On Fri, Aug 7, 2015 at 2:46 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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?
