Re: Selecting a non-locked row.

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