Found out that inline view will execute first even the session is being block by other.
Below the steps to reproduce my testing.
create the below object in testing schema first.
LUCOTUS @ orcl1> select dbms_metadata.get_ddl('FUNCTION','FUNC_VALUE') from dual;
DBMS_METADATA.GET_DDL('FUNCTION','FUNC_VALUE')
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "LUCOTUS"."FUNC_VALUE" (i int) return int
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'insert into test2 values (1)' ;
commit;
return i;
end;
LUCOTUS @ orcl1> ed
Wrote file afiedt.buf
1* select dbms_metadata.get_ddl('TABLE','TEST2') from dual
LUCOTUS @ orcl1> /
DBMS_METADATA.GET_DDL('TABLE','TEST2')
--------------------------------------------------------------------------------
CREATE TABLE "LUCOTUS"."TEST2"
( "I" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
Session 1
=========
LUCOTUS @ orcl1> delete from t_test_con;
insert into t_test_con values ('1',1,null,'a','abc');
insert into t_test_con values ('2',1,null,'b','abc');
commit;
1 row deleted.
LUCOTUS @ orcl1> delete from test2;
2 rows deleted.
LUCOTUS @ orcl1> commit;
Commit complete.
LUCOTUS @ orcl1> update t_test_con
set search_col = '1st',
version_no = version_no + 1,
constant_cond_col='ABC'
where key_col = (
select *
from
(
select func_value(1)
from dual
)
where rownum = 1
) 2 3 4 5 6 7 8 9 10 11 12 13 ;
1 row updated.
SESSION 2
=========
LUCOTUS @ orcl1> select * from test2;
I
----------
1
SESSION 3
=========
LUCOTUS @ orcl1> update t_test_con
set search_col = '2nd',
version_no = version_no + 1,
constant_cond_col='ABC'
where key_col = (
select *
from
(
select func_value(1)
from dual
)
where rownum = 1
) 2 3 4 5 6 7 8 9 10 11 12 13 ;
(hang due to block)
SESSION 2
=========
LUCOTUS @ orcl1> SELECT * FROM TEST2;
I
----------
1
1
(Even session 3 not fully execute, seems the function been run successfully).
SESSION 1
=========
LUCOTUS @ orcl1> commit;
Commit complete.
SESSION 3
=========
LUCOTUS @ orcl1> commit;
Commit complete.
LUCOTUS @ orcl1> select * from t_test_con;
KEY_COL VERSION_NO SEARCH_COL ORDER_BY_C CONSTANT_C
---------- ---------- ---------- ---------- ----------
1 3 2nd a ABC
2 1 b abc
The point here is to show inline view will execute even the session been block by other session.
