oracle-databaseplsqltransactionsoracle21c

Differentiate between main transaction and autonomous transaction?


Is there an Oracle interface which tells you if the current transaction is the main transaction or an autonomous transaction withn PL/SQL context?

I know how to get the current transaction ID:

dbms_transaction.local_transaction_id

This would tell me if I'm within a transaction or not. But I need to further examine if the current transaction is the main transaction or if some code opened an autonomous transaction.

Use case / background:

During a test with utplsql there may be the need to differentiate between the main transaction and an autonomous transaction. For instance to see if the logic was correctly executed within an autonomous transaction. However, we are not sure yet about the test design in such cases so we want to see our options.

Surely, the autonomous transaction is only used during exception handling to create records which must not be rolled back.


Solution

  • When you start a PL/SQL block marked as autonomous, your current transaction pointer (v$session.taddr) is NULL'd out even though the current transaction still exists and is still found in v$transaction. When you do your first DML operation within that autonomous transaction layer, you now have two transaction entries in v$transaction, but your transaction pointer (v$session.taddr) points to only the last/current one. That makes sense, as v$session which has only one row per session can only provide you with one transaction address for that one row, whereas you actually have two.

    Fortunately, taddr/addr is not the only link between these views. You also have ses_addr/saddr going the other way. So you can use this to test if there is another transaction you own but which is not the current one (the transaction addresses don't match):

        SELECT COUNT(*)
          FROM v$transaction t,
               v$session s
         WHERE s.sid = SYS_CONTEXT('USERENV','SID')
           AND t.ses_addr = s.saddr      -- belongs to me
           AND (s.taddr IS NULL OR s.taddr <> t.addr);  -- but not my current transaction
    

    If you get something other than 0, you are in an autonomous transaction.

    You will of course need the SELECT ANY DICTIONARY priv to see these views. Testing it out:

    DECLARE
      FUNCTION i_am_autonomous
        RETURN varchar2
      AS
        var_exists integer;
      BEGIN
        SELECT COUNT(*)
          INTO var_exists
          FROM v$transaction t,
               v$session s
         WHERE t.ses_addr = s.saddr      
           AND s.sid = SYS_CONTEXT('USERENV','SID')
           AND (s.taddr IS NULL OR s.taddr <> t.addr);
    
        IF var_exists = 0
        THEN
          RETURN 'N';
        ELSE
          RETURN 'Y';
        END IF;
      END i_am_autonomous;
      
      PROCEDURE ptest
      AS
        PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
        dbms_output.put_line('inside autonomous but before inner transaction start:'||i_am_autonomous);
        INSERT INTO tmp1 VALUES (2);
        dbms_output.put_line('after inner transaction:'||i_am_autonomous);
    
        ROLLBACK;
      END ptest;
    BEGIN
      INSERT INTO tmp1 VALUES (1);
      dbms_output.put_line('outer transaction before call:'||i_am_autonomous);
      ptest;
      dbms_output.put_line('outer transaction after call:'||i_am_autonomous);
      ROLLBACK;
    END;
    

    Output:

    outer transaction before call:N
    inside autonomous but before inner transaction start:Y
    after inner transaction:Y
    outer transaction after call:N