"select for update" confusion

From: Ken Godee <ken(at)perfect-image(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: "select for update" confusion
Date: 2004-03-22 00:10:39
Message-ID: 405E2EFF.4050904@perfect-image.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to build a type of record "check out" program and I'm just
a wee bit confused.
Several users will be using the same select
statement at the same time and just want to make sure they're getting
different records. example.......

-----------------------------------------------------
user1

begin;

select account from customer where customer is not null and chkout
is null order by account for update limit 1;

"account 123"

update customer set chkout = 'x' where account = 123;

update 1

end;
-----------------------------------------------------
user2

begin;

select account from customer where customer is not null and chkout
is null order by account for update limit 1;

(waits for commit from user1 to release row lock)
(user1 commits)

"account ( 0 rows)"

end;
-----------------------------------------------------

I thought the cursor would wait for the commit from user1 and once
user1 commited it would re-evaluate the query starting at that row
and continue if it doesn't meet the select where clause.

Ok, I see I can remove the "limit 1" and let the cursor return
"all rows" (thousands, or maybe "limit 50"), just thought it would be
more efficent using the "limit 1"

errr, am I missing something, should one just return all rows/limit 50
or should I just be going about this another way?

Any thoughts would be great.

TIA
Ken

Responses

Browse pgsql-general by date

  From Date Subject
Next Message marian 2004-03-22 05:01:06 prob with really simple insert
Previous Message Bruno Wolff III 2004-03-21 17:48:23 Re: Simple list tables question