Re: Serialization errors despite KEY SHARE/NO KEY UPDATE

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