From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Peter Geoghegan <pg@heroku.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Date: Thu, 30 Apr 2015 19:00:03 -0700
Message-ID: <5542DE23.6060800@iki.fi> (raw)
In-Reply-To: <CAM3SWZR9RNzQS7prmU0mt4mZ8CQ7f4+UP-vvbT87y8CT_SLFxQ@mail.gmail.com>
References: <CAM3SWZTGE0z5_E8wRi9wt3qgOiMpSwtwr5uDcZDGhRZ1_pz6vg@mail.gmail.com>
<CAM3SWZTzKXsGkxDdxNZjJEm=3P0Eq63y8OUV5rD5cegoWK8B_A@mail.gmail.com>
<553EFF0D.4040903@iki.fi>
<CAM3SWZR9RNzQS7prmU0mt4mZ8CQ7f4+UP-vvbT87y8CT_SLFxQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 04/27/2015 11:02 PM, Peter Geoghegan wrote:
> On Mon, Apr 27, 2015 at 8:31 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>> I thought we had an ironclad scheme to prevent deadlocks like this, so I'd
>> like to understand why that happens.
>
>
> Okay. I think I know how it happens (I was always skeptical of the
> idea that this would be 100% reliable), but I'll be able to show you
> exactly how tomorrow. I'll have pg_xlogdump output then.
I was able to reproduce this, using two sessions, so that on session
does a regular INSERT, and another does INSERT ON CONFLICT, after adding
a sleep(5) to a strategic place. So this was indeed a live bug,
reproducible even without the hack you had to allow ON CONFLICT UPDATE
with exclusion constraints. Fortunately this is easy to fix.
Here's how to reproduce:
1. Insert "sleep(5)" into ExecInsertIndexTuples, just after the
index_insert() call.
2. Create the test table and index:
create extension btree_gist;
create table foo (id int4, constraint foo_x exclude using gist (id with
=) );
3. Launch two psql sessions, A and B. Do the following:
A: set deadlock_timeout='10s';
B: set deadlock_timeout='20s';
A: begin; select txid_current();
B: begin; select txid_current();
A: insert into foo values (1) on conflict do nothing;
(the insert will hit the sleep(5) - quickly perform the second insert
quickly: )
B: insert into foo values (1);
At this point, both transactions have already inserted the tuple to the
heap. A has done so speculatively, but B has done a regular insert. B
will find A's tuple and wait until A's speculative insertion completes.
A will find B's tuple, and wait until B completes, and you get the
deadlock. Thanks to the way the deadlock_timeout's are set, A will
detect the deadlock first and abort. That's not cool with ON CONFLICT
IGNORE.
To fix that, we need to fix the "livelock insurance" check so that A
does not wait for B here. Because B is not a speculative insertion, A
should cancel its speculative insertion and retry instead. (I pushed the
one-line fix for that to your github repository)
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: hlinnaka@iki.fi, pg@heroku.com, andres@anarazel.de, jeff.janes@gmail.com, sfrost@snowman.net, dean.a.rasheed@gmail.com, bruce@momjian.us, pgsql-hackers@postgresql.org
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
In-Reply-To: <5542DE23.6060800@iki.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox