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
