At times a large transaction may be broken down into smaller logical units for the purpose of rolling back a part of the transaction if needed. Such units are marked by the keyword “SAVEPOINT”. This results in partial rollback of the transaction. This solution delves on how Shareplex handles the transaction when such transaction is meant to be ignored by Capture with the use of SHAREPLEX_IGNORE_TRANS feature and when that transaction results in a partial rollback due to the use of “SAVEPOINT” option. The question assumes significance when large transactions meant to be ignored by Capture have been rolled back only partially due to the presence of SAVEPOINTs.
General information.
When a transaction is executed after executing SHAREPLEX_IGNORE_TRANS and if the transaction has any “ROLLBACK to <savepoint>” whereby the source table gets a part of the transaction (due to partial rollback), the target table is still deprived of that transaction. So the net result is that the source table will get the partial transaction and the target table will not get any part of that transaction. This is the way the feature SHAREPLEX_IGNORE_TRANS is designed to work, namely it will not allow any part of the transaction to be sent to target, regardless of whether the source table gets full or part of that transaction. This happens because the ignoring of the transaction only ends once the COMMIT or ROLLBACK is issued but not when “ROLLBACK to <savepoint>” is issued.
The following test on a simple table in replication illustrates this. The table is named TABLE1 and consists of only one column named num_col. On source the following is executed:
Source:
SQL> execute sha75a.shareplex_ignore_trans;
PL/SQL procedure successfully completed.
SQL> insert into table2 values (1);
1 row created.
SQL> savepoint A;
Savepoint created.
SQL> insert into table2 values (2);
1 row created.
SQL> rollback to A;
Rollback complete.
SQL> commit;
Commit complete.
SQL> select * from table2;
NUM_COL
----------
1
When the target is queried, it does not get any part of that transaction:
Target:
SQL> select * from table2;
no rows selected
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center