SCN of update/delete
Alberto Dell'Era, May 13, 2003 - 4:22 pm UTC
Hi Tom,
so, to check my understanding and complete what the original poster said, is it correct to say that, in the context of a serializable transaction, when an update/delete is issued against a row, Oracle will raise the "cannot serialize transaction" exception if and only if the SCN of the row is higher than the SCN of the transaction ?
I.e., a serializable transaction is nothing more than this, plus the read-consistency "as of" the SCN of the transaction ?
May 13, 2003 - 5:12 pm UTC
if and only if is too strong. it depends on whether we can tell -- if the ITL entries are still there (why you might want to set initrans higher then the default if you use this heavily)
a serializable transactions means
o you will see the database as it existed when your transaction began (sort of like "flashing back"
o you will recieve an error if you try to modify a row that was modified since your transaction began or we cannot make a read consistent view of that row you are trying to read (like when you get an ora1555)
A reader, May 13, 2003 - 7:24 pm UTC
read committed
A reader, May 13, 2003 - 7:44 pm UTC
Is SET TRANSACTION ISOLATION LEVEL READ COMMITTED the default behavior of Oracle?
Thanks.
May 13, 2003 - 9:15 pm UTC
yes
SCN increment
Gaurav, May 29, 2003 - 12:08 pm UTC
Dear Tom,
As addition to this discussion, I tried to perform the same exercise which you did... get the scn number and commit.... here is the output of that---
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3229757
SQL> commit;
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3229761
SQL> commit;
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3229765
SQL> commit;
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3229842
you can notice that the increment is erratic. First it was incremented by 4 then in the last it was incremented by some other value. May I ask you what is the algorithim/mechanism/reason behind this?
One more doubt - please tell me whether SCN is unique to whole database or not (I think it is unique) if it is unique than where this informations will stored -in the control file or in all detafile header?
Regards,
Gaurav
May 29, 2003 - 1:46 pm UTC
there are things in the background going on -- AQ, SMON, Job queues -- maybe even other users. The "idle" database is actually very active.
These background things did it.
there is an SCN for a database -- it is like a clock, a ticker, always incrementing as time marches on. its a controlfile thing.
SCN Use
gaurav, May 30, 2003 - 5:51 am UTC
Thanks Tom,
For clearing my doubts. However, In addition to this I would like to ask something more, The use of SCN.
I have read the Oracle documentation and articles/discussion on sites but I haven't got the clear answer. Please, clarify this to me with some precise explanation. The scenario is-
X user has logged in to the system. Will oracle allocate some/current SCN to him?
Y user has logged into the system and updated one table but haven't commited. Suppose earlier table1 has 10 records now it has 11 records.
Now when X user execute select on table1 he will get only records 10. How oracle will come to know that it has to ignore the 11th record is there any thing other than SCN ?
I think there should be one more variable on the block level which should tell that this block has been updated.
I am totally confused. It will be very helpfull for me as well as my other collegues if you explain this whole procedure with proper example. I know you are very busy, if it is not possible for you then please atleast give me some reference (link) where I can get the required info.
Thanking you in advance.
Regards,
Gaurav
May 30, 2003 - 8:17 am UTC
SCNs are allocated to transactions, not users and they are assigned upon COMMIT.
When Oracle starts user X's query -- it says "what is the current system SCN - oh, it is 55, great". Now every block it reads will be inspected. If the block is newer then scn 55, has any modifications made AFTER scn 55 (scn 55 is like a time, like "12 oclock noon"), Oracle will roll back the block to scn 55 using the RBS.
All blocks for user X's query will therefore be as of scn 55 -- either because they were not modified after scn 55 or because we rolled them back to scn 55.
There is a transaction array (ITL) on the block header that tells us this.
"Transactions containing DML statements with subqueries should use serializable ..."
Jim Dickson, May 30, 2003 - 10:05 am UTC
I am not sure how single statements that contain sub-queries work w.r.t scn and serialization.
The Concept guides state
"Transactions containing DML statements with subqueries should use serializable isolation to guarantee consistent read."
Does DML in this usage includes SELECTs ?
I don't believe the Concept guide explains the advice - to me, it implies each query (parent/sub) within the statement compares blocks to different SCNs.
Can you enlighten?
Gratefully
May 30, 2003 - 10:20 am UTC
the query is the query -- there is just a single query.
I've no idea what they might have meant by that sentence taken out of context. How about a direct link into otn.oracle.com that gives it to us in context.
Thanks
Gaurav, June 02, 2003 - 6:31 am UTC
Thanks a lot tom for clearing my doubt. That is the informations for which I am looking for.
Regards,
Gaurav
Transactions containing DML statements with subqueries should use serializable
Jim Dickson, June 02, 2003 - 10:21 am UTC
June 02, 2003 - 10:44 am UTC
That looks like a documentation bug if you ask me.
I'll file one on it. A statement is processed read consistently -- with or without subqueries -- in read committed or serializable.
serializable only affects the behavior of multiple statements run in a transaction.
SCN change when COMMIT?
steven, June 02, 2003 - 11:36 pm UTC
hello tom,
you have said that "SCNs are allocated to transactions, not users and they are assigned upon COMMIT."
but then i try many times ,sqlplus reply me such:
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
4185290
SQL> commit;
commit success.
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
4185290
SQL> commit;
commit success.
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
4185290
SQL> commit;
commit success.
SQL> select dbms_flashback.get_system_change_number
2 from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
4185292
it seems that scn generated doesn't have to increase during user commit? can you explain me more?
thank you very much.
"Transactions containing DML statements with subqueries should use serializable ..."
Jim Dickson, June 04, 2003 - 7:17 am UTC
I'd love for you to get a full explanation for this.
I have already raised issue on Metalink (DocID 285912.995)
Oracle Support did provide code examples, and results/errors with/without serializable isolation level set.
But there was no real explanation of underlying architecture/mechanics and not an SCN in sight.
Thanks for following this up.
Jim
Read Only Transactions
Sagar, June 10, 2003 - 8:49 am UTC
Hi Tom,
I tried to test out Read Only transactions in a pl/sql block....but I get the following error
**************************************************
SQL> declare
2 begin
3 set transaction isolation level read only;
4 end;
5 /
set transaction isolation level read only;
*
ERROR at line 3:
ORA-06550: line 3, column 38:
PLS-00103: Encountered the symbol "ONLY" when expecting one of the following:
committed
**************************************************
Can you help me out here?
Thnx
Sagar
June 10, 2003 - 9:23 am UTC
execute immediate 'set ....';
or
begin
dbms_transaction.read_only;
end;
/
Transactions containing DML statements with subqueries should use serializable
Jim Dickson, September 29, 2003 - 8:46 am UTC
How can i follow up the documentation bug you filed on this (June 2003)?
thanks
What could the concept guide mean?
Lucy Lynch, April 19, 2004 - 6:26 pm UTC
This is in response to the question about this statement: "Transactions containing DML statements with subqueries should use serializable
isolation to guarantee consistent read." Tom and others thought this was wrong (and I agree) but there is a related problem. It seems to me that this must be referring not to subqueries, per se, but to user-defined functions or object methods. These, indeed, do not give read-consistency with the query that uses them.
So an innocuous-looking query like
select acct_id, acct_bal(acct_id)
from acct_table
could return a set of results that were never correct at any point in time, depending on what tables acct_bal is reading and whether those tables are changing while this query is executing.
Types of SCN ?
Pravesh Karthik from India, June 05, 2005 - 6:26 am UTC
Tom,
I was asked in an interview, whether i know the types of SCN. Is this question valid?. if so, can you please tell me the types of SCN.
1. Which are the tables that has SCN info stored.
v$log_history low_change# and high_change#
v$datafile as CHECKPOINT_CHANGE#, LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#
any other table, please let me know.
Thanks for your consideration.
Pravesh Karthik
June 05, 2005 - 9:28 am UTC
there is only one "type" of SCN, and it is a number. That is like asking "what type of minutes in the day are there", just one -- a minute. the SCN 54153423 is just "time", just like 12:01 is just a "time"
There are many dozens of "times of interest" -- just like I'm interested in what time I need to be at work, what time is lunch (very very important) and what time I get to leave, what time do I get home and what time do I go to bed.
But they are all just times.
I'm really not sure what the relevance of their question was. Those are just "points in time". Various points of time of interest are stored all over the place. I don't have (nor am i really interested in -- not very meaningful) a list of all views where an SCN might be stuffed.
SCN & Incarnation
Alay, September 16, 2005 - 10:12 am UTC
Hi tom,
I have two questions.
(1) What is SCN? When it changes? Where it is recorded? For what purpose it uses?
(2) What is incarnation number? Where it is recorded? When it changes? For what purpose it uses?
get_system_change_number on 8i?
Louis Goldstein, September 27, 2005 - 2:01 pm UTC
As you stated, get_system_change_number was added in 9i. Is there a way to inspect the SCN on 8i?
Thanks,
Louis
September 27, 2005 - 2:12 pm UTC
sort of:
ops$tkyte@ORA9IR2> create table scn_tab ( x number primary key ) ;
Table created.
ops$tkyte@ORA9IR2> insert into scn_tab values (0);
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function get_scn return number
2 as
3 pragma autonomous_transaction;
4 l_scn number;
5 begin
6 update scn_tab set x = userenv('COMMITSCN') returning x into l_scn;
7 commit;
8 return l_scn;
9 end;
10 /
Function created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select get_scn from dual;
GET_SCN
----------
2360725
don't call it lots. And remember the instant you get an SCN, it is out of date already. the database is always committing (it is never idle)
search for commitscn to read a bit about it, not documented, good for testing/playing with only, it is magic.
Follow up on SCN workings
Robert, September 28, 2005 - 10:09 am UTC
Tom,
This is what you said to Guarav back on May 30, 2003.
<QUOTE>
When Oracle starts user X's query -- it says "what is the current system SCN - oh, it is 55, great". Now every block it reads will be inspected. If the block is newer then scn 55, has any modifications made AFTER scn 55 (scn 55 is like a time, like "12 oclock noon"), Oracle will roll back the block to scn 55 using the RBS.
All blocks for user X's query will therefore be as of scn 55 -- either because they were not modified after scn 55 or because we rolled them back to scn 55.
<QUOTE>
Tom, My question is: Let's say there are several version of the block that Oracle is looking for (e.g.: SCN: 54, 53, 20, 11). Will Oracle always get the max(SCN) that is <= the current one (e.g.: <= 55 in this case)?
Thank you,
Robert.
September 28, 2005 - 10:40 am UTC
yes, oracle always gets the max(scn) that is less than or equal to 55 in this case.
How does it know?
Robert, September 28, 2005 - 11:13 am UTC
Tom,
Could you explain the mechanics of how Oracle knows what is the correct block with an SCN that is <= the current transaction's SCN (or point to some documentation, etc. that explains the mechanics of this).
Thanks,
Robert.
September 28, 2005 - 12:34 pm UTC
this is not documented stuff, conceptualize it like this:
we go for the block, it is too new. We look for the oldest block that is >= our scn in the cache (if not there, read it in from disk and put it there).
We then start walking undo chain for that block until it is "<=" and stop.
Walking the chain?
Robert, September 28, 2005 - 1:24 pm UTC
Tom,
I think I have seen you explain the concept of Oracle 'walking the [undo] chain' before (to find the block <= our SCN).
Could you please briefly explain the concept of how each block (in the undo space?) connects to the other versions of that block (or point me to where you've explained it before :) ?
Thank you,
Robert.
September 28, 2005 - 5:23 pm UTC
loosely speaking:
the block has transaction information on it.
look at block, block points to undo for itself. goto undo, undo changes to block (including changes to block transaction information!). We now have block before changes.
If block is "young enough - use it", else "look at block, block points to undo for itself........"
everytime we undo the changes, we have a "clean block" as of that prior point in time, if block is "ok to use, use it", else keep going back in time.
IF we find the undo we need no longer exists, that is the 1555.
I think I got it!
Robert, September 28, 2005 - 6:10 pm UTC
Thanks Tom!
SCN advance without commit
A reader, February 09, 2006 - 3:07 pm UTC
You mentioned SCN changes upon commit. However, it seems that SCN advances based on time (on both 10g and 9i). I am the only user on the database and I am not runninging any transaction. Could you please explain?
SQL> conn /as sysdba
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
819806
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
819806
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
819807
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
819807
February 10, 2006 - 11:33 am UTC
your database is never asleep. It is continously doing something.
Always makes me laugh when someone says "I don't need to put my database into backup mode when I copy the files. I do it at 3am, no one is logged in, therefore there will be no transactions".
The database never sleeps.
AQ, Jobs, SMON, whatever (many things) - they are all out there doing stuff, forever, always, constantly.
and they commit.
Got it.
A reader, February 10, 2006 - 10:53 pm UTC
Thanks for the explaination!
Subqueries still an open issue in the documentation?
Jernej Kovse, October 30, 2006 - 4:29 am UTC
Tom,
the 10g documentation still states:
"Transactions containing DML statements with subqueries should use serializable
isolation to guarantee consistent read."
(Exactly on the same spot as mentioned above for
the previous documentation versions). The above
sentence is very very confusing. Is it transaction-level
consistency that is meant here?
Can I somehow get SCN on which result of select is consistent ?
Andriy, April 05, 2008 - 12:19 pm UTC
Can I somehow get SCN on which result of select(or cursor) is consistent ?
may be from some oracle dictionary view I can get this information? or I dont have ability to get this information?
April 07, 2008 - 8:53 am UTC
use SCN in the query itself
as system: what time is it, and use "as of <that time, that scn>"
dbms_flashback.get_system_change_number
SCN will not change if you commit a read only transaction
eddie, April 08, 2008 - 3:53 am UTC
It seems that the SCN will change only if you commit a
transaction which really update something in database.
The following is the script I used to verify my idea, please
noted they are copied to sqlplus, instead of typed in by
hand, so they are executed almost at the same time and other
background process, like AQ, has not got the chance to
update SCN.
select dbms_flashback.get_system_change_number from dual;
commit;
select dbms_flashback.get_system_change_number from dual;
commit;
commit;
select dbms_flashback.get_system_change_number from dual;
The result shows that the SCN will not change if you commit
a read only transaction.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
14783996
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
14783996
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
14783996