Re: Serialization errors despite KEY SHARE/NO KEY UPDATE

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