Re: Selecting a non-locked row.

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(at){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.