deadlock, select for update - one session

select_for_update_deadlock_one_session.log

SQL> 
SQL> create table t (id, value) as select 1, 1 from dual
  2  /

Table created.

Elapsed: 00:00:00.03
SQL> 
SQL> var cur_1 refcursor
SQL> var cur_2 refcursor
SQL> 
SQL> declare
  2  	 v_id t.id%type;
  3  	 v_value t.value%type;
  4  begin
  5  	 open :cur_1 for select * from t for update;
  6  	 fetch :cur_1 into v_id, v_value;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> 
SQL> declare
  2  	 pragma autonomous_transaction;
  3  	 v_id t.id%type;
  4  	 v_value t.value%type;
  5  begin
  6  	 open :cur_2 for select * from t for update;
  7  	 fetch :cur_2 into v_id, v_value;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource 
ORA-06512: at line 6 


Elapsed: 00:00:03.41
SQL> 
SQL> drop table t purge;

Table dropped.

Elapsed: 00:00:00.04
SQL> 
SQL> exit

select_for_update_deadlock_one_session.sql

cl scr 

spool select_for_update_deadlock_one_session.log

set echo on timi on

create table t (id, value) as select 1, 1 from dual
/

var cur_1 refcursor 
var cur_2 refcursor 

declare 
    v_id t.id%type;
    v_value t.value%type;
begin 
    open :cur_1 for select * from t for update;
    fetch :cur_1 into v_id, v_value;
end;
/

declare 
    pragma autonomous_transaction;
    v_id t.id%type;
    v_value t.value%type;
begin 
    open :cur_2 for select * from t for update;
    fetch :cur_2 into v_id, v_value;
end;
/

drop table t purge;

exit