Flashback queries and read consistency mechanism

In one of Oracle professional forums the question has been asked: how can I see the history of changes made by my transaction ? One of the answers was: use a flashback query. This is a wrong answer unfortunately. When we query data Oracle allowes us to view only changes committed before SCN of query start or changes made by our transaction before the beginning of query. Oracle uses the undo information to reconstruct the required versionsof blocks. they are known as CR (consistent read) versions of blocks. In fact it has to rollback all transactions in blocks what have not been committed before the query beginning. At the same time Oracle can have to perform “partial rollback” for our own transaction.The example below illustrates it:

SQL> var rc refcursor

SQL> select * from t;

no rows selected

SQL> insert into t values(1); — Changes before cursor open 1 row created.

SQL> exec open :rc for select * from t; — Cursor open, SCN assigned PL/SQL procedure successfully completed.

SQL> — Change data after the query beginning.

SQL> — Our transaction changes the same block using the same ITL slot

SQL> insert into t values(2);

1 row created.

SQL>– Oracle has to perform partial rollback

SQL>– in our transaction to recover consistent version of block.

SQL>– It has to rollback “insert into t values(2)”

SQL> print rc

X

————————-

1

SQL> exec open :rc for select * from t; — Now we have new query SCN > SCN of changes PL/SQL procedure successfully completed.

SQL> print rc

X

————————-

1

2

Everything works as it stated in concepts. But it’s easy to rollback the transaction in the block entirely – owing to RBA information in the block ITL slot and ability to definethe transaction status using the transaction table in undo header. But what to do if we need to rollback our own transaction partially – up to the last change before the query beginning ? It’s not a trivial question.The undo records don’t contain the information about SCN, andITL slot contains only Commit SCN, and the block header contains the information about SCN of last changes. Probably Oracle uses the same mechanism to provide the consistent read and statement-level rollback – statement-level system savepoints. In this case Oracle knows to what point it should rollback the data in the block. In that sense flashback query works differ than a usual select query when we talk about seeing the changes made by our transaction. Because there is not any relation between SCN in “AS OF SCN ()” clause and the system-level savepoint of the query, Oracle is not able to define the boundary for the partial rollback. Moreover, even if we had the relation between SCN and undo entry, we still would not able to provide the correct partial rollback. Savepoints separate one SQL operator from another (because the transaction performs operators in series). But not SCNs do. So if we rollbacked our transaction to the certain SCN we would have a risk to get to the “middle” of DML operator – and get to the inconsistent data snapshot. In flashback queries Oracle doesn’t have any alternative as to rollback ALL changes what have not been committed before specified SCN, including the changes of our transaction. So it should be taken into account when we try to use flashback queries.

SQL> insert into t values(1);

1 row created.

SQL> select * from t;

X

————————-

1

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

——————-

9300655466989

SQL> select * from t as of scn (9300655466989);

no rows selected

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s