Re: regression, deadlock in high frequency single-row UPDATE

From: hubert depesz lubaczewski <depesz(at)gmail(dot)com>
To: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, pgsql-bugs(at)postgresql(dot)org, Paulo Tanimoto <paulo(at)janrain(dot)com>
Subject: Re: regression, deadlock in high frequency single-row UPDATE
Date: 2014-12-09 12:36:25
Message-ID: CAKrjmhdM=sRaQARMW62gSuZvGSJF20VX4UgQv4ZOoopxFoT-ZA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

For whatever it's worth, I was able to bisect the problem to this commit:

commit
0ac5ad5134f2769ccbaefec73844f8504c4d6182

Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>

Date: Wed Jan 23 12:04:59 2013 -0300

Improve concurrency of foreign key locking
...

depesz

On Tue, Dec 9, 2014 at 6:58 AM, Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz
> wrote:

> On 09/12/14 12:55, Andrew Sackville-West wrote:
>
>> Some months later (stupid business priorities...) we have something to
>> report on this. We have been able to produce a minimal schema to
>> demonstrate the problem, and successfully induced the deadlock using
>> pgbench (thanks to Paulo Tanimoto, cc-ed here).
>>
>> Please see:
>>
>> https://gist.github.com/andrewsw-janrain/40d1687db013b1e7c3b3
>>
>> for detailed instructions on how to trigger the deadlock.
>>
>> I would be thrilled to learn that we've done something wrong here,
>> otherwise I think this represents a regression introduced in 9.3.
>>
>>
>>
>
> That is interesting - FWIW I can reproduce:
>
> - 9.2.9 no deadlock
> - 9.4rc1 many deadlocks
>
> so something has changed after 9.2 for sure!
>
> Just mucking about I changed certain things in your schema setup (removed
> ON DELETE CASCADE, use CURRENT_TIMESTAMP instead of now()...lessen the use
> of DEFAULT)...however still seeing deadlocks in 9.4, so at least it is easy
> to reproduce!
>
> Regards
>
> Mark
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Julian Schauder 2014-12-09 13:49:04 Re: BUG #12181: Low Disk Space
Previous Message Francisco Olarte 2014-12-09 12:19:54 Re: BUG #11986: psql uses pager inside Emacs shell buffer (not a terminal)