From: Olivier Dony <odo+pggen@odoo.com>
Subject: Serialization errors despite KEY SHARE/NO KEY UPDATE
To: pgsql-general@postgresql.org
Message-ID: <560AA479.4080807@odoo.com>
Date: Tue, 29 Sep 2015 16:47:21 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

Using 9.3.9 and 9.4.4 I'm experiencing serialization errors that I
have trouble explaining based on the documentation.

The concurrent transactions are framework-driven and all of them
are running in REPEATABLE READ isolation level.

The error can be reproduced as shown below. T1 fails when acquiring
the second KEY SHARE lock for the same row, but does not fail if
only one of the T1 UPDATEs is executed, either the first or the second
one.

Is there some hidden read/write dependency at play here?

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?

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"


--
Olivier


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