Re: Oracle Bug?

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 22 Apr 2005 21:45:36 +0100
Message-ID: <7sni619j0e8ge47lsrf72ihptu2i5b9f78@4ax.com>

On Fri, 22 Apr 2005 12:46:36 -0700, DA Morgan <damorgan_at_x.washington.edu> wrote:

>Oracle 10gR1 (10.1.0.4)
>
>CREATE OR REPLACE PROCEDURE atx IS
>PRAGMA AUTONOMOUS_TRANSACTION;
>BEGIN
> INSERT INTO test VALUES ('ATX', dbms_flashback.get_system_change_number);
> COMMIT;
>END;
>/
>
>BEGIN
> -- first insert
> INSERT INTO test VALUES ('TX1', dbms_flashback.get_system_change_number);
>
> -- call autonomous transaction
> atx;
>
> -- second insert
> INSERT INTO test VALUES ('TX2', dbms_flashback.get_system_change_number);
> COMMIT; -- rollback repaced with a commit
>END;
>/
>
>SELECT * FROM test;
>
>REASON SCNNO
>-------------------- ----------
>TX1 17381335
>ATX 17381335
>TX2 17381336
>
>Why is the record committed the PRAGMA AUTONOMOUX_TRANSACTION
>getting the same SCN as TX1 and why is TX1 not getting the
>same SCN as TX2? It just seems wrong.

"system change number (SCN)

A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN."

 The SCNs shown do seem consistent with the order of inserts vs. commits?

TX1 - not committed yet

ATX - not committed at the time of the insert, so still the same SCN, assuming no other activity on the database

TX2 - the autonomous transaction did a commit, so by the time you get to TX2 the system is on a new SCN.

 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

PL/SQL procedure successfully completed

 SCNs are increasing - I'm not even doing anything on that database, it's the Oracle 10g background processes doing their stuff (auto stats gathering?)

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Fri Apr 22 2005 - 15:45:36 CDT