Database Reference
In-Depth Information
this
UPDATE
statement. Rather, it is the fact that
X
was used to locate the row, and the consistent read value of
X
(1 in
this case) differs from the current mode read of
X
(2). Now, upon restart, the trigger sees the value of
X=2
(following
modification by the other session) as the :
OLD
value and
X=3
as the :
NEW
value.
So, this shows that these restarts happen. It takes a trigger to see them in action; otherwise, they are generally
undetectable
. That does not mean you can't see other symptoms—such as a large
UPDATE
statement rolling back work
after updating many rows and then discovering a row that causes it to restart—just that it is hard to definitively say,
“This symptom is caused by a restart.”
An interesting observation is that triggers themselves may cause restarts to occur even when the statement itself
doesn't warrant them. Normally, the columns referenced in the
WHERE
clause of the
UPDATE
or
DELETE
statement are
used to determine whether or not the modification needs to restart. Oracle will perform a consistent read using these
columns and, upon retrieving the block in current mode, it will restart the statement if it detects that any of them
have changed. Normally, the other columns in the row are not inspected. For example, let's simply rerun the previous
example and use
WHERE Y>0
to find the rows in both sessions, the output we'll see in the first session (the one that gets
blocked) would be:
EODA@ORA12CR1> update t set x = x+1 where y > 0;
old.x = 1, old.y = 1
new.x = 2, new.y = 1
old.x = 2, old.y = 1
new.x = 3, new.y = 1
1 row updated.
So why did Oracle fire the trigger twice when it was looking at the
Y
value? Does it examine the whole row? As
you can see from the output, the update was, in fact, restarted and the trigger again fired twice, even though we were
searching on
Y>0
and did not modify
Y
at all. But, if we re-create the trigger to simply print out the fact that it fired,
rather than reference the
:OLD
and
:NEW
values, as follows, and go into that second session again and run the update,
we observe it gets blocked (of course):
EODA@ORA12CR1> create or replace trigger t_bufer
2 before update on t for each row
3 begin
4 dbms_output.put_line( 'fired' );
5 end;
6 /
Trigger created.
EODA@ORA12CR1> update t set x = x+1;
fired
1 row updated.
After committing the blocking session, we'll see the following:
EODA@ORA12CR1> update t set x = x+1 where y > 0;
fired
1 row updated.
The trigger fired just once this time, not twice. Thus, the
:NEW
and
:OLD
column values, when referenced in the
trigger, are also used by Oracle to do the restart checking. When we referenced
:NEW.X
and
:OLD.X
in the trigger,
X
's consistent read and current read values were compared and found to be different. A restart ensued. When we
removed the reference to that column from the trigger, there was no restart.
