Re: Serialization errors despite KEY SHARE/NO KEY UPDATE - Mailing list pgsql-general

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)


>> -- 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):

9=# \d users
                                    Table "public.users"
  Column |            Type             |                     Modifiers

--------+-----------------------------+----------------------------------------------------
  id     | integer                     | not null default
nextval('users_id_seq'::regclass)
  name   | character varying           |
  date   | timestamp without time zone |
Indexes:
     "users_pkey" PRIMARY KEY, btree (id)
Referenced by:
     TABLE "orders" CONSTRAINT "orders_user_id_fkey" FOREIGN KEY (user_id)
REFERENCES users(id)

9=# \d orders
                                Table "public.orders"
  Column  |       Type        |                      Modifiers

---------+-------------------+-----------------------------------------------------
  id      | integer           | not null default nextval('orders_id_seq'::regclass)
  name    | character varying |
  user_id | integer           |
Indexes:
     "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
     "orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)


--
Olivier