pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Kurt Roeckx <Q@ping.be>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Selecting a non-locked row.
Date: Sun, 19 Jan 2003 23:03:48 +0100
Message-ID: <20030119220348.GA4944@ping.be> (raw)
In-Reply-To: <200301191326.10285.josh@agliodbs.com>
References: <20030119133320.GA3984@ping.be>
	<200301191326.10285.josh@agliodbs.com>

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

view thread (5+ messages)  latest in thread

Message-ID: <20030119220348.GA4944@ping.be>
Permalink:  ../20030119220348.GA4944@ping.be/
Also on:    postgresql.org/message-id/20030119220348.GA4944@ping.be

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: Q@ping.be, josh@agliodbs.com
  Subject: Re: Selecting a non-locked row.
  In-Reply-To: <20030119220348.GA4944@ping.be>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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