On Mon, Apr 4, 2011 at 4:18 PM, Edoardo Serra
<edoardo@serra.to.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.