Thread: Foreign key and locking problem

Hi guys,

I have a problem with the implicit "FOR SHARE" lock which postgres seems to get on the referenced record when there is
aforeign 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
recordsin 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

On 04/05/2011 04:18 AM, Edoardo Serra wrote:
> Hi guys,
>
> I have a problem with the implicit "FOR SHARE" lock which postgres seems to get on the referenced record when there
isa foreign key. 
> I'm using postgres 8.3.3 from debian packages.

[snip]

> 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?

Is your concern really the locking? Or is it the fact that your two
transactions aren't successfully serialized?

If you're using ISOLATION  LEVEL SERIALIZABLE you need to be prepared to
re-try transactions after serialization failures. Your application code
cannot just fire & forget transactions, it has to remember them and be
able to reissue them on failure. If that is not acceptable to you, then
you should look into whether READ COMMITTED isolation will offer you
sufficient guarantees and see if you can use that.

--
Craig Ringer