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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…

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 perspective…