GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。 作者： 葉金榮 文章來源：GreatSQL社區原創 如何快速臨時禁止某賬戶登入 角色ROLES管理需要先激活 關於授權的其他幾點補充 如何複製/復用賬戶 ...
[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;
