Foreign key and locking problem

From: Edoardo Serra <edoardo(at)serra(dot)to(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Foreign key and locking problem
Date: 2011-04-04 20:18:43
Message-ID: EEAAE7DE-FFC0-41B6-A1D3-7E74D89E8967@serra.to.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi guys,

I have a problem with the implicit "FOR SHARE" lock which postgres seems to get on the referenced record when there is a foreign key.
I'm using postgres 8.3.3 from debian packages.

Here is a sample database structure and commands to reproduce.

-- Test database structure

CREATE TABLE people (
id serial NOT NULL,
nickname character varying(255) NOT NULL,
status integer NOT NULL
);

ALTER TABLE people ADD PRIMARY KEY (id);

CREATE TABLE friendships (
id serial NOT NULL,
person1_id integer NOT NULL,
person2_id integer NOT NULL
);

ALTER TABLE friendships ADD PRIMARY KEY (id);

ALTER TABLE friendships ADD FOREIGN KEY (person1_id) REFERENCES people (id)
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE friendships ADD FOREIGN KEY (person2_id) REFERENCES people (id)
ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO people (id, nickname, status) VALUES (1, 'john.doe', 5);
INSERT INTO people (id, nickname, status) VALUES (2, 'jane.doe', 5);

-- now, in 2 different sessions I type:

client1> BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
client2> BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
client2> UPDATE people SET status = 6 WHERE id = 1;
client1> INSERT INTO friendships (id, person1_id, person2_id) VALUES (default, 1, 2);

client1 hangs trying to acquire the implicit FOR SHARE lock.

client2> COMMIT;

At this point, client1 gives the following error:
ERROR: could not serialize access due to concurrent update
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."people" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

Is there a way to work around that?

In my architecture I have a background task which is computing friendships and a web frontend which is updating the records in the people table.
So updates to the people table can occurr while the background task is doing his job.

Any idea?

Tnx in advance

Regards

Edoardo

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2011-04-04 20:32:24 Re: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
Previous Message Rob Sargent 2011-04-04 19:57:22 scary xpath_table behaviour