Nested blocks, autonomous transactions and "Where do I commit?"

This question rolled into my In Box today:
If I have a procedure that is AUTONOMOUS_TRANSACTION that does an insert and then it calls a procedure with an insert, does the second procedure need a commit, or will the procedure with the AUTONOMOUS_TRANSACTION handle the commit? If you don't know off the top of your head, don't worry, I can build a test.
First of all, if you ever find yourself writing something like "If you don't know off the top of your head, don't worry, I can build a test." then please by all means go right ahead and build yourself a test script.

By doing so, you will better understand the feature in question and remember what you learned. Plus you end up with a script you can share with the community on LiveSQL.

But I don't mind answering such questions. That way I get to better understand the feature in question, remember what I learned, share a script on LiveSQL (link at bottom of post), and also add to my blog. :-)

So here goes: the answer is NO. The "second" procedure - invoked by the first - does not have to include a COMMIT statement.

Would you like proof or more information about the autonomous transaction feature of PL/SQL? Then keep reading.

When you add this statement to the declaration section of a procedure or function...

PRAGMA AUTONOMOUS_TRANSACTION;

the following rule then applies:
Before the subprogram can be closed and control passed back to the calling block, any DML changes made within that subprogram must be committed or rolled back.
If there are any unsaved changes, the PL/SQL engine will raise the ORA-06519 exception, as shown below:

CREATE OR REPLACE FUNCTION nothing RETURN INTEGER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE employees SET last_name = 'abc';

   RETURN 1;
END;
/

BEGIN
   DBMS_OUTPUT.put_line (nothing);
END;
/

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "STEVEN.NOTHING", line 10
ORA-06512: at line 2

OK, so that's the basic idea. Now let's move on the specific question. What if an autonomous transaction procedure calls another procedure, which does not include the pragma shown above but does execute a DML statement and does not commit? Will we see an ORA-06519 error? The code below shows that we will not.

CREATE TABLE me_and_my_lovelies (name VARCHAR2 (100));

BEGIN
   INSERT INTO me_and_my_lovelies VALUES ('Grandpa Steven');
   INSERT INTO me_and_my_lovelies VALUES ('Loey');
   INSERT INTO me_and_my_lovelies VALUES ('Juna');
   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE not_auton_no_commit
   AUTHID DEFINER
IS
BEGIN
   UPDATE me_and_my_lovelies
      SET name = UPPER (name);
END not_auton_no_commit;
/

CREATE OR REPLACE PROCEDURE auton_commit
   AUTHID DEFINER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   not_auton_no_commit ();

   COMMIT;
END auton_commit;
/

BEGIN
   auton_commit;
END;
/

SELECT COUNT(*) low_name
  FROM me_and_my_lovelies
 WHERE name <> UPPER (name)
/

LOW_NAME
--------
0

No error is raised. All rows have been updated. So let's go back to the rule:
Before the subprogram can be closed and control passed back to the calling block, any DML changes made within that subprogram must be committed or rolled back.
You might be thinking: But the UPDATE statement (the "DML change") was not made "within" auton_commit. Yes and no. Yes, the UPDATE statement is not part of the text of auton_commit. But the UPDATE statement was executed within the scope of auton_commit. And that's what counts. Any code executed by auton_commit, either "directly" in its executable section or "indirectly" by invoking another subprogram, is part of the autonomous transaction.

The only point at which the rule is applied is when PL/SQL attempts to close auton_commit and return control to the outer block.

LiveSQL script containing the above code here.

More information about autonomous transactions here.

