ORACLE报错:ORA-04091 表发XXX生了变化,触发器/函数不能读它

2024-01-09 12:42:48

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
BEGIN
	FOR CAL IN (...) LOOP
		V_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" IS
	FUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER IS
		BEGIN
			SELECT COUNT(*) > 0 THEN 1 ELSE 0 END
			INTO 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" IS
	FUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER IS
		pragma autonomous_transaction;	--事务自治
		BEGIN
			SELECT COUNT(*) > 0 THEN 1 ELSE 0 END
			INTO RESULT 
			FROM TABLE1 
			WHERE ...;
			RETURN(RESULT);
		END;
	END;
END PKG_CHECK_VALUE;

拓展:自治事务的特点

  • 第一,这段程序不依赖于原有Main程序,比如Main程序中有未提交的数据,那么在自治事务中是查找不到的。

  • 第二,在自治事务中,commit或者rollback只会提交或回滚当前自治事务中的DML,不会影响到Main程序中的DML。

文章来源:https://blog.csdn.net/weixin_43136158/article/details/135473372
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。