Andy Hassall wrote:
> 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.
I was assuming, obviously incorrectly, that ORA_ROWSCN could be
used to determine (in a sense) which COMMIT was responsible for
a specific row.
My assumption was that TX1 and TX2 being part of a single transaction
would have the same SCN. That the autonomous transaction, acting in a
sense like another user logging on with a separate session, would have
its own SCN: Obviously not the case. But why still escapes me.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Apr 23 2005 - 18:36:33 CDT
