Can a sub-procedure procedure lock and modify the same rows FOR UPDATE that its calling procedure already locked?

It won't cause a deadlock. THat can only happen when two sessions update the same row because they are employing an optimistic locking strategy. Here is what happens.

It won't cause a deadlock. THat can only happen when two sessions update the same row because they are employing an optimistic locking strategy. Here is what happens Some test data: SQL> select * from t23 2 / PERSON_NAME ----------------------------------------------------------------------------- Fox in socks Mr Knox Sam-I-Am The Lorax John Doe SQL> This is your anonymous (with corrected sybtax):.

SQL> declare 2 cursor c_jd is 3 select * 4 from t23 5 where person_name = 'John Doe' 6 for update of person_name; 7 procedure foo_proc 8 ( p_name in t23. Person_name%type) 9 is 10 cursor c_fp is 11 select * 12 from t23 13 where person_name = p_name 14 for update of person_name; 15 r_fp c_fp%rowtype; 16 begin 17 open c_fp; 18 fetch c_fp into r_fp; 19 update t23 20 set person_name = upper(r_fp. Person_name) 21 where current of c_fp; 22 close c_fp; 23 end foo_proc; 24 begin 25 for onerow in c_jd loop 26 foo_proc(onerow.

Person_name); 27 end loop; 28 end; 29 / PL/SQL procedure successfully completed. SQL> And this is the outcome SQL> select * from t23 2 / PERSON_NAME ----------------------------------------------------------------------------- Fox in socks Mr Knox Sam-I-Am The Lorax JOHN DOE SQL> So does it succeed? Because the FOR UPDATE is a session level lock.

The two locks are issued from the same session so Oracle is smart enough to resolve them without contention. Howver if you were to do something like declare an PRAGMA AUTONOMOUS_TRANSACTION in FOO_PROC() it would hurl ORA-00060: deadlock detected while waiting for resource The fact that two calls to FOR UPDATE in the same session do not not fail in this manner is an important piece of architectural design. It is not possible to tell whether a procedure issues a lock without looking at the source code.So when PROC_A() calls PROC_B() it has no idea whether that procedure issues a lock.

But PROC_A() can issues its own lock, confident that this action will not cause PROC_B() to fail. This is a good thing, because it upholds the Law of Demeter and reduces coupling. Of course, your scenario is artificial, and would be rejected as bad practice in a code review, but that is a different issue!

Edit "To test this I did make FOO_PROC autonomous and it did not run into a deadlock; is that because it's in the same session? " Are you sure? The AUTONOMOUS_TRANSACTION pragma means precisely that FOO_PROC() runs in its own discrete session, and so fails to get a lock: SQL> declare 2 cursor c_jd is 3 select * 4 from t23 5 for update of person_name; 6 procedure foo_proc 7 ( p_name in t23.

Person_name%type) 8 is 9 pragma autonomous_transaction; 10 cursor c_fp is 11 select * 12 from t23 13 where person_name = p_name 14 for update of person_name; 15 r_fp c_fp%rowtype; 16 begin 17 dbms_output. Put_line('Inside FP'); 18 open c_fp; 19 fetch c_fp into r_fp; 20 update t23 21 set person_name = upper(r_fp. Person_name) 22 where current of c_fp; 23 close c_fp; 24 commit; 25 end foo_proc; 26 begin 27 for onerow in c_jd loop 28 dbms_output.

Put_line('Outer loop START'); 29 foo_proc(onerow. Person_name); 30 dbms_output. Put_line('Outer loop END'); 31 end loop; 32 end; 33 / Outer loop START Inside FP declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 11 ORA-06512: at line 18 ORA-06512: at line 29 SQL> (I added some DBMS_OUTPUT statements to show what's happening).

"When you said the code example I provided was bad practice, what do you mean? " I meant having a loop driving off a SELECT statement calling another program which selects from the same table. Indeed, which selects the very same row.

Generally speaking, we should avoid doing unnecessary work. You already have the row: why read it again?

Nesting a procedure, having a nested procedure get an update lock, creating a procedure which could have been replaced by a single line of execution in the calling code, ...? I'm self-taught in PL/SQL and am always looking for tips. – RenderIn May 23 '10 at 4:00 Thanks for the edits... I appreciate the guidance. – RenderIn May 23 '10 at 5:00.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.