Re: Foreign key and locking problem

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 is a 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