From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Peter Geoghegan <pg@heroku.com>
To: Andres Freund <andres@anarazel.de>
To: Jeff Janes <jeff.janes@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Date: Mon, 27 Apr 2015 20:31:25 -0700
Message-ID: <553EFF0D.4040903@iki.fi> (raw)
In-Reply-To: <CAM3SWZTzKXsGkxDdxNZjJEm=3P0Eq63y8OUV5rD5cegoWK8B_A@mail.gmail.com>
References: <CAM3SWZTGE0z5_E8wRi9wt3qgOiMpSwtwr5uDcZDGhRZ1_pz6vg@mail.gmail.com>
<CAM3SWZTzKXsGkxDdxNZjJEm=3P0Eq63y8OUV5rD5cegoWK8B_A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
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: <553EFF0D.4040903@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