Write Inconsistency Part I

Oracle Database enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time.

Here is an example of write inconsistency in Oracle. (Able to reproduce in 10g, 11g and 12c (12.1.0.2))

In session-1 do this.

Session#1> set feedback off

Session#1> drop table t purge;

Session#1> create table t as select 1 x,0 y from dual;

Session#1> insert into t(x,y) values(2,0);

Session#1> set feedback on

Session#1> select * from t;

         X          Y

---------- ----------

         1          0

         2          0

2 rows selected.

Session#1> update t set y = 1;

2 rows updated.

Session#1> select * from t;

         X          Y

---------- ----------

         1          1

         2          1

2 rows selected.

Session#1>

Remember no, Commit yet! From the session-2 do this.

Session#2> select * from t;

         X          Y

---------- ----------

         1          0

1 row selected.

Session#2> update t set y = y+1;

Session#2 wait for Session#1 to complete the transaction, until then session#2 is blocked.

Now go back to session#1 and do commit!

Session#1> commit;

Commit complete.

Session#1> select * from t;

         X          Y

---------- ----------

         1          1

         2          1

2 rows selected.

Since Session#1 got committed, Update statement in Session#2 got unblocked and query from session#2 returned this.

Session#2> update t set y = y+1;

1 row updated.

Session#2> select * from t;

         X          Y

---------- ----------

         1          2

         2          1

2 rows selected.

Session#2>

This is an interesting example of something that arguably doing the wrong thing.

According to test result,

• The second session has update (1, 1) to (1, 2) – which means it has seen some of the effects of the transaction executed by the first session.
•  But it has not update (2, 1) to (2, 2) – which means it hasn’t seen all of the effects of the transaction executed by the first session.

In principle, the second transaction should have done one of these things.

•  Rolled back and restarted at an SCN later than commit SCN generated by the first transaction.
•  Failed and rolled back (something similar to ORA-01877 errors)

We will see how a Transaction restart, could fix this issue. Next installment coming soon…J