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: | Whole Thread | Raw Message | 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
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 |