It is easier to see a restart than you might at first think. We’ll be able to observe one, in fact, using a simple one-row table. This is the table we’ll use to test with:
rajesh@10GR2> create table t (x number,y number);
Table created.
Elapsed: 00:00:00.04
rajesh@10GR2> insert into t values(1,1);
1 row created.
Elapsed: 00:00:00.01
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@10GR2>
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 simply print out the before and after image of the row as the result of an update:
rajesh@10GR2> create or replace trigger t_trig
2 before update on t
3 for each row
4 begin
5 dbms_output.put_line(' old.x = '||:old.x||' old.y = '||:old.y);
6 dbms_output.put_line(' new.x = '||:new.x||' new.y = '||:new.y);
7 end;
8 /
Trigger created.
Now we’ll update that row:
rajesh@10GR2> update t set x = x + 1;
old.x = 1 old.y = 1
new.x = 2 new.y = 1
1 row updated.
Elapsed: 00:00:00.01
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:
rajesh@10GR2> update t set x = x + 1 where x > 0;
That 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:
rajesh@10GR2> 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.
Elapsed: 00:00:07.43
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 cannot 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, that shows that these restarts happen.
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:
rajesh@10GR2> 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.
You might at first wonder, “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
rajesh@10GR2> create or replace trigger t_trig
2 before update on t
3 for each row
4 begin
5 dbms_output.put_line (' Trigger Fired ');
6 end;
7 /
Trigger created.
rajesh@10GR2>
rajesh@10GR2> update t set x = x + 1;
Trigger Fired
1 row updated.
Elapsed: 00:00:00.00
and go into that second session again and run the update, we observe it gets blocked (of course). After committing the blocking session, we’ll see the following:
rajesh@10GR2> update t set x = x + 1 where y > 0;
Trigger Fired
1 row updated.
The trigger fired just once this time, not twice. This shows that 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.
So the rule is that the set of columns used in the WHERE clause to find the rows plus the columns referenced in the row triggers will be compared. The consistent read version of the row will be compared to the current read version of the row, and if any of them are different the modification will restart.
Note You can use this bit of information to further understand why using an AFTER FOR EACH ROW trigger is more efficient than using a BEFORE FOR EACH ROW. The AFTER trigger won’t have the same effect.
No comments:
Post a Comment