pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelecting a non-locked row.
5+ messages / 3 participants
[nested] [flat]
* Selecting a non-locked row.
@ 2003-01-19 13:33 Kurt Roeckx <Q@ping.be>
2003-01-19 21:26 ` Re: Selecting a non-locked row. Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kurt Roeckx @ 2003-01-19 13:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting a non-locked row.
2003-01-19 13:33 Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
@ 2003-01-19 21:26 ` Josh Berkus <josh@agliodbs.com>
2003-01-19 22:03 ` Re: Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
2003-01-20 01:34 ` Re: Selecting a non-locked row. D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 2 replies; 5+ messages in thread
From: Josh Berkus @ 2003-01-19 21:26 UTC (permalink / raw)
To: Kurt Roeckx <Q@ping.be>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting a non-locked row.
2003-01-19 13:33 Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
2003-01-19 21:26 ` Re: Selecting a non-locked row. Josh Berkus <josh@agliodbs.com>
@ 2003-01-19 22:03 ` Kurt Roeckx <Q@ping.be>
2003-01-19 22:34 ` Re: Selecting a non-locked row. Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Kurt Roeckx @ 2003-01-19 22:03 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting a non-locked row.
2003-01-19 13:33 Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
2003-01-19 21:26 ` Re: Selecting a non-locked row. Josh Berkus <josh@agliodbs.com>
2003-01-19 22:03 ` Re: Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
@ 2003-01-19 22:34 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-01-19 22:34 UTC (permalink / raw)
To: Kurt Roeckx <Q@ping.be>; +Cc: pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting a non-locked row.
2003-01-19 13:33 Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
2003-01-19 21:26 ` Re: Selecting a non-locked row. Josh Berkus <josh@agliodbs.com>
@ 2003-01-20 01:34 ` D'Arcy J.M. Cain <darcy@druid.net>
1 sibling, 0 replies; 5+ messages in thread
From: D'Arcy J.M. Cain @ 2003-01-20 01:34 UTC (permalink / raw)
To: josh@agliodbs.com, Kurt Roeckx <Q@ping.be>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-01-20 01:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-19 13:33 Selecting a non-locked row. Kurt Roeckx <Q@ping.be>
2003-01-19 21:26 ` Josh Berkus <josh@agliodbs.com>
2003-01-19 22:03 ` Kurt Roeckx <Q@ping.be>
2003-01-19 22:34 ` Josh Berkus <josh@agliodbs.com>
2003-01-20 01:34 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox