[20120421] 自治事务(AUTONOMOUS_TRANSACTION) 与临时表.txt

[20120421] 自治事务(AUTONOMOUS_TRANSACTION) 与临时表.txt

前一阵遇到一个死锁的问题，存储过程使用了自治事务，测试以下使用临时表的问题。

1.测试环境：
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

drop table t purge ;
create global temporary table t (id number,name varchar2(10)) on commit delete rows;
CREATE UNIQUE INDEX I_T_I ON SCOTT.T(ID);

CREATE  PROCEDURE test1( l_id number,l_name varchar2,flag VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        IF flag = 'INSERT' THEN
                insert into t values(l_id,l_name);
        END IF;
        IF flag = 'UPDATE' THEN
                update t set id=l_id,name=l_name where id=l_id;
        END IF;
        IF flag = 'DELETE' THEN
                delete from t where id=l_id;
        END IF;
        commit;
        dbms_output.put_line (flag);
END;
/


2.测试：
SQL> insert into t values(1,'a');
1 row created.

SQL> update t set name='aaa' where id=1;
1 row updated.

SQL> exec test1(1,'A','UPDATE');
UPDATE
PL/SQL procedure successfully completed.

--可以发现居然没事！
SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa

--实际上根本没有修改操作。做一个delete操作：
SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa

SQL> exec test1(1,'aaa','DELETE');
DELETE

PL/SQL procedure successfully completed.

SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa
--可以发现根本没有删除记录，就像根本没有执行一样！

--再做一个insert操作!

SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa
SQL> exec test1(1,'A','INSERT');
BEGIN test1(1,'A','INSERT'); END;

*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "SCOTT.TEST1", line 6
ORA-06512: at line 1


SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa

--我建立的唯一索引在表T，理论将在插入(1,'A')是应该报ora-00001错误，没有出现。

3.删除临时表T,修改属性为preserver看看。

drop table t purge ;
create global temporary table scott.t ( id    number, name  varchar2(10)) on commit preserve rows;
CREATE UNIQUE INDEX I_T_I ON SCOTT.T(ID);

SQL> insert into t values(1,'a');
1 row created.

SQL> update t set name='aaa' where id=1;
1 row updated.

SQL> exec test1(1,'A','UPDATE');
UPDATE
PL/SQL procedure successfully completed.

SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa

--可以发现居然没事！情况同上！

SQL> exec test1(1,'aaa','DELETE');
DELETE

PL/SQL procedure successfully completed.

SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa

--可以发现根本没有删除记录，就像根本没有执行一样！
--再做一个insert操作!
SQL> exec test1(1,'aaa','INSERT');
BEGIN test1(1,'aaa','INSERT'); END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SCOTT.TEST1", line 6
ORA-06512: at line 1

--出现了死锁！
SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa

--插入另外的数值看看。
SQL> exec test1(2,'aaa','INSERT');
INSERT

PL/SQL procedure successfully completed.

SQL> select * from t;
        ID NAME
---------- ----------
         1 aaa
         2 aaa

总结：实际上了解临时表的特性就很容易理解以上测试！
从以下链接摘录一段：
http://rajeshwaranbtech.blogspot.com/2010/09/transactionlevel-temporary-tables-with.html

        If you are using GLOBAL TEMPORARY tables, one thing you must be aware of is the fact that a  transaction‐level temporary
table cannot be used by more than one transaction, in a single session, at the same time. Temporary tables are managed at the
session‐level, and when they are created in the mode that makes them ?transactional? (on commit, delete rows), they can only
be used by either the parent, or the child transaction, but not both.
另外我的测试在执行如下代码，并不报错！

SQL> drop table t purge ;
Table dropped.
SQL> create global temporary table t (id number,name varchar2(10)) on commit delete rows;
Table created.
SQL> insert into t values(1,'a');
1 row created.

CREATE OR REPLACE PROCEDURE auto_proc2
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   FOR x IN (SELECT *
               FROM t)
   LOOP
      NULL;
   END LOOP;
END;
/

SQL> exec auto_proc2
PL/SQL procedure successfully completed.