Search Postgresql Archives

Re: postgresql93-9.3.5: deadlock when updating parent table expected?

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

 



Thanks, Alvaro, 

Yes indeed.  I have a test that causes the deadlock almost immediately. 
I have upgraded to 9.3.6 and have been running for a few hours now w/o 
deadlock errors observed.

Dmitry
________________________________________
From: Alvaro Herrera [alvherre@xxxxxxxxxxxxxxx]
Sent: Wednesday, February 18, 2015 6:19 AM
To: Dmitry O Litvintsev
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  postgresql93-9.3.5: deadlock when updating parent table expected?

Dmitry O Litvintsev wrote:
> Hi,
>
> I recently updated to postgresql93-9.3.5 (from 9.2.9). I see frequent deadlocks
> when updating parent table in insert into child table. There is foreign key constraint between
> child table and parent table. Parent table is updated on by trigger in insert into child table. So
> pretty much standard thing. Is it expected to deadlock?

This is probably caused by a bug that was fixed in 9.3.6:

Author: Alvaro Herrera <alvherre@xxxxxxxxxxxxxx>
Branch: master [0e5680f47] 2014-12-26 13:52:27 -0300
Branch: REL9_4_STABLE Release: REL9_4_1 [0e3a1f71d] 2014-12-26 13:52:27 -0300
Branch: REL9_3_STABLE Release: REL9_3_6 [048912386] 2014-12-26 13:52:27 -0300

    Grab heavyweight tuple lock only before sleeping

    We were trying to acquire the lock even when we were subsequently
    not sleeping in some other transaction, which opens us up unnecessarily
    to deadlocks.  In particular, this is troublesome if an update tries to
    lock an updated version of a tuple and finds itself doing EvalPlanQual
    update chain walking; more than two sessions doing this concurrently
    will find themselves sleeping on each other because the HW tuple lock
    acquisition in heap_lock_tuple called from EvalPlanQualFetch races with
    the same tuple lock being acquired in heap_update -- one of these
    sessions sleeps on the other one to finish while holding the tuple lock,
    and the other one sleeps on the tuple lock.

    Per trouble report from Andrew Sackville-West in
    http://www.postgresql.org/message-id/20140731233051.GN17765@andrew-ThinkPad-X230

    His scenario can be simplified down to a relatively simple
    isolationtester spec file which I don't include in this commit; the
    reason is that the current isolationtester is not able to deal with more
    than one blocked session concurrently and it blocks instead of raising
    the expected deadlock.  In the future, if we improve isolationtester, it
    would be good to include the spec file in the isolation schedule.  I
    posted it in
    http://www.postgresql.org/message-id/20141212205254.GC1768@xxxxxxxxxxxxxx

    Hat tip to Mark Kirkwood, who helped diagnose the trouble.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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