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