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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
