Database Reference
In-Depth Information
To observe the restart, all we need is a trigger to print out some information. We'll use a BEFORE UPDATE FOR
EACH ROW trigger to print out the before and after image of the row as the result of an update:
EODA@ORA12CR1> create or replace trigger t_bufer
2 before update on t for each row
3 begin
4 dbms_output.put_line
5 ( 'old.x = ' || :old.x ||
6 ', old.y = ' || :old.y );
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
Search WWH ::




Custom Search