福建省建设厅网站人员,广州开发区控股集团有限公司,傻瓜一键自助建站系统,大型网站建设建设公司ORACLE报错:ORA-04091 表发XXX生了变化#xff0c;触发器/函数不能读它 问题描述问题分析解决办法拓展#xff1a;自治事务的特点 问题描述
在开发校验函数FUNCTION的时候#xff0c;用数据跑批测试的时候报错。经排查这个校验函数FUNCTION的被一个存储过程中的update语句调… ORACLE报错:ORA-04091 表发XXX生了变化触发器/函数不能读它 问题描述问题分析解决办法拓展自治事务的特点 问题描述
在开发校验函数FUNCTION的时候用数据跑批测试的时候报错。经排查这个校验函数FUNCTION的被一个存储过程中的update语句调用了。所以update更新数据的同时又select了校验函数FUNCTION猜测这两个事务冲突了导致了这个报错。
校验存储过程例如代码所示校验存储过程P_CHECK_VALUE中的update调用了校验函数PKG_CHECK_VALUE.F_CHECK_VALUE(ARG1, ARG2)
--调用校验的存储过程
CREATE OR REPLACE PROCEDURE P_CHECK_VALUE(V_TABLENAME VARCHAR2, V_DECLAREDATE VARCHAR,V_BATCHNO VARCHAR2 DEFAULE NULL,V_4 VARCHAR2 DEFAULT NULL) IS
BEGINFOR CAL IN (...) LOOPV_SQL UPDATE TABLE1 SET TABLE1.A1 TABLE1.A1 || (SELECT TABLE2.B2 FROM TABLE2 WHERE TABLE2.NUM1 PKG_CHECK_VALUE.F_CHECK_VALUE(ARG1, ARG2));EXECUTE IMMDEIATE V_SQL;COMMIT;END LOOP;
END;校验函数是写在校验包里的例如
--校验包的函数
CREATE OR REPLACE PACKAGE BODY PKG_CHECK_VALUE ISFUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER ISBEGINSELECT COUNT(*) 0 THEN 1 ELSE 0 ENDINTO RESULT FROM TABLE1 WHERE ...;RETURN(RESULT);END;END;
END PKG_CHECK_VALUE;问题分析
在函数中查询某张表前对表做了更新和插入操作,使表数据发生了变化,而函数返回的数据已经发生变化,导致出错. 如果既对同一个表进行update又调用其他触发器/函数对同一个表进行查询select操作猜测是这两个操作同时处理一张表导致两个事务冲突了。 不清楚会先update再select还是先select再update总之这两个操作是对同一张表进行了增删改查处理了。
解决办法
在函数BEGIN之前加入一个自治事务pragma autonomous_transaction;**其中PRAGMA关键字通知PL/SQL编译器,将声明它的这个PL/SQL代码块分割为一个自治的或独立的事务。这意味着在使用 PRAGMA AUTONOMOUS_TRANSACTION 的函数或过程中可以执行 SQL 操作并提交或回滚这些操作而不会影响主事务中的数据。例子中的主事务是update操作子事务是函数的select操作总的来说PRAGMA AUTONOMOUS_TRANSACTION 的作用是在 PL/SQL 中创建一个独立的事务用于执行与主事务完全独立的操作通常用于记录日志。
PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”翻译的还算好理解对于定义成自治事务的Procedure实际上相当于一段独立运行的程序段这段程序不依赖于主程序也不干涉主程序 pragma autonomous_transaction;
修改后如下所示不会报错了
--校验包的函数
CREATE OR REPLACE PACKAGE BODY PKG_CHECK_VALUE ISFUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER ISpragma autonomous_transaction; --事务自治BEGINSELECT COUNT(*) 0 THEN 1 ELSE 0 ENDINTO RESULT FROM TABLE1 WHERE ...;RETURN(RESULT);END;END;
END PKG_CHECK_VALUE;拓展自治事务的特点 第一,这段程序不依赖于原有Main程序比如Main程序中有未提交的数据那么在自治事务中是查找不到的。 第二,在自治事务中commit或者rollback只会提交或回滚当前自治事务中的DML不会影响到Main程序中的DML。
