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