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: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 23 Apr 2005 12:46:56 -0700
Message-ID: <1114285379.522885@yasure>


Andy Hassall wrote:

> On Fri, 22 Apr 2005 21:45:36 +0100, Andy Hassall <andy_at_andyh.co.uk> wrote:
>
>

>>Are you perhaps thinking that DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER should
>>return the SCN as of the start of the transaction? I'm not sure it gives that
>>type of read-consistent guarantee - it really does give you the current SCN,
>>which presumably can change mid-transaction due to other unrelated transactions
>>committing. That ought to be easy to test; consider:
>>
>>SQL> begin
>> 2      for i in 1..10 loop
>> 3          dbms_output.put_line(dbms_flashback.get_system_change_number);
>> 4          dbms_lock.sleep(1);
>> 5      end loop;
>> 6  end;
>> 7  /
>>4302410
>>4302411
>>4302411
>>4302411
>>4302412
>>4302412
>>4302412
>>4302413
>>4302413
>>4302413

>
>
> Actually it'd be a better demonstration if it shows this happens even after
> starting a transaction, so:
>
> SQL> insert into test values ('TX3', 0);
>
> 1 row inserted
>
> SQL> begin
> 2 for i in 1..10 loop
> 3 dbms_output.put_line(dbms_flashback.get_system_change_number);
> 4 dbms_lock.sleep(1);
> 5 end loop;
> 6 end;
> 7 /
>
> 4316672
> 4316672
> 4316673
> 4316673
> 4316673
> 4316674
> 4316674
> 4316674
> 4316675
> 4316675
>
> PL/SQL procedure successfully completed

Still confused.

TX1 does an insert but does not commit.

ATX does an insert as part of a completely independent transaction. Commits the record and that transaction is complete independently of anything happening with regard to TX1.

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

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?

Thanks again.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Apr 23 2005 - 14:46:56 CDT

Original text of this message

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