| insert into a select * from a [message #28031] |
Mon, 24 November 2003 06:01  |
Frank Thomas
Messages: 3
Registered: November 2003
|
Junior Member |
|
|
Hi,
how is
insert into a select * from a;
working?
Although I'm an oracle newbie I think the question is not that easy
to answer. After I read some Oracle docu and several articles at
asktom.oracle.com and at orafaq.net I am still deranged. I am able
to see the changes I made to rows (insert or update) independent
of the isolation level. Each block must contain something else than
just the SCN, because otherwise the above statement would result
in an endless loop. What part of the data my own Tx inserts/
updates is a cursor reading? Every thing before declare, before
open, before first fetch???? Other rules for pl/sql?
I'm puzzled. Can someone point me to the right place to find an
answer to these questions?
|
|
|
|
| Re: insert into a select * from a [message #28032 is a reply to message #28031] |
Mon, 24 November 2003 08:39  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Oracle's Statement level read consistency ensures that you see the data (ie the query 'select * from a ') as of the SCN when it begins the statement.
For eg)
On session 1 , I do this :
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects;
Table created.
SQL> insert into t select * from t;
35529 rows created.
On Session 2, I do this :
-- Get the current SCN
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
387850949
-- I get the Sid,Serial# of Session 1 and see what SCN that transaction(ie insert) uses,whilst its running, I get
SQL> select t.start_scnb from V$transaction t,v$session s where t.addr=s.taddr and s.sid=7 and s.serial#=810;
START_SCNB
----------
387850949
So data blocks with this SCN are the only ones the query sees and thats what will get inserted into T. No Infinite loops. This will hold good for ANY Isolation level ( ie Read Committed, Read Only , Serialiaable ).
Any block of table T that changes during this statement(ie somebody else performs a DML on these blocks) execution will have a higher SCN and hence Oracle will reconstruct a read consistent image (as of the Snapshot scn) using the rollback segments. There is an Interested Transaction List (ie ITL) in each block header that knows whether the block has any pending changes and other info(eg rollback segment etc) .
Hope this clears up your confusion.
Thiru
|
|
|
|
| Re: insert into a select * from a [message #28033 is a reply to message #28032] |
Mon, 24 November 2003 10:09 |
Frank Thomas
Messages: 3
Registered: November 2003
|
Junior Member |
|
|
I was just talking about one(!) session. I thought that the SCN is fix
for every transaction and just set at the beginning of the
transaction. If this is not true how does the server know what my
SCNs are? If it is true I'll have the original problem that all rows I
add in one transaction have the same SCN.
I saw a discusion (think it was on asktom) about when the SCN
changes. And someone said that it changes if 1 block is changed
more than 253 times in the same transaction. Maybe the server
really keeps a list of active SCNs for every transaction , but
normally doesn't have to add a new one. But than the
documentation about how consistent_get works should be more
precise. It would not ignore all SCNs > start-SCN. As well all
blocks would not just contain the SCN but also this additional
byte.
Can someone shed some light on this?
|
|
|
|
| Re: insert into a select * from a [message #28034 is a reply to message #28033] |
Mon, 24 November 2003 10:51 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
and I was also talking about 1 session only ( In the 2nd session I got the SCN of the transaction running in the 1st Session , not the 2nd session) . I could have got the same results in the 1st session after the insert was done. I just wanted to capture the start_Scn of a running statement.
The SCN is fixed at the start of the transaction only for isolation levels of Serializable and REad Only. For default isolation level of READ COMMITTED,it gets the Scn at the beginning of the Statement , not transaction.
If you want to freeze the database as of the start of the transaction(ie dont see changes made by other transactions), then use SERIALIZABLE or READ ONLY , not read committed.
|
|
|
|
| Re: insert into a select * from a [message #28044 is a reply to message #28034] |
Tue, 25 November 2003 01:15 |
Frank Thomas
Messages: 3
Registered: November 2003
|
Junior Member |
|
|
OK, lets say the SCN is fix for isolation level serializable. In that
case every row I'm touching will get that that SCN. In any one
transaction you can run "insert into a select * from a" as often as
you like and the number of rows always doubles. Lets say I start
with a table that contains 1 row. That row has some SCN lets call it
S1 and our transaction has S2. I do "insert into a select * from a".
After that statement I have one row with S1 and one with S2. I
rerun "insert into a select * from a" . Now the server can do two
different things. It can select rows with SCN S2 or not.
OK, lets say it does read them: It inserts the new rows with the
same SCN S2. Than we have an endless loop, because we are
reading rows with SCN S2 over and over again.
If the server would ignore SCN S2 we would have 3 rows in the
table. This is not the case!
The description of isolation level serializable should be something
like "you see all data from other transactions that committed
before your tx started and all uncommitted data of your own tx
from the start of your statement". To make this explainable with
SCNs you would need a term like "my SCNs" and change the SCN
of the new rows wih every statement. In that case the list of "my
SCNs" would grow by one with every statement in that tx. Or you
do what I suspect. You have somekind of sub-counter to be able
to say "this is from the n-th statement of my tx" and you need that
sub-counter just in case we are talking about rows of your own
SCN.
|
|
|
|
| Re: insert into a select * from a [message #28047 is a reply to message #28044] |
Tue, 25 November 2003 06:02 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
The documentation is right in this respect and says this
" Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements
"
It does not see changes made by other transactions but itself. Plus the block header sort of maintains a history of transactions ( ie Sequence numbers) in addition to the SCN to keep track of the internal transaction changes also.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
