Can a sub-procedure procedure lock and modify the same rows FOR UPDATE that its calling procedure al
- by RenderIn
Will the following code lead to a deadlock or should it work without any problem? I've got something similar and it's working but I didn't think it would. I thought the parent procedure's lock would have resulted in a deadlock for the child procedure but it doesn't seem to be.
If it works, why? My guess is that the nested FOR UPDATE is not running into a deadlock because it's smart enough to realize that it is being called by the same procedure that has the current lock.
Would this be a deadlock if FOO_PROC was not a nested procedure?
DECLARE
FOO_PROC(c_someName VARCHAR2) as
cursor c1 is select * from awesome_people where person_name = c_someName FOR UPDATE;
BEGIN
open c1;
update awesome_people set person_name = UPPER(person_name);
close c1;
END FOO_PROC;
cursor my_cur is select * from awesome_people where person_name = 'John Doe' FOR UPDATE;
BEGIN
for onerow in c1 loop
FOO_PROC(onerow.person_name);
end loop;
END;
