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

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(at)2ndquadrant(dot)com]
Sent: Wednesday, February 18, 2015 6:19 AM
To: Dmitry O Litvintsev
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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(at)alvh(dot)no-ip(dot)org>
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@alvh.no-ip.org

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

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