ORA-600 [4156] SAVEPOINT and PL/SQL Exception Handling

Oracle MOS (RDBMS 10.2.0.4)
     Bug 9471070 : ORA-600 [4156] GENERATED WITH EXECUTE IMMEDIATE AND SAVEPOINT
contains a TEST CASE:

create table t(id number, label varchar2(10));
insert into t(id, label) values(1, 'label');
commit;

-- ORIGINAL --
begin
  savepoint sp;
  update t set label = label where id = 1;
  execute immediate '
    begin
      raise_application_error(-20000, ''error-sp'');
    exception
      when others then
        rollback to savepoint sp;
        update t set label = label where id = 1;
        raise;
    end;';
end;
/

which outputs the Error:

ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []

and DIAGNOSTIC ANALYSIS wrote:

The ORA-600[4156] error means that we are checking the savepoint undo block address while rolling back the transaction. The savepoint does not currently belong to this transaction. So the operation in this testcase does not seem supported, but there is no documentation on this behavior.

In this Blog, we will try to deduce 4 further Variants which all generate the same error, and demonstrate that neither Exception Handler nor Execute Immediate is a necessary condition of such error.

In the original Test Case,

raise_application_error(-20000, ''error-sp''); 

is only to transfer code flow to exception handler, remove it, we get:

-- VARIANT-1 --
begin
  savepoint sp;
  update t set label = label where id = 1;
  execute immediate '
    begin
      rollback to savepoint sp;
      update t set label = label where id = 1;
      raise_application_error(-20000, ''error-sp'');
    end;';
end;
/

Unshelling EXECUTE IMMEDIATE, it is a derived Test Case:

-- VARIANT-2 --
savepoint sp;
update t set label = label where id = 1;           
begin
  rollback to savepoint sp;
  update t set label = label where id = 1;
  raise_application_error(-20000, 'error-sp');
end;
/

all generate the same ORA-600 [4156].

Applying the equivalent transformation to factor out:

raise_application_error(-20000, 'error-sp'); 

(See Book Expert Oracle Database Architecture (3rd Edition, Thomas Kyte, Darl Kuhn) - Chapter 8, Section: Atomicity (Page 277-283))

Savepoint sp;
statement;
If error then rollback to sp;

We render the Test Case as:

-- VARIANT-3 --
savepoint sp;
update t set label = label where id = 1;   
savepoint spx;        
begin
  rollback to savepoint sp;
  update t set label = label where id = 1;
  rollback to savepoint spx;    -- spx cleared away by "rollback to savepoint sp;"
  dbms_output.put_line('error-sp');
end;
/

it results in the same error:

ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
ORA-01086: savepoint 'SPX' never established in this session or is invalid

which contains a second line indicating that the error is related to savepoint 'SPX'.

None of Exception Handler and Execute Immediate is involved in the above Test Code.

All above Test Cases generate the similar incident dumps as follows:

kturRollbackToSavepoint perm undokturRollbackToSavepoint savepoint uba: 0x00c0525c.0708.30 xid: 0x0051.021.00002fcd
kturRollbackToSavepoint current call savepoint: ksucaspt num: 1737  uba: 0x00c0525c.0708.30

Reasoning

Looking at the last transformed code,
  "rollback to savepoint sp"
jumps back to line
  "savepoint sp"
and hence scopes out line "savepoint spx" and make savepoint 'SPX' invisible. When running to the line "rollback to savepoint spx", 'SPX' is not able to find, and therefore the error: never established.

The graphic below depicts the partial intersection of savepoints' pair, which erases the 'savepoint spx' with runtime "goto" logic and makes it never reachable. A savepoints' pair is valid if they are pairwise disjoint, or one is a proper subset of another.

|------> savepoint sp;
|        update t set label = label where id = 1;   
|  |---> savepoint spx;        
|  |     begin
|--|---->  rollback to savepoint sp;
   |       update t set label = label where id = 1;
   |--->   rollback to savepoint spx; 
         end;

With the aid of above deliberate code transformations, we obtained 4 versions of code which produced the same error. It is not clear if they are all semantically equivalent, and internally identical.

Referring back to DIAGNOSTIC ANALYSIS again, it said:
   So the operation in this testcase does not seem supported, but there is no documentation on this behavior.

All tests are done in Oracle 11.2.0.4.0 and 12.1.0.2.0.

Implicit Savepoint and Execute immediate 

When unshelling EXECUTE IMMEDIATE in the second transformation, outermost BEGIN END is intentionally removed.

Without removing it, the equivalent code looks like:

savepoint spx;
begin
  savepoint sp;
  update t set label = label where id = 1;           
  begin
    rollback to savepoint sp;
    update t set label = label where id = 1;
    rollback to savepoint spx; 
    dbms_output.put_line('error-sp');
  end;
end;
/
--If error then rollback to spx;

It runs without error since there does not exist savepoint partially overlapping ('SP' is proper subset of 'SPX').

It seems that Oracle sets an implicit savepoint before EXECUTE IMMEDIATE to perform the dynamic statement parsing and executing. Factoring out EXECUTE IMMEDIATE by the same principle, we get a fourth variant which hits the same error:

-- VARIANT-4 --
begin
  savepoint sp;
  update t set label = label where id = 1;
  savepoint spx;
  execute immediate '
    begin
        rollback to savepoint sp;
        update t set label = label where id = 1;
        rollback to savepoint spx;
        dbms_output.put_line(''error-sp'');
    end;';
end;
/
--If error then rollback to spx;

Oracle Statement-Level Rollbacks said:
   Before executing any SQL statement, Oracle marks an implicit savepoint (not available to you).

Update (September 13, 2020), see Next Blog: The Second Test of ORA-600 [4156] Rolling Back To Savepoint.

ORA-01086

One ORA-01086 can be simply reproduced by:

SQL> rollback to savepoint spy;
  ORA-01086: savepoint 'SPY' never established in this session or is invalid

Savepoint Watching

Savepoint is probably implemented as Stack push and pop operations.

Looking ORA-600 [4156] trace and incident dumps, stack trace shows that subroutine psdtsv calls xctsav to set Savepoint, ksupop to popup Savepoint and xctrsp to rollback savepoint..

One can also run code below,

begin
  for i in 1..1000000 loop
    dbms_transaction.savepoint('sp'||i);
    dbms_transaction.rollback_savepoint('sp'||i);
  end loop;
end;
/

and print process stack trace by UNIX command, for example, Solaris, pstack.

References

1. Expert Oracle Database Architecture(Page 277-283)
2. Oracle Statement-Level Rollbacks
3. SQL DML Exceptions, Rollbacks and PL/SQL Exception Handlers