On 10/2/15 11:44 AM, Olivier Dony wrote:
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)
http://www.postgresql.org/docs/9.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
does say this:
"A foreign key must reference columns that either are a primary key or
form a unique constraint."
So you can kind of infer it. It could probably be more explicitly
mentioned somewhere though. Care to suggest a doc change?
-- 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):
I'm not sure. Perhaps Kevin Grittner (author of serializable patch) can
shed some light.
--
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
