Thread: "select for update" confusion

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

on 3/21/04 5:10 PM, ken@perfect-image.com purportedly said:

> 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.......

AFAIK, Postgres does not have exclusive row-level locking. I.e., your SELECT
.. For UPDATE only locks writes, and not reads (SELECTs). You can only
accomplish exclusive locks with a table lock (LOCK TABLE), which should
guarantee serial execution of all concurrent queries, but with a possible
performance penalty depending on your application.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"