Select for update and locking problem

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Select for update and locking problem
Date: 2003-11-05 22:08:39
Message-ID: 200311051408.39418.scrawford@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In one system I have a table of work to be done (for simplicity in
this explanation I'll use a table consisting of id and status).

A client-side app needs to fetch 10 available items from the table but
naturally we don't want two clients working on the same 10 items.

My first attempt at handling this was, in pseudocode:

begin transaction
select * from worktodo where status = 0 limit 10 for update
update worktodo set status = 1 where id in set selected above
commit

If I test this method by hand on two "simultaneous" transactions I
will get 10 records on the first transaction and the second will
block until the first one commits.

When the first commits the second proceeds but unfortunately returns
no records even though there are plenty available.

I can reach my goal by getting an access exclusive lock on the table
and then doing the transaction but there seems there must be a better
way.

Thoughts? Comments? Ideas? Is there a way to exclude locked rows from
a query?

Cheers,
Steve

Browse pgsql-general by date

  From Date Subject
Next Message CSN 2003-11-05 22:26:13 SQL_ASCII encoding
Previous Message Andrew Sullivan 2003-11-05 21:55:12 Re: Multi-master asynchronous replication