Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
To: Jim Nasby <Jim.Nasby@BlueTreble.com>
References: <560AA479.4080807@odoo.com> <560DB385.2080904@BlueTreble.com>
From: Olivier Dony <odo+pggen@odoo.com>
Cc: pgsql-general@postgresql.org
Message-ID: <560EB487.7010400@odoo.com>
Date: Fri, 2 Oct 2015 18:44:55 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <560DB385.2080904@BlueTreble.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general