From: Olivier Dony <odo+pggen@odoo.com>
To: Jim Nasby <Jim.Nasby@BlueTreble.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
Date: Fri, 2 Oct 2015 18:44:55 +0200
Message-ID: <560EB487.7010400@odoo.com> (raw)
In-Reply-To: <560DB385.2080904@BlueTreble.com>
References: <560AA479.4080807@odoo.com>
<560DB385.2080904@BlueTreble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
On 10/02/2015 12:28 AM, Jim Nasby wrote:
> 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.
Interesting, do you know if that is mentioned in the documentation somewhere?
(I couldn't find it)
>> -- 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?
I don't think so. I can reproduce the problem with the queries quoted above,
and the only index that seems to be present is the PK (sorry for the wrapping):
9=# \d users
Table "public.users"
Column | Type | Modifiers
--------+-----------------------------+----------------------------------------------------
id | integer | not null default
nextval('users_id_seq'::regclass)
name | character varying |
date | timestamp without time zone |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "orders" CONSTRAINT "orders_user_id_fkey" FOREIGN KEY (user_id)
REFERENCES users(id)
9=# \d orders
Table "public.orders"
Column | Type | Modifiers
---------+-------------------+-----------------------------------------------------
id | integer | not null default nextval('orders_id_seq'::regclass)
name | character varying |
user_id | integer |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
--
Olivier
--
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: odo+pggen@odoo.com, Jim.Nasby@BlueTreble.com
Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
In-Reply-To: <560EB487.7010400@odoo.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