Part II Seeing a Restart
Part II, Seeing a Restart
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:
ops$tkyte@ORA10G> create table t ( x int, y int );
Table created.
ops$tkyte@ORA10G> insert into t values ( 1, 1 );
1 row created.
ops$tkyte@ORA10G> commit;
Commit complete.
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:
ops$tkyte@ORA10G> 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:
ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> 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:
ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> 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:
ops$tkyte@ORA10G> 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 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. It takes a trigger to see them in action; otherwise, they are generally “undetectable.” That does not mean you cannot 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:
ops$tkyte@ORA10G> 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
ops$tkyte@ORA10G> 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.
ops$tkyte@ORA10G> update t set x = x+1;
fired
1 row updated.
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:
ops$tkyte@ORA10G> update t set x = x+1 where y > 0;
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.
Which leads us to the “Why do we care?” question.
To be continued….
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:
ops$tkyte@ORA10G> create table t ( x int, y int );
Table created.
ops$tkyte@ORA10G> insert into t values ( 1, 1 );
1 row created.
ops$tkyte@ORA10G> commit;
Commit complete.
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:
ops$tkyte@ORA10G> 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:
ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> 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:
ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> 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:
ops$tkyte@ORA10G> 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 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. It takes a trigger to see them in action; otherwise, they are generally “undetectable.” That does not mean you cannot 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:
ops$tkyte@ORA10G> 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
ops$tkyte@ORA10G> 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.
ops$tkyte@ORA10G> update t set x = x+1;
fired
1 row updated.
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:
ops$tkyte@ORA10G> update t set x = x+1 where y > 0;
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.
Which leads us to the “Why do we care?” question.
To be continued….
posted by Thomas Kyte at 11:25 AM
20 Comments:
Well, the question "why do we care" is an important one. In fact, as you can see from the response on this topic, we do care (the we being the royal we, individually and globally encompassing anyone who actually cares)!
We care because we do want to know exactly how Oracle behaves in these situations, and not all of us can come up with the test cases to show the behaviour in multiple situations. Having these different test cases presented to us is a great form of documentation that we can go away and test in our own systems to validate these behaviours.
But, the main reason that "we care" is because we need to know how our applications are really going to behave. How do "we" exploit, or avoid (depending on the outcome of our tests) the behaviours identified here.
Showing comparisons to the behaviour of other DBMS's, while interesting, is not the most useful information since the majority of "us" are committed to Oracle. The comparison information can help "us" convince managers who read IT periodicals to stick with Oracle, but that is probably about it.
Thanks Tom.
Warren
Well, the question "why do we care" is an important one.
that's why I left the "tease", I have the why do you care (wouldn't have asked otherwise...)
it is just "coming tommorrow"
Same Bat time
Same Bat channel
:)
"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. "
Perhaps the rule should be modified to:
So the rule is that we consider the following :
1. the set of columns used in the WHERE clause to find the rows - if
these are modifed then we restart
2. if ANY column's new and/or old values is referenced in a row trigger affected by the update, we restart."
scott@myora> create table t( x number, y number, z number );
Table created.
scott@myora> insert into t values ( 1, 1, 1 );
1 row created.
scott@myora> commit;
Commit complete.
scott@myora> 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 end;
7 /
Trigger created.
In the trigger, only old value of x is referenced - neither y nor z 's old or new values are referenced.
session1:
scott@myora> update t set z = z+1;
old.x = 1
1 row updated.
session 2 (blocks):
scott@myora> update t set z = z+1 where z > 0;
session 1 (commit)
scott@myora> commit;
Commit complete.
session 2:
scott@myora> update t set z = z+1 where z > 0;
old.x = 1
old.x = 1
1 row updated.
The session 2 restarted even though in the trigger we only referenced old value of x (which is not referenced in the where clause - in fact was not part of updates at all)
Hi Tom,
When I'm done talking for an entire day - you can just "stick a fork in me, I am done".
Tom, I think we would need more than a fork for you :-)... based on the last two blog entries I can only say -- hat off man.
After reading Part I, it was mind bogging for me on how will you show us restart read behavior (I was so teased today that I periodically checked for an update hoping you'll post part II before returning home)-- I can hardly imagine how Part II could be written more clearly. Now, I think my understanding of the Oracle 'current' blocks is much better. Thank you!
warren posted:
Showing comparisons to the behaviour of other DBMS's, while interesting, is not the most useful information since the majority of "us" are committed to Oracle.
On contrary, I found it extremly interesting (I'm sure most readers do), not many authors do that kind of comparison.
regards,
ales
"why using an AFTER FOR EACH ROW trigger is more efficient than using a BEFORE FOR EACH ROW"
I would use a before-row trigger when I want to modify/write the :new values for the row. I cant do this in a after-row trigger.
So regardless of all these write consistency issues, if I need to use a before-row trigger, I would use it, there is really no other option for it.
Or are you saying that if both a before or after row trigger serves your need, you should choose after-row trigger?
Thanks
Or are you saying that if both a before or after row trigger serves your need, you should choose after-row trigger?
exactly!
Unless you need to write to the :new records, in real life you should use an AFTER trigger.
nice blog, tom.
but i'm confused. could this be shown the way they render digital timing diagrams?
sorry, not asking for extra efforts off you.
Is it correct to infer from this that a before-update auditing trigger that used an autonomous transaction to log an update attempt would log this apparantly-single action twice then?
If I weren't so busy, I'd test this myself of course ... :D
they render digital timing diagrams?
what is that? (asks an analog guy)...
an autonomous transaction to log an update attempt would log this apparantly-single action twice then?
100% correct, that is one of my demos :)
an autonomous transaction to log an update attempt would log this apparantly-single action twice then?
Oh, I was slower than Tom.
Here's a proof anyway:
S1>create table t (x)
2 as
3 select 1 from dual;
Table created.
S1>create table logt(s int, o int, n int);
Table created.
S1>create sequence s;
Sequence created.
S1>create or replace trigger x_bef_upd_trig
2 before update on t
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 insert into logt (s,o,n) values (s.nextval,:old.x,:new.x);
8 commit;
9 end;
10 /
Trigger created.
S1>update t set x=2;
1 row updated.
another_tx>update t set x=3;
(blocks)
S1>commit;
Commit complete.
S1>select * from logt order by s;
S O N
---------- ---------- ----------
1 1 2
2 1 3 <-restarted
3 2 3
100% correct, that is one of my demos :)
I can't believe that I'm not beng paid for these shrewd questions.
My previous post was incorrect (as kindly pointed out by Alberto in a private email). The restart was a "basic restart" and had nothing to do with the trigger. However, I did find one curious thing (nit picking).
"This shows that the :NEW and :OLD column values, when referenced in the trigger, are also used by Oracle to do the restart checking. "
It does not have to be both :old and :new values. It can be either of them. So, even if we refer to only the :old value of a column that was modified in the update, Oracle checks if the value has changed and ensues a restart.
This is even though, we could not possibly have modified the :old value in the trigger.
Consider:
schema and trigger:
----
scott@MYORA92> create table t( x number, y number );
Table created.
scott@MYORA92> insert into t values ( 1, 1 );
1 row created.
scott@MYORA92> commit;
Commit complete.
scott@MYORA92> create or replace trigger t_bufer
2 before update on t for each row
3 begin
4 dbms_output.put_line( 'old x ' || :old.x );
5 end;
6 /
Trigger created.
------
session 1:
------
scott@MYORA92> update t set x = x+1;
old x 1
1 row updated.
------
session 2 (blocks)
------
scott@MYORA92> update t set x = x+1 where y > 0;
-----
session 1 (commit)
scott@MYORA92> commit;
Commit complete.
------
session 2 (unblocks with a restart)
------
scott@MYORA92> update t set x = x+1 where y > 0;
old x 1
old x 2
1 row updated.
------
The restart happenned even though the trigger referenced the old value of x only. I guess this is because Oracle thinks that you referenced :old value, you may have made decisions based on the :old value which is no longer correct so it restarts the statement.
Hi,
On one of the responses in Oracle-L,
Steve adadms posted the following
"Hi David,
The difference can be demonstrated with a small test.
This is 10.2.0 with _in_memory_undo = false to show the redo.
SQL> connect test/test
Connected.
SQL> create table test (key number);
SQL> insert into test (key) values (1);
SQL> create trigger test before update on test for each row begin null; end;
2 /
SQL> connect test/test
Connected.
SQL> update test set key = key + 1;
SQL> select n.name, m.value from sys.v_$mystat m, sys.v_$statname n
2 where m.value > 0 and n.statistic# = m.statistic# and bitand(n.class, 2) > 0;
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 2
redo size 632
SQL> drop table test;
SQL> create table test (key number);
SQL> insert into test (key) values (1);
SQL> create trigger test after update on test for each row begin null; end;
2 /
SQL> connect test/test
Connected.
SQL> update test set key = key + 1;
SQL> select n.name, m.value from sys.v_$mystat m, sys.v_$statname n
2 where m.value > 0 and n.statistic# = m.statistic# and bitand(n.class, 2) > 0;
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 1
redo size 432
SQL>
We got 2 redo records for the BEFORE trigger (but only 1 for the AFTER trigger).
An extra redo record was needed because the row was locked separately before the
application of the change vectors for the update.
This may look trivial, but it is a potential scalability issue in a busy OLTP
environment. The extra current mode buffer also has a non-trivial impact on
scalability and CPU usage."
From this it seems that Oracle does get the block in current mode during the before update trigger, which seems to be the opposite of what you say in your blog. Could you please clarify
On one of the responses in Oracle-L,
I'm not sure it shows it conflicts, but rather that right after the trigger code goes - it does the 'write consistency check' for all of the referenced attributes in the trigger and likely to apply any possible changes to the :new record (and that is where it blocks, and then rolls back)
My oracle-l post was showing another reason why BEFORE ROW triggers should be avoided, namely that the trigger locks the row even if it makes no other changes to the row. This generates redo and of course the block is got in current mode to apply it.
When Tom said that Oracle waits until after this trigger fires to get the block in current mode, he was talking of the current get to apply the redo for the update itself, as is made clear in the context. He was not saying that this was the only current get, merely that it was not feasible to continue to hold the block in current mode between the two sets of changes.
If the statement restart is a kind of rollback to savepoint, then it should behave similarly regarding locks. A rollback to savepoint releases all table and row locks obtained since the savepoint, but not transaction locks. My tests show that this is indeed the case for statement restart, and that PL/SQL user locks acquired using dbms_lock (even with release_on_commit=true) behave like transaction locks in this respect.
i wonder why my after update trigger still firing twice ??
here's my scenario..
1. i have trigger to insert a value into a table x after update on table y.
2. i executed a java program to update a value on table y, which will raise the trigger it self.
3. i queried table x and found two value being inserted from my trigger.
is there any correlation with this session thing ?
@anonymous
In your case, it will be a bug in your code/logic.
The restart would ROLL BACK. The first firing of the trigger would have ROLLED BACK. Only if you did something non-transactional would this restart affect you (like sending an email in a trigger, like committing in in a trigger, etc).
So, no, this is not the cause of your issue, your code is the cause in this case.
Turn on sql_trace and see what your application is doing exactly.
"something non-transactional"
Can we take bets on the insert in the trigger being done as autonomous transaction ?
@gary,
if it is an autonomous transaction, then they are committing in a trigger and that would be a huge mistake in their logic - committing in a trigger should *never* be done except to log and error (and then the trigger itself doesn't commit, the error logging procedure does)
POST A COMMENT
<< Home