11g FLASHBACK Compensating Transaction

When the procedure completes successfully, it has created a compensating transaction, having backed out the original transaction(s). This compensating transaction does not include a commit.

Here is an example in which two tables, t and t_d, have been used. Table t has a primary key on col1 and table t_d has a foreign key on col1 which references table t (col1):

LUTZ  @ orcl SQL> SELECT versions_xid , versions_operation , col1
                FROM t
                     VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

VERSIONS_XID     V       COL1
---------------- - ----------
020017004B020000 I          4
020017004B020000 I          5
020017004B020000 I          6
020017004B020000 I          7
020017004B020000 I          8
020017004B020000 I          9
020017004B020000 I         10
03001300F8010000 D         10
03001300F8010000 D          9
03001300F8010000 D          8
03001300F8010000 D          7
03001300F8010000 D          6
03001300F8010000 D          5
03001300F8010000 D          4
                            1
                            2
                            3
                            4
                            5
                            6
                            7
                            8
                            9
                           10

24 rows selected.

LUTZ  @ orcl SQL> SELECT versions_xid , versions_operation , col2
                FROM t_d
                     VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

VERSIONS_XID     V       COL2
---------------- - ----------
020017004B020000 U         11
03001300F8010000 U         14
090007005B020000 U         11
                           10

With id 03001300F8010000, one row has updated (the only row in the table) in table t_d, and deleted 7 rows in table t.

One could even have a look at the sql statements which would undo the transaction:

LUTZ  @ orcl SQL> SELECT undo_sql
                    FROM flashback_transaction_query
                      WHERE xid=HEXTORAW('03001300F8010000');

UNDO_SQL
-------------------------------------------------------------------------------
insert into "LUTZ"."T"("COL1") values ('10');
insert into "LUTZ"."T"("COL1") values ('9');
insert into "LUTZ"."T"("COL1") values ('8');
insert into "LUTZ"."T"("COL1") values ('7');
insert into "LUTZ"."T"("COL1") values ('6');
insert into "LUTZ"."T"("COL1") values ('5');
insert into "LUTZ"."T"("COL1") values ('4');
update "LUTZ"."T_D" set "COL2" = '11' where ROWID = 'AAAO2xAAEAAAAAXAAA';

Next, the PL/SQL block is called with the value 03001300F8010000 for the exchange variable at the prompt.

After a while, a message is received with the information that the procedure has successfully completed.

LUTZ  @ orcl SQL> SELECT * FROM user_flashback_txn_state;

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE
---------------- ---------------- ---------------- ----------------
03001300F7010000 09000C0058020000                  CASCADE
03001300F7010000 0100180013020000 09000C0058020000 CASCADE
020017004B020000 03001300F8010000                  CASCADE
0400140049020000 05001E001D020000                  CASCADE

Here it is possible to see multiple rows for the same transaction; one for each dependent transaction.

At this point,  it is possible to check the results of the compensating transaction to see if they are satisfactory. The DBA can also choose to commit, or respectively rollback, the compensating transaction if the results are unsatisfactory.

The results of executed, but not yet committed or rolled back, compensating transactions can be viewed in two dynamic performance views:

1.      v$flashback_txn_mods shows all modifications of all compensating transactions in memory:

SYS AS SYSDBA @ orcl SQL> desc V$FLASHBACK_TXN_MODS

Name                          Null?    Type
---------------------------   -----    ------
COMPENSATING_XID                       RAW(8)
COMPENSATING_TXN_NAME                  VARCHAR2(255)
XID                                    RAW(8)
TXN_NAME                               VARCHAR2(255)
PARENT_XID                             RAW(8)
INTERESTING                            NUMBER
ORIGINAL                               NUMBER
BACKOUT_SEQ                            NUMBER
UNDO_SQL                               VARCHAR2(4000)
UNDO_SQL_SQN                           NUMBER
UNDO_SQL_SUB_SQN                       NUMBER
BACKOUT_SQL_ID                         NUMBER
OPERATION                              VARCHAR2(30)
BACKEDOUT                              NUMBER
CONFLICT_MOD                           NUMBER
MODS_PER_LCR                           NUMBER
 

2.      v$flashback_txn_graph shows the dependencies and conflicting operations after a compensating transaction has been started but are neither committed or rolled back yet:

SYS AS SYSDBA @ orcl SQL> desc V$FLASHBACK_TXN_GRAPH

 Name               Null?    Type
 ------------------ -------- -------------
COMPENSATING_XID             RAW(8)
COMPENSATING_TXN_NAME        VARCHAR2(255)
XID                          RAW(8)
TXN_NAME                     VARCHAR2(255)
PARENT_XID                   RAW(8)
INTERESTING                  NUMBER
ORIGINAL                     NUMBER
BACKOUT_SEQ                  NUMBER
NUM_PREDS                    NUMBER
NUM_SUCCS                    NUMBER
DEP_XID                      RAW(8)
DEP_TXN_NAME                 VARCHAR2(255)
TXN_CONF_SQL_ID              NUMBER
DEP_TXN_CONF_SQL_ID          NUMBER

% Detailed information about all compensating transactions in the database,             including all dependencies, can be read from the data dictionary with the data            dictionary views

• flashback_txn_report (committed transactions!)

• flashback_txn_state (current state with dependency report)

LUTZ  @ orcl SQL> desc  user_flashback_txn_report;

 Name                       Null?   Type
 -------------------------- ------  ----
 COMPENSATING_XID           NOT NULL RAW(8)
 COMPENSATING_TXN_NAME               VARCHAR2(256)
 COMMIT_TIME                         DATE
 XID_REPORT                          CLOB

LUTZ  @ orcl SQL>  SELECT xid_report
                    FROM user_flashback_txn_report
                     WHERE COMPENSATING_XID=HEXTORAW('020017004B020000'); 

XID_REPORT
----------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="020017004B0200

Internally, the server has used logminer functionality and retrieved all information needed for the backout operation from the redo log files. The server has also found all dependencies and created a list of statements. After this, it has applied the compensating statements ordered in the correct sequence.

The dependency handling can be managed with the options parameter of the transcation_backout procedure. There are several possibilities:

The privileges needed for a transaction backout are:

• EXECUTE on dbms_flashback

• SELECT ANY TRANSACTION (in OEM and for flashback_transcation_query view)

• SELECT, FLASHBACK and DML privileges on all tables involved in the transactions need to be backed out.

The limitations for transaction backout are:

• Only committed transactions can be flashed back.

• Transactions which have dependent transactions can only be flashed back with either the cascade, nonconflict_only, or nocascade_force options.

• DDL statements are not allowed to be flashed back in transactions.

• Only transactions which do not make any changes which are not supported by the logminer can be flashed back.

• All dependent transactions must be committed before the backout operation.

o        There can not be any dependent transactions which have been aborted more than one minute after the beginning of the backout operation.

o        A backout operation can only be started if there is no active ongoing logminer session.