Skip to main content

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.
Labels:

Comments

  1. UnknownApril 11, 2018 at 7:58 AM

    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. Steven FeuersteinApril 11, 2018 at 8:04 AM

    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. JohnApril 12, 2018 at 5:54 AM

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

    ReplyDelete
    Replies
    1. Steven FeuersteinApril 12, 2018 at 6:05 AM

      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. iudithApril 15, 2018 at 12:59 PM

    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. Steven FeuersteinApril 16, 2018 at 12:33 PM

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

      Delete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more