Date: Sun, 19 Jan 2003 23:03:48 +0100
From: Kurt Roeckx <Q@ping.be>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Selecting a non-locked row.
Message-ID: <20030119220348.GA4944@ping.be>
References: <20030119133320.GA3984@ping.be>
	<200301191326.10285.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200301191326.10285.josh@agliodbs.com>
User-Agent: Mutt/1.4i

On Sun, Jan 19, 2003 at 01:26:10PM -0800, Josh Berkus wrote:
> Kurt,
> 
> > I have a table with records in, and they either still need to be
> > processed or not.  Several people will be using the table at the
> > same time.
> > 
> > I was thinking about using a select .... for update limit 1, but
> > the other persons will of course try to select the same record
> > and wait until that one is released.  The locks are held for a
> > long period of time.
> > 
> > Is there a way to select a row that is not locked yet?
> 
> I'm a bit confused by your question.  Could you try explaining it another way, 
> possibly with SQL code examples?

I have data in the table that should only be used once.  Several
people will be using the table at the same time to get a new
record out of it.  After they're done with it the record gets
marked as done.

I have a query that looks something like:

begin;
select id, data
from table
where used IS NULL
order by id
for update
limit 1;

And after some time:
update table
set used = 1
where id = id;
commit;

Of course a second person doing the same thing will just wait for
my commit.

What I want is that he just gets the next non-locked record.


Kurt