Search Postgresql Archives

Re: Strange deadlock in foreign key check

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 08/06/2015 09:29 AM, Sophia Wright wrote:
On Fri, Aug 7, 2015 at 1:11 AM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx
<mailto:alvherre@xxxxxxxxxxxxxxx>> 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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux