RE: Select for update in SPL
Boris,
My understanding of what Jonathan explained is
that your procedure should be modified slightly
"THE STORED PROCEDURE WILL USE AN UPDATE CURSOR
AUTOMATICALLY IF THERE IS A 'WHERE CURRENT OF' CLAUSE
IN AN 'UPDATE' OR 'DELETE' STATEMENT IN THE STATEMENT BLOCK
WITHIN THE 'FOREACH' LOOP"
That is, once the record if fetched by the cursor
in the following exapmle, a promotable lock is set on it;
UPDATE promotes this lock to EXCLUSIVE lock
-------------
create procedure test_lock() returning char(15);
define dummy char(15);
begin work;
set isolation to dirty read;
-- presentce of the DUMMY UPDATE inside the cursor
-- is turning the entire cursor into SELECT FOR UPDATE
foreach c_curs for select my_field into dummy
from my_table
where my_id = 1
-- THIS DUMMY UPDATE GUARANTEES THAT FETCHED RECORD
-- IS LOCKED
update my_table set my_field = my_field
where current of c_curs;
return my_field with resume;
--do something that takes a while, so we can check running that procedure
--from another session and see if it gets lock exception
exit foreach;
end foreach;
commit;
end procedure
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: Boris Niyazov [mailto:niyazov@law.columbia.edu]
>
> Well, as I wrote, I'd like to put a promotable lock on a table record
> WITHOUT updating/deleiting it, b/c the logic of the code (update may not
> occur). I have no problem to do that in 4GL or dbaccess using FOR UPDATE.
> Your suggestion of using FOREACH to open cursor does not place a
> promotable
> lock on the record that I tested using the following
>
> create procedure test_lock() returning char(15);>
> define dummy char(15);
>
> begin work;
>
> set isolation to repeatable read;> foreach c_curs for select my_field into dummy
> from my_table
> where my_id = 1
>
> return my_field with resume;
>
> -- do something that takes a while, so we can check running that
> procedure
> -- from another session and see if it gets lock exception
>
> exit foreach;
> end foreach;
>
> commit;
>
> end procedure
>
>
> Do you know about any other way to place a lock on a record in SPL without
> actually updating it?
>
> Thanks
> - Boris
sending to informix-list