[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.
3.收尾:
--//洗掉建立的函式以及對于表emp.
--//drop table emp purge ;
--//rename empxx to emp;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/542242.html
標籤：Oracle