Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
To: Olivier Dony <odo+pggen@odoo.com>, <pgsql-general@postgresql.org>
References: <560AA479.4080807@odoo.com>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <560DB385.2080904@BlueTreble.com>
Date: Thu, 1 Oct 2015 17:28:21 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <560AA479.4080807@odoo.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 01 Oct 2015 22:28:28.1239 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN1PR11MB0687
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

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


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