Message-ID: <5542DE23.6060800@iki.fi>
Date: Thu, 30 Apr 2015 19:00:03 -0700
From: Heikki Linnakangas <hlinnaka@iki.fi>
Reply-To: hlinnaka@iki.fi
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.6.0
MIME-Version: 1.0
To: Peter Geoghegan <pg@heroku.com>
CC: Andres Freund <andres@anarazel.de>, Jeff Janes <jeff.janes@gmail.com>,
 Stephen Frost <sfrost@snowman.net>,
 Dean Rasheed <dean.a.rasheed@gmail.com>, Bruce Momjian <bruce@momjian.us>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
References: 
 <CAM3SWZTGE0z5_E8wRi9wt3qgOiMpSwtwr5uDcZDGhRZ1_pz6vg@mail.gmail.com>
	<CAM3SWZTzKXsGkxDdxNZjJEm=3P0Eq63y8OUV5rD5cegoWK8B_A@mail.gmail.com>
	<553EFF0D.4040903@iki.fi>
 <CAM3SWZR9RNzQS7prmU0mt4mZ8CQ7f4+UP-vvbT87y8CT_SLFxQ@mail.gmail.com>
In-Reply-To: 
 <CAM3SWZR9RNzQS7prmU0mt4mZ8CQ7f4+UP-vvbT87y8CT_SLFxQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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