Statement-Level Atomicity

[UPDATE 2014/11/17: Horrible copy/paste error! My PL/SQL code below left out the all-important RAISE; command in the exception handler. Many thanks to Dom Brooks for giving me a heads up.]

So important yet often ignored or misunderstood! No, not me, but “statement level atomicity”.

Whenever you call the Oracle database to change data, the result will be all or nothing: Oracle will either do everything you asked it to, or nothing at all. This is true for SQL and PL/SQL.

What the documentation says

From the Concepts guide:

If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback. This operation has the following characteristics:

  • A SQL statement that does not succeed causes the loss only of work it would have performed itself.The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction…
  • The effect of the rollback is as if the statement had never been run.

There is no specific mention of PL/SQL here. I asked the author, Tom Kyte, about PL/SQL “statements” on asktom.oracle.com, “unhandled exceptions”. He replied:

any “statement” is an atomic statement.
every “statement” is
plsql is just a statement, so is update, they are all just statements.
all statements in Oracle are atomic…

I kept searching the Oracle documentation and finally found this in the TimesTen PL/SQL Developer’s Guide:

TimesTen PL/SQL differs from Oracle Database PL/SQL in a scenario where an application executes PL/SQL in the middle of a transaction, and an unhandled exception occurs during execution of the PL/SQL. Oracle Database rolls back to the beginning of the anonymous block. TimesTen does not roll back.

So there it is in black and white: it doesn’t matter whether you call the SQL engine or the PL/SQL engine, your “statement” either makes all the changes you asked for or none at all.

What Steven Feuerstein says

This blog was inspired by Steven’s recent tweet:

This may seem to contradict the Oracle documentation, but in fact it doesn’t. Steven is talking about what happens inside the PL/SQL code, whereas the documentation refers to what happens after the PL/SQL code has finished.

What my tests show

Here is a simple test case that shows how Steven is right.

CREATE TABLE T (N NUMBER);

set serveroutput on

declare
  procedure show_cnt(p_label in varchar2) is
    l_cnt number;
  begin
    select count(*) into l_cnt from t;
    dbms_output.put_line(p_label || ', count(*) = ' || l_cnt);
  end show_cnt;
begin
  show_cnt('At beginning');
  insert into t values(1);
  show_cnt('After good insert');
  insert into t values('a');
exception when invalid_number then
  show_cnt('After bad insert');
  -- the following line reraises the exception
  RAISE;
end;
/
Error report -
ORA-01722: invalid number
ORA-06512: at line 16
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.
At beginning, count(*) = 0
After good insert, count(*) = 1
After bad insert, count(*) = 1

select count(*) from t;

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

select count(*) trans_cnt
from v$transaction;

 TRANS_CNT
----------
         0

Notice how the first insert did NOT get rolled back within the PL/SQL block, but it DID get rolled back after the block ended!
Notice also that, since the PL/SQL block was the first statement in a transaction, there is no transaction anymore. The situation is exactly as if the statement never ran in the first place.

Conclusion

  • When a “client” calls the Oracle database, it doesn’t matter whether it calls the SQL engine or the PL/SQL engine, the entire call is a “statement” that will always be atomic.
  • When PL/SQL (inside the database) calls the SQL engine, the SQL statement is atomic. If the execution goes wrong, only that SQL statement is rolled back.
  • PL/SQL execution “goes wrong” if and only if an exception is passed back to the calling program. Without the RAISE in my exception handler, the exception would never go back to the caller and Oracle would not roll back the statement!
  • If the PL/SQL code does a COMMIT or ROLLBACK itself, statement-level atomicity will not work as intended.
Advertisements

2 thoughts on “Statement-Level Atomicity

  1. Hi Stew,

    I believe you have the wrong results from your plsql block and have therefore come to a misleading conclusion.
    In your code above, you are handling and not re-raising invalid_number therefore the block should not rollback.

    select count(*) from t;
     
    declare
      procedure show_cnt(p_label in varchar2) is
        l_cnt number;
      begin
        select count(*) into l_cnt from t;
        dbms_output.put_line(p_label || ', count(*) = ' || l_cnt);
      end show_cnt;
    begin
      show_cnt('At beginning');
      insert into t values(1);
      show_cnt('After good insert');
      insert into t values('a');
    exception when invalid_number then
      show_cnt('After bad insert');
    end;
    /
    
    select count(*) from t;
    

    gives

      COUNT(*)
    ----------
             0 
    
    anonymous block completed
    At beginning, count(*) = 0
    After good insert, count(*) = 1
    After bad insert, count(*) = 1
    
      COUNT(*)
    ----------
             1 
    

    If your PLSQL block was re-raising the exception and leaving it unhandled then the block would roll back.

    rollback;
    select count(*) from t;
     
    declare
      procedure show_cnt(p_label in varchar2) is
        l_cnt number;
      begin
        select count(*) into l_cnt from t;
        dbms_output.put_line(p_label || ', count(*) = ' || l_cnt);
      end show_cnt;
    begin
      show_cnt('At beginning');
      insert into t values(1);
      show_cnt('After good insert');
      insert into t values('a');
    exception when invalid_number then
      show_cnt('After bad insert');
      raise;
    end;
    /
    
    select count(*) from t;
    

    gives

    rollback complete.
      COUNT(*)
    ----------
             0 
    
    Error starting at line : 10 in command -
    
    ORA-01722: invalid number
    ORA-06512: at line 15
    01722. 00000 -  "invalid number"
    *Cause:    
    *Action:
    
    At beginning, count(*) = 0
    After good insert, count(*) = 1
    After bad insert, count(*) = 1
    
      COUNT(*)
    ----------
             0 
    

    I wonder if you’re using SQL Developer and falling foul of it’s default behaviour of running scripts in separate sessions?

    Regards,
    Dominic

    • Hello Dominic,

      Thanks so much for pointing this out! It was not SQL Developer’s fault. I tested with RAISE and without, then copied and pasted the wrong test :-(

      I have corrected the post.

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