Message-ID: <553EFF0D.4040903@iki.fi>
Date: Mon, 27 Apr 2015 20:31:25 -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>, 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>
CC: 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>
In-Reply-To: 
 <CAM3SWZTzKXsGkxDdxNZjJEm=3P0Eq63y8OUV5rD5cegoWK8B_A@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 07:02 PM, Peter Geoghegan wrote:
> So, this can still happen, but is now happening less often than
> before, I believe. On a 16 core server, with continual 128 client
> jjanes_upsert exclusion constraint only runs, with fsync=off, I
> started at this time:
>
> 2015-04-27 21:22:28 UTC [ 0 ]: LOG:  database system was shut down at
> 2015-04-27 21:22:25 UTC
> 2015-04-27 21:22:28 UTC [ 0 ]: LOG:  database system is ready to
> accept connections
> 2015-04-27 22:47:20 UTC [ 0 ]: LOG:  autovacuum launcher started
> 2015-04-27 22:47:21 UTC [ 0 ]: LOG:  autovacuum launcher started
>
> Finally, with ON CONFLICT UPDATE (which we don't intend to support
> with exclusion constraints anyway), the torture testing finally
> produces a deadlock several hours later (due to having "livelock
> insurance" [1]):
>
> 2015-04-28 00:22:06 UTC [ 0 ]: LOG:  autovacuum launcher started
> 2015-04-28 00:37:24 UTC [ 432432057 ]: ERROR:  deadlock detected
> 2015-04-28 00:37:24 UTC [ 432432057 ]: DETAIL:  Process 130628 waits
> for ShareLock on transaction 432432127; blocked by process 130589.
>          Process 130589 waits for ShareLock on speculative token 13 of
> transaction 432432057; blocked by process 130628.
>          Process 130628: insert into upsert_race_test (index, count)
> values ('7566','-1') on conflict
>                        update set count=TARGET.count + EXCLUDED.count
>                        where TARGET.index = EXCLUDED.index
>                        returning count
>          Process 130589: insert into upsert_race_test (index, count)
> values ('7566','1') on conflict
>                        update set count=TARGET.count + EXCLUDED.count
>                        where TARGET.index = EXCLUDED.index
>                        returning count
> 2015-04-28 00:37:24 UTC [ 432432057 ]: HINT:  See server log for query details.
> 2015-04-28 00:37:24 UTC [ 432432057 ]: CONTEXT:  while checking
> exclusion constraint on tuple (3,36) in relation "upsert_race_test"
> 2015-04-28 00:37:24 UTC [ 432432057 ]: STATEMENT:  insert into
> upsert_race_test (index, count) values ('7566','-1') on conflict
>                        update set count=TARGET.count + EXCLUDED.count
>                        where TARGET.index = EXCLUDED.index
>                        returning count
>
> ON CONFLICT UPDATE will only ever use unique indexes, and so is not affected.
>
> Given that exclusion constraints can only be used with IGNORE, and
> given that this is so hard to recreate, I'm inclined to conclude that
> it's acceptable. It's certainly way better than risking livelocks by
> not having "deadlock insurance". This is a ridiculously CPU-bound
> workload, with extreme and constant contention. I'd be surprised if
> there were any real complaints from the field in practice.
>
> Do you think that this is acceptable, Heikki?

I thought we had an ironclad scheme to prevent deadlocks like this, so 
I'd like to understand why that happens.

- Heikki


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers