Oracle: calling a procedure via DBLINK does not rollaback to savepoint in tables that are over another dblink - Stack Overflow

admin管理员组

文章数量:1400494

i have a 19c installtion and i'm trying to understand why somedata i still written when i use a savepoint. One database is a non-CDB (DB1) and the other is a PDB (PDB2). Each databases have dblinks to each others.

I have this procedure in DB1 that i made to reproduce the error. The aim is to have a loop to analyze data. If an error is found, ignore the error and continue with the next row.

Here i will try to insert a row in a table that live in PDB2 database (TEST_TABLE) and a row in a table (AA_TEST2) that lives in the DB that host the procedure (DB1)

create PROCEDURE AA_SP_TEST2B

BEGIN
      DECLARE
        var_ID INTEGER;
      BEGIN
        var_ID := 0;
        DELETE FROM AA_TEST2 t;
        DELETE FROM REMOTE_TABLE;
        WHILE var_ID < 20
        LOOP
          BEGIN
            SAVEPOINT START_LOOP;
            INSERT INTO AA_TEST2 ( ID, DES, DATA ) VALUES ( var_ID, 'LOC', SYSDATE);
            INSERT INTO MY_user.REMOTE_TABLE@PDB2( ID, VARCHAR1 ) VALUES( var_ID ,'REM');
            IF var_ID > 4 AND var_ID/2 = TRUNC(var_ID/2)  THEN
              INSERT INTO AA_TEST2 ( DATA ) VALUES ('PIPPO');
            END IF;
            var_ID := var_ID + 1;
          EXCEPTION
              WHEN OTHERS THEN
                ROLLBACK TO START_LOOP;
            END;
        END LOOP;
      END; 
      COMMIT;
END;
/

I'm calling this procedure FROM pdb2, that is also the database where TEST_TABLE lives:

BEGIN
  DB1_USER.AA_SP_TEST2B@DB1;
END;

The procedure is simple: when i reach a var_ID that is ODD and exception should occour, so will find in the table only rows with odds numbers becouse of the SAVEPOINT and ROLLBACK declarations.

What is see instead is that:

• On TEST_TABLE@PDB2 i have odds and even rows: it seems like the rollback to savepoint didn't occour.
• On the AA_TEST2 i have only odds rows.

I don't understand what i'm missing here.

This procedure is called from SQL Developer on PDB2 user My_user.

The only thing i noticed is that if i add the PRAGMA AUTONOMOUS_TRANSACTION to this procedure it seems to work right: the even rows are not inserted in the REMOTE_TEST table and the local AA_TEST2 Table.

Any hint??

i have a 19c installtion and i'm trying to understand why somedata i still written when i use a savepoint. One database is a non-CDB (DB1) and the other is a PDB (PDB2). Each databases have dblinks to each others.

I have this procedure in DB1 that i made to reproduce the error. The aim is to have a loop to analyze data. If an error is found, ignore the error and continue with the next row.

Here i will try to insert a row in a table that live in PDB2 database (TEST_TABLE) and a row in a table (AA_TEST2) that lives in the DB that host the procedure (DB1)

create PROCEDURE AA_SP_TEST2B

BEGIN
      DECLARE
        var_ID INTEGER;
      BEGIN
        var_ID := 0;
        DELETE FROM AA_TEST2 t;
        DELETE FROM REMOTE_TABLE;
        WHILE var_ID < 20
        LOOP
          BEGIN
            SAVEPOINT START_LOOP;
            INSERT INTO AA_TEST2 ( ID, DES, DATA ) VALUES ( var_ID, 'LOC', SYSDATE);
            INSERT INTO MY_user.REMOTE_TABLE@PDB2( ID, VARCHAR1 ) VALUES( var_ID ,'REM');
            IF var_ID > 4 AND var_ID/2 = TRUNC(var_ID/2)  THEN
              INSERT INTO AA_TEST2 ( DATA ) VALUES ('PIPPO');
            END IF;
            var_ID := var_ID + 1;
          EXCEPTION
              WHEN OTHERS THEN
                ROLLBACK TO START_LOOP;
            END;
        END LOOP;
      END; 
      COMMIT;
END;
/

I'm calling this procedure FROM pdb2, that is also the database where TEST_TABLE lives:

BEGIN
  DB1_USER.AA_SP_TEST2B@DB1;
END;

The procedure is simple: when i reach a var_ID that is ODD and exception should occour, so will find in the table only rows with odds numbers becouse of the SAVEPOINT and ROLLBACK declarations.

What is see instead is that:

• On TEST_TABLE@PDB2 i have odds and even rows: it seems like the rollback to savepoint didn't occour.
• On the AA_TEST2 i have only odds rows.

I don't understand what i'm missing here.

This procedure is called from SQL Developer on PDB2 user My_user.

The only thing i noticed is that if i add the PRAGMA AUTONOMOUS_TRANSACTION to this procedure it seems to work right: the even rows are not inserted in the REMOTE_TEST table and the local AA_TEST2 Table.

Any hint??

• oracle-database
• stored-procedures
• plsql
• transactions
• dblink

Share Improve this question Follow asked Mar 26 at 5:26 execcrexeccr 2366 bronze badges 1

• Where is TEST_TABLE in your code? Do you mean REMOTE_TABLE ? – Paul W Commented Mar 26 at 11:29

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

We're supposed to control distributed transactions from the initiating session, not on the remote end. If you try to do things on the remote, you get strange behavior.

As of 19c, an attempt by the remote procedure to perform a rollback will rollback only the work that the remote session has done - not the work that the local initiating session may have done. The remote procedure call creates an internal savepoint (I believe called $TxnBeginInternalSavepoint$), and a rollback on that remote automatically becomes, effectively, rollback to savepoint $TxnBeginInternalSavepoint$. A commit, on the other hand, results in a commit on the local side as well (the entire distributed transaction).

When you add to this a DML over a second link - in your case a loopback to the originating database (INSERT INTO MY_user.REMOTE_TABLE@PDB2) - you have three database sessions involved in an even more complex distributed transaction. In that scenario, a rollback in the remote should throw an exception, ORA-55714: savepoint '$TxnBeginInternalSavepoint$' disabled with interleaved changes across Oracle RAC instances or something to that effect. Perhaps this behaves differently on non-RAC, and that might explain why you aren't getting an error, just not the behavior you expect.

When you use a named savepoint (like START_LOOP), if you rollback to that savepoint and the remote transaction has only worked with objects local to it, it will work as expected. But if it includes work over that third session (your insert to REMOTE_TABLE), it will throw the same error as the unrestricted rollback will in a 3-session distributed transaction.

Coding like this is not supported. You are supposed to control distributed transactions from the initiating session only. Over the years, transaction control on the remote has been partially permitted (commit works, rollback only partly so, rollback with a third session not at all). Because this isn't supported, Oracle's behavior is bound to change from version to version, and perhaps between RAC and non-RAC.

Moral of the story: don't use a loopback link to initiate a third session in your distributed transaction unless you keep all your transaction control in the initiating session, or you declare pragma distributed_transaction, which starts a brand new transaction local to the remote, which makes the remote session the initiating session and fully able to control the new transaction it initiates.

本文标签：