[20221227]a mutating table error without a trigger!.txt

[20221227]a mutating table error without a trigger!.txt

--//快放假,沒什麼事情,花一點點時間看了harmfultriggers.blogspot.com,關於觸發器的相關危害.
--//參考連結:harmfultriggers.blogspot.com/2011/12/look-mom-mutating-table-error-without.html
--//實際上許多開發太不瞭解資料庫,觸發器對於資料庫管理就是一種災難,也許有一點點誇大,當然下面的例子
--//並沒有使用觸發器,但是出現ORA-04091: table XXXX is mutating, trigger/function may not see it.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

rename emp to empxx;
--//drop table EMP;
create table EMP
(EMPNO    number(3,0)  not null primary key
,ENAME    varchar2(20) not null
,SAL      number(4,0)  not null)
/
insert into emp(empno,ename,sal) values(100,'Toon',4000);
insert into emp(empno,ename,sal) values(101,'Izaak',5000);
insert into emp(empno,ename,sal) values(102,'Marcel',7000);
insert into emp(empno,ename,sal) values(103,'Rene',8000);
commit;

--//分析表
@ tpt/gts emp

SCOTT@test01p> select * from emp;
     EMPNO ENAME                       SAL
---------- -------------------- ----------
       100 Toon                       4000
       101 Izaak                      5000
       102 Marcel                     7000
       103 Rene                       8000
4 rows selected.

2.測試:

--//測試1:
SCOTT@test01p> update EMP e1 set e1.SAL =  e1.SAL + ((select avg(e2.SAL) from EMP e2) - e1.SAL)/2 ;
4 rows updated.
--//執行OK.

SCOTT@test01p> select * from emp;
     EMPNO ENAME                       SAL
---------- -------------------- ----------
       100 Toon                       5000
       101 Izaak                      5500
       102 Marcel                     6500
       103 Rene                       7000
4 rows selected.

SCOTT@test01p> rollback;
Rollback complete.

--//手工測試驗證執行修改後結果正確.
4000+5000+7000+8000 = 24000
24000/4 = 6000
4000+(6000-4000)/2 = 5000
5000+(6000-5000)/2 = 5500
7000+(6000-7000)/2 = 6500
8000+(6000-8000)/2 = 7000

--//測試2:
--//建立f_new_sal函式,換成函式執行看看..
create or replace function f_new_sal
(p_current_sal in number) return number as
--
pl_avg_sal number;
--
begin
  --
  select avg(SAL) into pl_avg_sal
    from EMP;
  --
  return p_current_sal + (pl_avg_sal - p_current_sal)/2;
  --
end;
/

SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL);
update EMP e set e.SAL = f_new_sal(e.SAL)
                         *
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.F_NEW_SAL", line 8
--//報錯!!因為執行時要保持資料的一致性,而呼叫函式再次訪問時結果已經發生變化,導致報錯.

--//測試3:
--//建立loopback dblink.
CREATE PUBLIC DATABASE LINK LOOPBACK
 CONNECT TO SCOTT
 IDENTIFIED BY <PWD>
 USING 'localhost:1521/test01p:DEDICATED';

--//嘗試建立的函式使用db_link.
create or replace function f_new_sal
(p_current_sal in number) return number as
--
pl_avg_sal number;
--
begin
  --
  select avg(SAL) into pl_avg_sal
  from EMP@loopback;                -- Here: added db-link.
  --
  return p_current_sal + (pl_avg_sal - p_current_sal)/2;
  --
end;
/

SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL);
4 rows updated.

SCOTT@test01p> select * from emp;
     EMPNO ENAME                       SAL
---------- -------------------- ----------
       100 Toon                       5000
       101 Izaak                      5625
       102 Marcel                     6703
       103 Rene                       7166
4 rows selected.
--//執行是成功了,但是注意對比上面直接修改的結果,完全不對,因為這樣雖然規避了查詢ORA-04091錯誤,
--//但是執行時的一致性破壞了,等於每次函式呼叫後返回的結果都是不同,這樣除了第一條修改正確外,其它3條修改都是錯誤的.

SCOTT@test01p> rollback;
Rollback complete.

--//測試4:
--//如果我修改的執行順序呢.
SCOTT@test01p> update (select * from EMP order by EMPNO desc) e   set e.SAL = f_new_sal(e.SAL);
4 rows updated.

SCOTT@test01p> select * from emp order by empno desc;
     EMPNO ENAME                       SAL
---------- -------------------- ----------
       103 Rene                       7000
       102 Marcel                     6375
       101 Izaak                      5297
       100 Toon                       4834
4 rows selected.
--//結果類似,僅僅empno=103的修改正確.

SCOTT@test01p> rollback;
Rollback complete.

--//測試5:
--//建立函式採用自治事務呢?
create or replace function f_new_sal
(p_current_sal in number) return number as
pragma autonomous_transaction;
pl_avg_sal number;
--
begin
  --
  select avg(SAL) into pl_avg_sal
    from EMP;
  --
  return p_current_sal + (pl_avg_sal - p_current_sal)/2;
  --
end;
/
--//pragma autonomous_transaction後面少寫一個逗號.調式浪費許多時間.
--//pragma 翻譯 編譯指示

SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL);
4 rows updated.

SCOTT@test01p> select * from emp ;
     EMPNO ENAME                       SAL
---------- -------------------- ----------
       100 Toon                       5000
       101 Izaak                      5500
       102 Marcel                     6500
       103 Rene                       7000
4 rows selected.
--//採用自治事務後修改正確.

SCOTT@test01p> rollback;
Rollback complete.

--//測試6:
--//函式採用DETERMINISTIC呢?
create or replace function f_new_sal
(p_current_sal in number) return number
DETERMINISTIC
as
pl_avg_sal number;
--
begin
  --
  select avg(SAL) into pl_avg_sal
    from EMP;
  --
  return p_current_sal + (pl_avg_sal - p_current_sal)/2;
  --
end;
/

SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL);
update EMP e set e.SAL = f_new_sal(e.SAL)
                         *
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.F_NEW_SAL", line 9
--//報錯!!

SCOTT@test01p> rollback;
Rollback complete.