From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Olivier Dony <odo+pggen@odoo.com>
To: pgsql-general@postgresql.org
Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
Date: Thu, 1 Oct 2015 17:28:21 -0500
Message-ID: <560DB385.2080904@BlueTreble.com> (raw)
In-Reply-To: <560AA479.4080807@odoo.com>
References: <560AA479.4080807@odoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
On 9/29/15 9:47 AM, Olivier Dony wrote:
> My understanding of the KEY SHARE/NO KEY UPDATE locks introduced in 9.3
> was that they would avoid side-effects/blocking between transactions
> that are only linked via FK constraints, as long as the target PK was
> not touched. Isn't it the case here?
Not quite. Any unique index that isn't partial and isn't a functional
index can satisfy a foreign key. That means that if you change a field
that is in ANY unique index that update becomes a FOR KEY UPDATE.
> If not, is there a reliable way to make T2 fail instead of T1 in such
> situations? I've tried adding an explicit
> "SELECT date FROM users WHERE id = 1 FOR UPDATE NOWAIT"
> at the beginning of T2 but that doesn't block at all.
>
> Thanks for the clarifications!
>
>
> -- Setup tables
> CREATE TABLE users ( id serial PRIMARY KEY,
> name varchar,
> date timestamp );
> CREATE TABLE orders ( id serial PRIMARY KEY,
> name varchar,
> user_id int REFERENCES users (id) );
> INSERT INTO users (id, name) VALUES (1, 'foo');
> INSERT INTO orders (id, name) VALUES (1, 'order 1');
>
>
> -- Run 2 concurrent transactions: T1 and T2
> T1 T2
> |-----------------------------|----------------------------------|
> BEGIN ISOLATION LEVEL
> REPEATABLE READ;
>
> UPDATE orders
> SET name = 'order of foo',
> user_id = 1
> WHERE id = 1;
>
> BEGIN ISOLATION LEVEL
> REPEATABLE READ;
>
> UPDATE users
> SET date = now()
> WHERE id = 1;
>
> COMMIT;
>
> UPDATE orders
> SET name = 'order of foo (2)',
> user_id = 1
> WHERE id = 1;
>
> T1 fails with:
> ERROR: could not serialize access due to concurrent update
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE
> "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
This isn't a locking failure, it's a serialization failure. I'm not sure
why it's happening though... is there an index on date?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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-general@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, odo+pggen@odoo.com
Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
In-Reply-To: <560DB385.2080904@BlueTreble.com>
* 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