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

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