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.

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

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

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel