Was
wondering, why don’t transaction (from second session) restart itself
at an SCN later than commit SCN generated by the first transaction?
Just
to check if restart happens, with trigger in place after create table, you’d
actually see different results….
Repeating
the Test case with Trigger in place now.
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> create or replace trigger
t_trig
2 before update on t
3 for each row
4 begin
5 dbms_output.put_line('updating '|| :old.x
||','|| :old.y
6 ||' to '|| :new.x || ',' ||
:new.y );
7 end;
8 /
Session#1> insert into t(x,y) values(2,0);
Session#1> set feedback on
Session#1>
Session#1> select * from t;
X Y
---------- ----------
1 0
2 0
2 rows selected.
Session#1>update t set y = 1;
updating 1,0 to 1,1
updating 2,0 to 2,1
2 rows updated.
Session#1> select * from t;
X Y
---------- ----------
1 1
2 1
2 rows selected.
Session#1>
So
Trigger reported that Session#1 has update two records - Remember no, Commit
yet!
Now
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;
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.
Session#1>
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;
updating 1,0 to 1,1
updating 1,1 to 1,2
updating 2,1 to 2,2
2 rows updated.
Session#2> select * from t;
X Y
---------- ----------
1 2
2 2
2 rows selected.
Session#2>
The
output from the trigger confirms, the update statement from session#2
a) Did a
consistent read on Table ‘T’
b) Got the
first row for update with x=0 and y=1 and changed y=2 and invoked the trigger
before executing the update statement (since it is a before update trigger).
c) Trigger
put this message "updating 1,0 to 1,1" in buffer
d) Upon trying
to make changes to that block, it realizes that row is locked by session#1 and
hence this update got blocked.
e) When
session#1 got committed – this update from session#2 resumes – in-turn got
restarted with SCN later than commit SCN generated by the first transaction.
·
So do again a consistent read, go two
rows, modify them, have those before and after values recorded by trigger in
buffer.
f) Session#2
get current read on block to make changes for new values of Y. (since session#1
got committed, block is available for current read to Session#2)
g) For
each row updated trigger tries to put message “updating x1, y1 to x2, y2” in
buffer
h) Once
the update statement completes, message from buffer got printed on screen.
The
presence of three message confirms that update got restarted and produced
correct results at the end of this transaction.
The
set of columns that "trigger" the restart are the ones used to locate
rows (of which there are none, the where clause doesn't exist) plus any columns
referenced in a trigger. Since the trigger refers to X and Y - they become part
of the set of columns responsible for triggering the restart of this update.
No comments:
Post a Comment