Transaction subtleties

This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out.

Lets set the scene with a simple procedure that commences a transaction, but then always fails


SQL> drop table test$tab purge;

Table dropped.

SQL>
SQL> create table test$tab (val varchar2(1));

Table created.

SQL>
SQL> create or replace  procedure foo_proc is
  2  begin
  3    dbms_output.put_line('in foo_proc');
  4    insert into test$tab values ('t');
  5    raise_application_error(-20001,'Error');
  6  end;
  7  /

Procedure created.

One of the cool things about PL/SQL is its nice handling of transaction processing, or often described with a statement along the lines of: “A PL/SQL procedure acts as a single unit of work”.

We can see a simple demonstration of this by running the procedure we just created


SQL> exec foo_proc
in foo_proc
BEGIN foo_proc; END;

*
ERROR at line 1:
ORA-20001: Error
ORA-06512: at "MCDONAC.FOO_PROC", line 5
ORA-06512: at line 1


SQL> select * from test$tab;

no rows selected

and as expected, the insertion was rolled back, to an “unseen” savepoint before the call to the procedure.

Simple enough eh ? Well no…there is more to the picture here. Let’s now call that procedure in some different scenarios (colour coding mine)

  • From an anonymous block
  • Using native dynamical SQL
  • Using DBMS_SQL

SQL> set serverout on
SQL> declare
  2    stmt varchar2(200);
  3    c    number;
  4    i    number;
  5
  6    function count_rows return number is
  7      l_cnt number;
  8    begin
  9      select count(*) into l_cnt from test$tab t;
 10
 11      return l_cnt;
 12    end;
 13
 14  begin
 15
 16    delete from test$tab;
 17    begin
 18      foo_proc;
 19    exception
 20      when others then
 21        dbms_output.put_line('static call - ' || count_rows);
 22    end;
 23
 24    delete from test$tab;
 25    begin
 26      execute immediate 'begin foo_proc; end;';
 27    exception
 28      when others then
 29        dbms_output.put_line('nds - ' || count_rows);
 30    end;
 31
 32    delete from test$tab;
 33    begin
 34      stmt := 'begin foo_proc; end;';
 35
 36      i := dbms_sql.open_cursor;
 37      dbms_sql.parse(c => i, statement => stmt, language_flag => dbms_sql.native);
 38      c := dbms_sql.execute(i);
 39      dbms_sql.close_cursor(i);
 40    exception
 41      when others then
 42        dbms_output.put_line('dbms_sql - ' || count_rows);
 43        if dbms_sql.is_open(i)
 44        then
 45          dbms_sql.close_cursor(i);
 46        end if;
 47    end;
 48  end;
 49  /
in foo_proc
static call - 1

in foo_proc
nds - 0

in foo_proc
dbms_sql - 0

PL/SQL procedure successfully completed.

The results appear to be inconsistent, but they are not. This is working as designed, and is documented in the PLSQL manual.

From http://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS850

“If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram.”

So that takes care of the first test above  – we called a subprogram, we did the change and did not roll it back. So what of the last two cases ?

These are just examples of standard statement level atomicity, ie, a statement works in its entirety or it doesn’t. In each case, we have executed a single “SQL” statement, which failed, so we roll back the statement.

And just to clarify further, if we remove the exception handler from test case #1, ie, so that the error is returned to the calling environment, then it is the anonymous block itself that become the single statement, and hence the change is rolled back




SQL> set serverout on
SQL> declare
  2    stmt varchar2(200);
  3    c    number;
  4    i    number;
  5
  6    function count_rows return number is
  7      l_cnt number;
  8    begin
  9      select count(*) into l_cnt from test$tab t;
 10
 11      return l_cnt;
 12    end;
 13
 14  begin
 15
 16    delete from test$tab;
 17    commit;
 18    begin
 19      foo_proc;
 20  --  exception
 21  --    when others then
 22  --      dbms_output.put_line('static call - ' || count_rows);
 23    end;
 24
 25    delete from test$tab;
 26    commit;
 27    begin
 28      execute immediate 'begin foo_proc; end;';
 29    exception
 30      when others then
 31        dbms_output.put_line('nds - ' || count_rows);
 32    end;
 33
 34    delete from test$tab;
 35    commit;
 36    begin
 37      stmt := 'begin foo_proc; end;';
 38
 39      i := dbms_sql.open_cursor;
 40      dbms_sql.parse(c => i, statement => stmt, language_flag => dbms_sql.native);
 41      c := dbms_sql.execute(i);
 42      dbms_sql.close_cursor(i);
 43    exception
 44      when others then
 45        dbms_output.put_line('dbms_sql - ' || count_rows);
 46        if dbms_sql.is_open(i)
 47        then
 48          dbms_sql.close_cursor(i);
 49        end if;
 50    end;
 51  end;
 52  /
in foo_proc
declare
*
ERROR at line 1:
ORA-20001: Error
ORA-06512: at "MCDONAC.FOO_PROC", line 5
ORA-06512: at line 19


SQL>
SQL> select count(*) from test$tab t;

  COUNT(*)
----------
         0

1 row selected.

2 thoughts on “Transaction subtleties

  1. IMHO, foo_proc() should be made “atomic” (ie the “A” in ACID) by having an exception handler do a rollback to an explicit savepoint in the function, so the caller doesn’t have to worry about side effects.
    The function can reraise the exception so it can be handled by the caller.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s