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