Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: eric soroos <eric-psql@soroos.net>, pgsql-sql@postgresql.org
Subject: Re: Row Locking?
Date: Tue, 22 Oct 2002 12:51:47 +0000
User-Agent: KMail/1.4.3
References: <38241230.1176905921@[4.42.179.151]>
In-Reply-To: <38241230.1176905921@[4.42.179.151]>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210221251.47781.dev@archonet.com>

On Monday 21 Oct 2002 11:09 pm, eric soroos wrote:
> I have a long running process that performs outside actions on the content
> of a table. The actions could all be done in parallel (if I had n
> processors), but I need to ensure that the process is attempted exactly o=
ne
> time per applicable row.

> Extending this to multiple threads if proving problematic. No locking lea=
ds
> to a race condition between the select and update. If I change the select
> to a SELECT ... FOR UPDATE it apparently locks the table against all other
> select for updates, then when the update is committed, the second thread
> returns nothing, even when there are other rows in the table that could be
> returned.
>
> Is there a single row locking against select?  Or can I effeciently do the
> equivalent of update set pending, then select the row that I just updated
> to get the contents? (perhaps without doing a table scan to find the oid =
of
> the row that I just updated). I can't afford to lock the entire table.

I think the issue is that both threads are hanging about for the same row. =
If=20
the first thread rolled back rather than committing, the second thread migh=
t=20
return the same row. You've only locked the one row, but that's the one row=
=20
everyone wants.

How about something like:

1. SELECT FOR UPDATE...
2. IF no id returned, sleep, goto 1
3. UPDATE foo set pending=3D't' ...

A different solution might be to have one thread allocating rows to the=20
others. So - make "pending" something that holds a thread id. That way the=
=20
processing threads just sit there trying to fetch the first allocated row a=
nd=20
sleep if none available. Alternatively, the allocating thread could pass th=
e=20
info directly to the processing threads.

Might be worth checking the archives for this list and pgsql-general - some=
one=20
had a similar question a few weeks/months ago.

--=20
  Richard Huxton