"select for update" confusion

Started by Ken Godeeabout 22 years ago2 messagesgeneral
Jump to latest
#1Ken Godee
ken@perfect-image.com

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

#2Keary Suska
hierophant@pcisys.net
In reply to: Ken Godee (#1)
Re: "select for update" confusion

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"