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

Question

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;

Answer 1

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?