Re: Strange deadlock in foreign key check

On 08/06/2015 04:24 PM, Sophia Wright wrote:
> On Fri, Aug 7, 2015 at 2:46 AM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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 <http://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?

Well what I see below from the 10000 ft level:

Session 1:
=>begin;
=>update fk_rel set x = 'b';

http://www.postgresql.org/docs/9.4/interactive/explicit-locking.html#LOCKING-ROWS

"The FOR UPDATE lock mode is also acquired by any DELETE on a row, and
also by an UPDATE that modifies the values on certain columns.
Currently, the set of columns considered for the UPDATE case are those
that have a unique index on them that can be used in a foreign key (so
partial indexes and expressional indexes are not considered), but this
may change in the future."

You have a UNIQUE index on x, so when you UPDATE it you get a FOR UPDATE
lock that from Table 13-3. Conflicting Row-level Locks conflicts with
all other locks.

Session 2:
=>delete from pk_rel where id = 1;
[Blocks waiting for Session 1]

You want to DELETE from pk_rel an id that has a FK dependency to the row
that is now locked above, so the DELETE waits pending the lock release
above.

Session 1:
=>insert into fk_rel values (1, 'a');
[Blocks waiting for Session 2]

You want to INSERT a row into fk_rel that has an pk_id that you are
trying to DELETE above, so this is waiting to to see what the DELETE
does and the DELETE is waiting to see what the UPDATE does and the
INSERT is waiting to see what they both do. So until the UPDATE
commits/rollbacks the DELETE and INSERT are stuck on what do about the
DELETE id=1/ INSERT pk_id=1 dilemma.


--
Adrian Klaver
adrian.klaver@aklaver.com