Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Bug?

Re: Oracle Bug?

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sat, 23 Apr 2005 23:35:15 +0100
Message-ID: <9rhl6156ntp3kpfp4cucm6ulsub0u5603k@4ax.com>


On Sat, 23 Apr 2005 12:46:56 -0700, DA Morgan <damorgan_at_x.washington.edu> wrote:

>Still confused.

 Could you explain what you think an SCN represents, and what you think increments it? My understanding is the main way to increment the SCN is to commit; but simply _starting_ a new transaction does not increment the SCN. SCN is incremented by the _end_ of transactions, not the start. Xho's reply mirrors this impression. As does the output of the script.

 The only way I think I can understand your confusion here is if you think that a transaction, when started, is "assigned" an SCN, and that dbms_flashback.get_system_change_number gets the SCN "assigned" to that transaction? This is not the case.

 dbms_flashback.get_system_change_number gets the current SCN of the database - this has nothing to do with the current transaction; it can't be, since you don't have an effect on the SCN until you end the transaction.

 Going step by step, assuming no other activity on the database:

 Database starts at SCN 1.

>TX1 does an insert but does not commit.

 OK. The database's SCN has not been affected by the transaction just started or the insert within it. Still at SCN 1.

>ATX does an insert as part of a completely independent transaction.

 Right. The row for ATX gets the current SCN - no commits so far, so we're still on SCN 1.

 So, so far we have TX1 inserted (uncommitted) with an SCN value of 1, and ATX inserted (uncommitted) with the same SCN value of 1.

>Commits the record and that transaction is complete independently
>of anything happening with regard to TX1.

 OK, we've got a commit. SCN goes up - we're at SCN 2.

 ATX gets committed to the database, but that doesn't change the values in the row - the value was based on the SCN at the time, which was before the commit. So the data we've got hasn't changed, but the database has now changed state.

 TX1 was inserted during SCN 1, and so the row has 1 in the column.  ATX was inserted during SCN 1, and so the row has 1 in the column.  

 A commit was done, which increases the SCN.

>TX2 does an insert followed by a commit that also commits
>the record identified as TX1.

 So at the time that the row TX2 is inserted, the SCN had gone up.

 TX1 was inserted during SCN 2, and so the row has 2 in the column.

 Both are now committed; the database is now on SCN 3.

>So, it seems to me, TX1 and TX2 are part of one transactions and,
>ATX being part of an autonomous transaction, should have a
>different SCN.
>
>So where am I wrong?

 Certainly no argument as to which transaction each row belongs to. But just because it's SCN 1 at the start of a transaction doesn't mean it'll be SCN 1 all the way through; the SCN is one situation where you can see a side-effect of other transactions committing. It's pretty close in principle to a sequence in that regard.

 How about simplifying first; let's just show how transactions affect the SCN - does starting a transaction modify the SCN? Then how about committing? Does the following make sense to you:

SQL> set feedback off
SQL> exec dbms_output.put_line(dbms_flashback.get_system_change_number); 4655113

SQL> insert into t values (1);
SQL> exec dbms_output.put_line(dbms_flashback.get_system_change_number); 4655113

SQL> commit;
SQL> exec dbms_output.put_line(dbms_flashback.get_system_change_number); 4655115

SQL> insert into t values (2);
SQL> exec dbms_output.put_line(dbms_flashback.get_system_change_number); 4655115

SQL> commit;
SQL> exec dbms_output.put_line(dbms_flashback.get_system_change_number); 4655117

 SCN before transaction starts = SCN within transaction  But SCN after commit > SCN before commit.

 The fact that in your example it's an autonomous transaction in the middle isn't really relevant to the SCN - the important bit is it did a commit - this increases the SCN of the database.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Received on Sat Apr 23 2005 - 17:35:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US