[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;