Home » SQL & PL/SQL » SQL & PL/SQL » Simultaneous DML executions on different objects in the same session (11g and higher, any OS)
Simultaneous DML executions on different objects in the same session [message #679016] Tue, 28 January 2020 08:29 Go to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
SQL> create table test12 (l_str2 varchar2(64 char));

Table created.

SQL> create table test22 (l_str2 varchar2(64 char));

Table created.

SQL> create or replace function fnc_execute_parallel(l_str string) return simple_integer
  2  is
  3      pragma AUTONOMOUS_TRANSACTION;
  4      l_return simple_integer:=1;
  5  begin
  6      execute immediate l_str;
  7      commit;
  8      return l_return;
  9  end;
 10  /

Function created.

SQL> insert into test12 (l_str2) values ('0');

1 row created.

SQL> insert into test22 (l_str2) values ('0');

1 row created.

SQL> commit;

Commit complete.

SQL> select fnc_execute_parallel('update test12 set l_str2='||''''||to_char(current_timestamp(6))||''''),
  2         fnc_execute_parallel('update test22 set l_str2='||''''||to_char(current_timestamp(6))||'''')
  3    from dual;

FNC_EXECUTE_PARALLEL('UPDATETEST12SETL_STR2='||''''||TO_CHAR(CURRENT_TIMESTAMP(6))||'''')
-----------------------------------------------------------------------------------------
FNC_EXECUTE_PARALLEL('UPDATETEST22SETL_STR2='||''''||TO_CHAR(CURRENT_TIMESTAMP(6))||'''')
-----------------------------------------------------------------------------------------
                                                                                        1
                                                                                        1

SQL> select a.l_str2,
  2         b.l_str2
  3    from test12 a,
  4         test22 b
  5  ;

L_STR2
----------------------------------------------------------------
L_STR2
----------------------------------------------------------------
28-JAN-20 09.18.41.028523 AM -05:00
28-JAN-20 09.18.41.028523 AM -05:00

SQL> 

P.S.
It's just an experience sharing.
Re: Simultaneous DML executions on different objects in the same session [message #679025 is a reply to message #679016] Tue, 28 January 2020 10:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2976
Registered: January 2010
Location: Connecticut, USA
Senior Member
Functions like SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP are executed once per statement, therefore will produce same value everywhere it is used in SQL statement.

SY.
Re: Simultaneous DML executions on different objects in the same session [message #679026 is a reply to message #679016] Tue, 28 January 2020 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know if there are simultaneous (I think parallel query must be set and enable for the session) but what is sure is that they do not run in the same transaction and not even in the current transaction of the session (the one of the query) so if they are not independent this will lead to not deterministic result.
I'm not sure, it is a good thing to use in real environment.

Note: also beware of SQL injection.

My 2 cents. Wink

Thanks for the trick to use with great care.
Re: Simultaneous DML executions on different objects in the same session [message #679028 is a reply to message #679025] Tue, 28 January 2020 11:15 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
Solomon Yakobson wrote on Tue, 28 January 2020 11:52
Functions like SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP are executed once per statement, therefore will produce same value everywhere it is used in SQL statement.

SY.
Not diving into discussion, I'm sure a lot of other ways exist to check out simultaneousness of this example.
Re: Simultaneous DML executions on different objects in the same session [message #679029 is a reply to message #679028] Tue, 28 January 2020 11:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2976
Registered: January 2010
Location: Connecticut, USA
Senior Member
Olexandr Siroklyn wrote on Tue, 28 January 2020 12:15

Not diving into discussion, I'm sure a lot of other ways exist to check out simultaneousness of this example.
Again, you can't assume both function calls run simultaneously. It might happen in RAC if parallel query execution is used but even then it isn't guaranteed. And it is definitely not at the same time in your example:


SQL> -- As I already mentioned current_timestamp is calculated once
SQL> select fnc_execute_parallel('update test12 set l_str2='||''''||to_char(current_timestamp(6))||''''),
  2         fnc_execute_parallel('update test22 set l_str2='||''''||to_char(current_timestamp(6))||'''')
  3    from dual
  4  /

FNC_EXECUTE_PARALLEL('UPDATETEST12SETL_STR2='||''''||TO_CHAR(CURRENT_TIMESTAMP(6
--------------------------------------------------------------------------------
FNC_EXECUTE_PARALLEL('UPDATETEST22SETL_STR2='||''''||TO_CHAR(CURRENT_TIMESTAMP(6
--------------------------------------------------------------------------------
                                                                               1
                                                                               1


SQL> select  a.l_str2,
  2          b.l_str2
  3    from  test12 a,
  4          test22 b
  5  /

L_STR2
----------------------------------------------------------------
L_STR2
----------------------------------------------------------------
28-JAN-20 12.42.35.786866 PM AMERICA/NEW_YORK
28-JAN-20 12.42.35.786866 PM AMERICA/NEW_YORK


SQL> -- And now current_timestamp is calculated at update time
SQL> select fnc_execute_parallel('update test12 set l_str2=to_char(current_timestamp(6))'),
  2         fnc_execute_parallel('update test22 set l_str2=to_char(current_timestamp(6))')
  3    from dual
  4  /

FNC_EXECUTE_PARALLEL('UPDATETEST12SETL_STR2=TO_CHAR(CURRENT_TIMESTAMP(6))')
---------------------------------------------------------------------------
FNC_EXECUTE_PARALLEL('UPDATETEST22SETL_STR2=TO_CHAR(CURRENT_TIMESTAMP(6))')
---------------------------------------------------------------------------
                                                                          1
                                                                          1


SQL> select  a.l_str2,
  2          b.l_str2
  3    from  test12 a,
  4          test22 b
  5  /

L_STR2
----------------------------------------------------------------
L_STR2
----------------------------------------------------------------
28-JAN-20 12.43.41.220409 PM AMERICA/NEW_YORK
28-JAN-20 12.43.41.220784 PM AMERICA/NEW_YORK


SQL>

SY.
Re: Simultaneous DML executions on different objects in the same session [message #679033 is a reply to message #679029] Wed, 29 January 2020 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Given it's a select from dual I doubt parallel would kick in either.
Re: Simultaneous DML executions on different objects in the same session [message #679138 is a reply to message #679016] Mon, 10 February 2020 08:57 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
Solomon Yakobson's note is correct. Below is a little bit more sophisticated working example:
SQL> create table test12 (l_str2 varchar(128));

Table created.

SQL> create table test22 (l_str2 varchar(128));

Table created.

SQL> insert into test12 (l_str2) values ('0');

1 row created.

SQL> insert into test22 (l_str2) values ('0');

1 row created.

SQL> commit;

Commit complete.

SQL> create type typ_dml_string as table of varchar2(4000);
  2  /

Type created.

SQL> create or replace function fnc_pl_manager return simple_integer parallel_enable
  2  is
  3  begin
  4      execute immediate 'alter session force parallel query';
  5      return 1;
  6  end fnc_pl_manager;
  7  /

Function created.

SQL> create or replace view viw_pl_manager
  2  as
  3  select 1 as THREAD_ID from dual where fnc_pl_manager=1
  4  union
  5  select 2 as THREAD_ID from dual where fnc_pl_manager=1;

View created.

SQL> create or replace package pkg_pl_manager
  2  as
  3           type rc_viw_pl_manager is ref cursor return viw_pl_manager%rowtype;
  4       function fnc_submit (p_task_list in typ_dml_string, p_pl_refcur in rc_viw_pl_manager )
  5                return typ_dml_string
  6                parallel_enable(partition p_pl_refcur by any)
  7                pipelined
  8                deterministic;
  9      procedure prc_launch(l_what in varchar2);
 10       function fnc_sleep(l_sec simple_integer) return simple_integer;
 11  end pkg_pl_manager;
 12  /

Package created.

SQL> create or replace package body pkg_pl_manager
  2  as
  3  ----------------------
  4  function fnc_sleep(l_sec simple_integer) return simple_integer
  5  is
  6  begin
  7      dbms_lock.sleep(l_sec);
  8      return 0;
  9  end fnc_sleep;
 10  --------------------------------
 11  procedure prc_launch(l_what in varchar2)
 12  is
 13  pragma autonomous_transaction;
 14  begin
 15      execute immediate l_what;
 16      commit;
 17  end prc_launch;
 18  --------------------------------
 19  function fnc_submit(p_task_list in typ_dml_string, p_pl_refcur in rc_viw_pl_manager)
 20  return typ_dml_string
 21  parallel_enable(partition p_pl_refcur by any)
 22  pipelined
 23  deterministic
 24  is
 25      pointer_viw_pl_manager viw_pl_manager%rowtype;
 26  begin
 27      loop
 28      fetch p_pl_refcur into pointer_viw_pl_manager;
 29      exit when p_pl_refcur%notfound;
 30      prc_launch(p_task_list(pointer_viw_pl_manager.thread_id));
 31      end loop;
 32      return;
 33  end fnc_submit;
 34  ---------------------------------
 35  end pkg_pl_manager;
 36  /

Package body created. 

SQL> select (select L_STR2 from test12 where pkg_pl_manager.fnc_sleep(1)=0) as TABLE_12_UPDATE_TIME,
  2         (select L_STR2 from test22 where pkg_pl_manager.fnc_sleep(1)=0) as TABLE_22_UPDATE_TIME,
  3         pkg_pl_manager.fnc_submit(typ_dml_string('update test12 set l_str2=to_char(12)||to_char(current_timestamp(6))',
  4                                                  'update test22 set l_str2=to_char(22)||to_char(current_timestamp(6))'),
  5                                   cursor(select THREAD_ID from viw_pl_manager)) as A
  6   from dual;

TABLE_12_UPDATE_TIME
------------------------------------------------------------------------------------------------------------------------
TABLE_22_UPDATE_TIME
------------------------------------------------------------------------------------------------------------------------
A
------------------------------------------------------------------------------------------------------------------------
1210-FEB-20 09.50.54.243180 AM -05:00
2210-FEB-20 09.50.54.309027 AM -05:00
TYP_DML_STRING()

[Updated on: Mon, 10 February 2020 09:02]

Report message to a moderator

Re: Simultaneous DML executions on different objects in the same session [message #679139 is a reply to message #679138] Mon, 10 February 2020 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
When I run your code I get 0 for both TABLE_12_UPDATE_TIME, TABLE_22_UPDATE_TIME.
Which is to be expected - oracle read consistency means you get the data at the point in time the query started, not after x seconds of sleep time.
To get your output you have to run it a second time - and then the times it the outputs are the times of the previous execution.

The fnc_sleep does nothing useful here.
Re: Simultaneous DML executions on different objects in the same session [message #679140 is a reply to message #679139] Mon, 10 February 2020 09:50 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
No objections. Are update dates close to each other in 1 second limit?
Re: Simultaneous DML executions on different objects in the same session [message #679141 is a reply to message #679140] Mon, 10 February 2020 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
1210-FEB-20 03.41.07.876141 PM +00:00
2210-FEB-20 03.41.07.876911 PM +00:00
Re: Simultaneous DML executions on different objects in the same session [message #679142 is a reply to message #679141] Mon, 10 February 2020 10:00 Go to previous messageGo to next message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
Good. Target accomplished.
Re: Simultaneous DML executions on different objects in the same session [message #679143 is a reply to message #679142] Mon, 10 February 2020 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Really?
If I run:
BEGIN
update test12 set l_str2=to_char(12)||to_char(current_timestamp(6));
update test22 set l_str2=to_char(22)||to_char(current_timestamp(6));

END;
And then query them I get:
1210-FEB-20 03.55.00.386554 PM +00:00
2210-FEB-20 03.55.00.387262 PM +00:00
Which is almost the same difference - 0.000708 vs 0.000770 seconds.

Re: Simultaneous DML executions on different objects in the same session [message #679144 is a reply to message #679143] Mon, 10 February 2020 10:38 Go to previous message
Olexandr Siroklyn
Messages: 27
Registered: September 2018
Location: USA
Junior Member
Well, dear cookiemonster, below is the third variant with a 5 second delay and small modifications. If statements are executed one-by-one you get 5 second date difference. I don't get.

create table tbl_pl_manager as select * from dual;
/
create or replace view viw_pl_manager
as
select 1 as THREAD_ID from tbl_pl_manager where fnc_pl_manager=1
union
select 2 as THREAD_ID from tbl_pl_manager where fnc_pl_manager=1;
/
create or replace package pkg_pl_manager
as
         type rc_viw_pl_manager is ref cursor return viw_pl_manager%rowtype;
     function fnc_submit (p_task_list in typ_dml_string, p_pl_refcur in rc_viw_pl_manager )
              return typ_dml_string
              parallel_enable(partition p_pl_refcur by any)
              pipelined
              deterministic;
    procedure prc_launch(l_what in varchar2);
end pkg_pl_manager;
/
create or replace package body pkg_pl_manager
as
--------------------------------
procedure prc_launch(l_what in varchar2)
is
pragma autonomous_transaction;
begin
    dbms_lock.sleep(5);
    execute immediate l_what;
    commit;
end prc_launch;      
--------------------------------
function fnc_submit(p_task_list in typ_dml_string, p_pl_refcur in rc_viw_pl_manager)
return typ_dml_string
parallel_enable(partition p_pl_refcur by any)
pipelined
deterministic
is
    pointer_viw_pl_manager viw_pl_manager%rowtype;
begin
    loop
    fetch p_pl_refcur into pointer_viw_pl_manager;
    exit when p_pl_refcur%notfound;
    prc_launch(p_task_list(pointer_viw_pl_manager.thread_id));
    end loop;
    return;
end fnc_submit;
---------------------------------
end pkg_pl_manager;
/

SQL> select pkg_pl_manager.fnc_submit(typ_dml_string('update test12 set l_str2=to_char(12)||to_char(current_timestamp(6))',
  2                                                  'update test22 set l_str2=to_char(22)||to_char(current_timestamp(6))'),
  3                                     cursor(select THREAD_ID from viw_pl_manager)) as A
  4    from dual;

A
------------------------------------------------------------------------------------------------------------------------
TYP_DML_STRING()

SQL> select L_STR2 from test12
  2  union
  3  select L_STR2 from test22;

L_STR2
------------------------------------------------------------------------------------------------------------------------
1210-FEB-20 11.34.19.584799 AM -05:00
2210-FEB-20 11.34.19.584009 AM -05:00

[Updated on: Mon, 10 February 2020 10:44]

Report message to a moderator

Previous Topic: Previous week's first and last day
Next Topic: ORA-20200: ORA-0000: normal, successful completion when executing PCRD_DF_MANAGEMENT.DF_MANAGE ORA
Goto Forum:
  


Current Time: Thu Aug 06 17:09:02 CDT 2020