But really we don’t need a trigger in
place for a “Transaction restart” to kick in? Not at all – a simple where
clause could do that.
Here is again a test case to demonstrate
that.
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>
Session#1>
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 session#2 do
this.
Session#2> select * from t;
X Y
---------- ----------
1 0
1 row selected.
Session#2> update t set y = y+1
where x >0 and y is not null;
Session#2 is blocked by Session#1 since
its transaction is not yet committed, so go back and do commit in Session#1.
Session#1> commit;
Commit complete.
Session#1> select * from t;
X Y
---------- ----------
1 1
2 1
2 rows selected.
Session#1>
Since session#1 got committed, session#2
got unblocked and completed its update statement, now session#2 show this.
Session#2>update t set y = y+1 where
x >0 and y is not null;
2 rows updated.
Session#2> select * from t;
X Y
---------- ----------
1
2
2 2
2 rows selected.
Session#2>
So
having the set of columns in where clause “triggers” the transaction restart,
which in-turn advances SCN later than the commit SCN generated by the first
transaction.
That
is the whole story of getting consistent result after the update from
Session#2.
So
why this statement “update T set y = y+1” behaves
different from this statement “update T set y = y+1 where x >0 and y is not null”? (Since both the statement is supposed to
update all the rows from the Table T) – This is identified as an Internal Bug
and not yet fixed still in Oracle 12c (12.1.0.2)
So
please aware of these and have your DML’s to be tested properly in
applications, to avoid these kind of inconsistency, till they get fixed.
No comments:
Post a Comment