Comments

  1. Steven, Buenos dias.

    Siguiendo el ejemplo del AUTONOMOUS_TRANSACTION tengo una pregunta, como determinas o que punto es optimo en un store que con un CFL o OPEN-FETCH-CLOSE procesa muchos registros, cada cuantos DML debes realizar el commit parcial de la transaccion.?

    por ejemplo.

    create or replace procedure strP_bullk_insert
    is
    cursor c_data is select * from tablaOrigen;
    --
    type deforigen is table of tablaOrigen%rowtype index by pls_integer;
    Regorigen deforigen;
    type deftemporal is table of tablaDestino%rowtype index by pls_integer;
    Regtemporal deftemporal ;
    --
    procedure insert_data
    is
    pragma AUTONOMOUS_TRANSACTION;
    begin
    forall indx in indices of Regtemporal
    insert into tablaDestino values Regtemporal(indx);
    commit;
    end;
    --
    begin
    open c_data;
    loop
    fetch c_data bulk collect into Regorigen limit 5000; --Limite a determinar
    pkg_process.do_stuff (Regorigen /*in*/, Regtemporal /*out*/);
    insert_data;
    exit when c_data%notfound;
    end loop;
    end;

    ReplyDelete
  2. A translation for all readers of the previous comment:

    Following the example of the AUTONOMOUS_TRANSACTION I have a question, how do you determine or which point is optimal in a store that processes a lot of records with a CFL or OPEN-FETCH-CLOSE, how many DML do you have to perform the partial commit of the transaction?

    When it comes to committing (defining your transaction), "optimal" MUST MUST MUST be determined by application requirements. That is, make sure that a "partial commit" is even acceptable.

    Assuming it is, the primary driver in the context of FORALL is likely to be how much PGA you can consume (use for your collection). The larger the array, the faster the overall processing. There is no set values to offer you here. You will likely need to do some testing and see how high you can go, and what kind of benefits in performance you see.

    Sorry, I hope this was awful. Mostly the answer, as so often, is: It depends.

    Ideas from others?

    ReplyDelete
  3. My view on that would be - Commit when your transaction is complete - never before.

    ReplyDelete
    Replies
    1. Generally, I agree, and perhaps even qualify with: commit when your users say to commit (ie, for the most part, you do NOT commit in your PL/SQL code). But of course sometimes incremental commits are needed, but that is a special case and generally we are not talking about a "transaction" in this case, but a "job" to be completed.

      Delete
  4. Hello All,

    In this context, maybe it's worth mentioning that, since the usual purpose of an
    autonomous transaction is "to be self-contained", it is good practice not to forget
    about error handling, so that to ensure a correct logic for the entire scenario.

    Here is a small example:

    create table mytab(n int primary key, x varchar2(10))
    /

    create or replace procedure p
    as
    begin
    insert into mytab values (1,'a');
    insert into mytab values (1,'b');
    end;
    /

    create or replace procedure p_auto
    as
    pragma autonomous_transaction;
    begin
    insert into mytab values (1,'a');
    insert into mytab values (1,'b');
    commit;
    end;
    /

    declare
    l_count pls_integer;
    begin
    begin
    dbms_output.put_line('calling p ...');
    p;
    exception
    when others then
    dbms_output.put_line(sqlerrm);
    end;

    select count(*) into l_count from mytab;
    dbms_output.put_line('Count after p = '||l_count);


    delete from mytab;
    commit;

    begin
    dbms_output.put_line('calling p_auto ...');
    p_auto;
    exception
    when others then
    dbms_output.put_line(sqlerrm);
    end;

    select count(*) into l_count from mytab;
    dbms_output.put_line('Count after p_auto = '||l_count);
    end;
    /

    calling p ...
    ORA-00001: unique constraint (SQL_HRDBRRMJRLOMZOKVMPFGGAKZB.SYS_C001753159) violated
    Count after p = 1

    calling p_auto ...
    ORA-00001: unique constraint (SQL_HRDBRRMJRLOMZOKVMPFGGAKZB.SYS_C001753159) violated
    Count after p_auto = 0


    In other words, when an autonomous procedure terminates with an unhandled exception,
    then all the uncommitted changes performed in that procedure are rolled back before the exception propagates further.

    For a non-autonomous procedure, this is different.


    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Thanks, Iudith. That is a wonderful appendix to this blog post.

      Delete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Quick Guide to User-Defined Types in Oracle PL/SQL

Recommendations for unit testing PL/SQL programs