Using 9.3.9 and 9.4.4 I'm experiencing serialization errors that I
have trouble explaining based on the documentation.
The concurrent transactions are framework-driven and all of them
are running in REPEATABLE READ isolation level.
The error can be reproduced as shown below. T1 fails when acquiring
the second KEY SHARE lock for the same row, but does not fail if
only one of the T1 UPDATEs is executed, either the first or the second
one.
Is there some hidden read/write dependency at play here?
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?
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"
--
Olivier
