Re: Precedence of function over table(s) in SQL

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Fri, 09 Apr 2004 00:00:03 +0100
Message-ID: <fblb70d4a0hm7tuttp36hn0i543gus81gh@4ax.com>

On 8 Apr 2004 10:13:38 -0700, nilendu_at_nilendu.com (Nilendu) wrote:

>Correct me if I am wrong, but I was under the impression that an
>uncommitted transaction could see the committed data done by an inside
>transaction *if* the sub-transaction happens to be an "AUTONOMOUS
>TRANSACTION".
 You are correct. The docs say:

"
Transaction Visibility

Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. These changes become visible to the main transaction when it resumes, if its isolation level is set to READ COMMITTED (the default).

If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; "

 And this contrived example demonstrates it:

SQL> create table t1 (c1 number);

Table created.

SQL> create table t2 (c2 number);

Table created.

SQL> create or replace procedure f2
  2 as
  3 PRAGMA AUTONOMOUS_TRANSACTION;
  4 begin

  5      update t1 set c1 = 2;
  6      insert into t2 values (1);
  7      commit;

  8 end;
  9 /

Procedure created.

SQL> create or replace function f1
  2 return number
  3 as
  4 v_c t1.c1%type;
  5 begin

  6      f2;
  7      select c1 into v_c from t1;
  8      return v_c;

  9 end f1;
 10 /

Function created.

SQL> insert into t1 values (1); -- 'outer' transaction begins

1 row created.

SQL> select f1() from dual; -- f1 calls f2 which commits 'inner' autonomous txn

      F1()

---

         1

SQL> select * from t2;

        C2

---

         1

 This shows:

(1) The 'inner' autonomous transaction could not see the uncommitted data from the 'outer' transaction. (If it could, f1() would return 2 not 1. The 'update' statement in f2 affected zero rows - the data was not visible in that context, t1 appeared empty).

(2) The 'outer' transaction could see the data committed by the 'inner' autonomous transaction. (If it could not, it would see t2 as being empty).

 If you alter the isolation level as described in the docs, it does exactly what is expected:

SQL> truncate table t1;

Table truncated.

SQL> truncate table t2;

Table truncated.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> insert into t1 values (1);

1 row created.

SQL> select f1() from dual;

      F1()

---

         1

SQL> select * from t2;

no rows selected

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

Received on Thu Apr 08 2004 - 18:00:03 CDT