Database Reference
In-Depth Information
7 dbms_output.put_line
8 ( 'new.x = ' || :new.x ||
9 ', new.y = ' || :new.y );
10 end;
11 /
Trigger created.
Now we'll update that row:
EODA@ORA12CR1> set serveroutput on
EODA@ORA12CR1> update t set x = x+1;
old.x = 1, old.y = 1
new.x = 2, new.y = 1
1 row updated.
So far, everything is as we expect: the trigger fired once, and we see the old and new values. Note that we have not
yet committed, however—the row is still locked. In another session, we'll execute this update:
EODA@ORA12CR1> set serveroutput on
EODA@ORA12CR1> update t set x = x+1 where x > 0;
This will immediately block, of course, since the first session has that row locked. If we now go back to the first
session and commit, we'll see this output (the update is repeated for clarity) in the second session:
EODA@ORA12CR1> update t set x = x+1 where x > 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.
As you can see, that row trigger saw two versions of that row here. The row trigger was fired two times: once with
the original version of the row and what we tried to modify that original version to, and again with the final row that
was actually updated. Since this was a BEFORE FOR EACH ROW trigger, Oracle saw the read-consistent version of the
record and the modifications we would like to have made to it. However, Oracle retrieved the block in current mode
to actually perform the update after the BEFORE FOR EACH ROW trigger fired. It waits until after this trigger fires to get
the block in current mode, because the trigger can modify the :NEW values. So Oracle can't modify the block until after
this trigger executes, and the trigger could take a very long time to execute. Since only one session at a time can hold a
block in current mode, Oracle needs to limit the time we have it in that mode.
After this trigger fired, Oracle retrieved the block in current mode and noticed that the column used to find this
row, X , had been modified. Since X was used to locate this record and X was modified, the database decided to restart
our query. Notice that the update of X from 1 to 2 did not put this row out of scope; we'll still be updating it with
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.”
 
Search WWH ::




Custom Search