Обсуждение: Selecting a non-locked row.

I'm in the process of writing an application, and I'm not sure
how to properly solve it.

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?


Kurt

Kurt,

> I'm in the process of writing an application, and I'm not sure
> how to properly solve it.
>
> 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?


--
-Josh BerkusAglio Database SolutionsSan Francisco

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

 Kurt,

> 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.

Well, there's two ways you can do this:

1) hack the system tables to find out which incomplete rows in the table are
currently locked, and select the lowest ID from those that aren't.   You can
do this in 7.3 fairly easily throught the "pg_locks" table, but in 7.2.3 it
involves a rather annoying hack of the "hidden" tuple fields (which I don't
know very well, so don't ask).

2) (my preference) modify your done/not done field to accept 3 values:  not
done, in progress, done.   Then add this step between select ... for update
and the final update that updates the row as "in progress".    Then you can
more easily select the first "not done" row.  (actually, you would have to
set a tuple lock with something longer lasting than select for update, and
arrange to drop it if the connection dies.  but it's still my preferred
solution)

--
-Josh BerkusAglio Database SolutionsSan Francisco

On Sunday 19 January 2003 16:26, Josh Berkus wrote:
> > 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 suspect that he is looking for something like this:

SELECT * FROM foo WHERE [yada yada yada] AND NOT LOCKED;

I don't think we have anything like that.  It's not a bad idea though.  I have 
used this facility in Progress RDBMS and it can be useful.  My application of 
it was to create my own sequences that were guaranteed to be contiguous.  
They could be out of order within reason but no numbers were allowed to be 
skipped.  I wound up creating a special table for sequences that had multiple 
entries for each sequence and you would simply get the lowest unlocked number 
in your sequence and bump it by the count of numbers, 10 in this case.  If 
someone else had a number locked in a transaction and after you took the next 
one they released it with an ABORT, the number simply became available again 
to the next process.  I'm not sure how to do something like that without the 
ability to exclude locked records from the query or else with an atomic 
compare and set function.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.