From: Bruno Wolff III <bruno@wolff.to>
To: Chris Gamache <cgg007@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: FIFO Queue Problems
Date: Fri, 1 Nov 2002 10:54:07 -0600
Message-ID: <20021101165407.GA32463@wolff.to> (raw)
In-Reply-To: <20021101145630.50886.qmail@web13801.mail.yahoo.com>
References: <20021101143348.A7426@goldfisch.at>
<20021101145630.50886.qmail@web13801.mail.yahoo.com>
On Fri, Nov 01, 2002 at 06:56:30 -0800,
Chris Gamache <cgg007@yahoo.com> wrote:
>
> The problem occurrs when two of the processes grab the exact same row at the
> exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if
> setting the transactions to serializable would fix the problem since it occurrs
> between different postmasters.
I played with this a bit and it looks like you want transaction isolation
level serializable and have the application retry when necessary.
Doing a select for update won't work cleanly. What will happen is that
you will get 0 rows returned when there is a conflict and you will need
to detect that same as you would by changing the isolation level.
What happens in the select for update case is you first rewrite the select
to use order by and limit to pick the minimum row since you can't use
for update when returning an aggregate. Then when the matching row is
found there is a possible lock contention with another update. If this
happens then when the other update commits the locked row is rechecked and
will no longer match and gets removed from the list of rows returned (leaving
no rows).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, cgg007@yahoo.com
Subject: Re: FIFO Queue Problems
In-Reply-To: <20021101165407.GA32463@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox