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

3.收尾:
--//删除建立的函数以及对于表emp.
--//drop table emp purge ;
--//rename empxx to emp;

• 还没有人评论，欢迎说说您的想法！