Re: Foreign key and locking problem

On Mon, Apr 4, 2011 at 4:18 PM, Edoardo Serra <edoardo(at)serra(dot)to(dot)it> wrote:

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

Do you really need SERIALIZABLE transactions? You have to more or less
expect transaction failures when you use that mode, and handle them.

I also ran into this issue when running 8.3. We have statistics tables we
update via triggers, and I was getting such locks blocking progress of
competing processes. The solution was to make the updates via a queue and
have a single thread apply them to the table. Thus, there are no competing
locks, and the main processes can fly along as fast as possible since all
they do is a single insert requesting the update into a table with no FKs or
other indexes that will slow it down. The only issue is that the thread
that applies the changes must always be running, and must be fast enough for
your workload.

Also, try 9.0. The FK locks are lighter now. Not sure if it will help your
serializable case though.