由函数引起的 Oracle 11 中的变异表 [英] Mutating Table in Oracle 11 caused by a function

问题描述

我们最近从 Oracle 10 升级到了 Oracle 11.2.升级后，我开始看到由函数而不是触发器(我以前从未遇到过)引起的变异表错误.这是在以前版本的 Oracle 中工作的旧代码.

We've recently upgraded from Oracle 10 to Oracle 11.2. After upgrading, I started seeing a mutating table error caused by a function rather than a trigger (which I've never come across before). It's old code that worked in prior versions of Oracle.

以下是会导致错误的场景:

Here's a scenario that will cause the error:

create table mutate (
    x NUMBER,
    y NUMBER
);

insert into mutate (x, y)
values (1,2);

insert into mutate (x, y)
values (3,4);

我创建了两行.现在，我将通过调用以下语句将行加倍:

I've created two rows. Now, I'll double my rows by calling this statement:

insert into mutate (x, y)
select x + 1, y + 1 
from mutate;

这不是复制错误所必需的，但它有助于我稍后的演示.所以表格的内容现在看起来像这样:

This isn't strictly necessary to duplicate the error, but it helps with my demonstration later. So the contents of the table now look like this:

X,Y
1,2
3,4
2,3
4,5

一切都很好.现在是有趣的部分:

All is well. Now for the fun part:

create or replace function mutate_count
return PLS_INTEGER
is
    v_dummy PLS_INTEGER;
begin
    select count(*) 
    into v_dummy
    from mutate;

    return v_dummy;
end mutate_count;
/

我创建了一个函数来查询我的表并返回一个计数.现在，我将把它与 INSERT 语句结合起来:

I've created a function to query my table and return a count. Now, I'll combine that with an INSERT statement:

insert into mutate (x, y)
select x + 2, y + 2
from mutate
where mutate_count() = 4;

结果?这个错误:

ORA-04091: table MUTATE is mutating, trigger/function may not see it
ORA-06512: at "MUTATE_COUNT", line 6

所以我知道导致错误的原因，但我很好奇为什么.Oracle 不是在执行 SELECT、检索结果集，然后 然后 执行这些结果的批量插入吗?如果在查询完成之前已经插入了记录，我只会期望发生变异表错误.但是，如果 Oracle 这样做了，就不会是之前的声明:

So I know what causes the error, but I am curious as to the why. Isn't Oracle performing the SELECT, retrieving the result set, and then performing a bulk insert of those results? I would only expect a mutating table error if records were already being inserted before the query finished. But if Oracle did that, wouldn't the earlier statement:

insert into mutate (x, y)
select x + 1, y + 1 
from mutate;

开始无限循环?

更新:

通过 Jeffrey 的链接，我在 the Oracle docs 中找到了这个:

Through Jeffrey's link I found this in the Oracle docs:

默认情况下，Oracle 保证语句级读取一致性.这单个查询返回的数据集相对于单个时间点.

By default, Oracle guarantees statement-level read consistency. The set of data returned by a single query is consistent with respect to a single point in time.

在他的帖子中还有来自作者的评论:

人们可能会争论为什么 Oracle 不确保这种语句级读取"出现在 SQL 中的重复函数调用的一致性"陈述.就我而言，它可以被认为是一个错误.但这是它目前的工作方式.

One could argue why Oracle doesn't ensure this 'statement-level read consistency' for repeated function calls that appear inside a SQL statement. It could be considered a bug as far as I'm concerned. But this is the way it currently works.

我是否认为这种行为在 Oracle 版本 10 和 11 之间发生了变化?

Am I correct in assuming that this behavior has changed between Oracle versions 10 and 11?

推荐答案

首先，

insert into mutate (x, y)
select x + 1, y + 1 
from mutate;

不启动无限循环，因为查询将看不到插入的数据——只有在语句开始时存在的数据.新行仅对后续语句可见.

Does not start an infinite loop, because the query will not see the data that was inserted - only data that existed as of the start of the statement. The new rows will only be visible to subsequent statements.

这个解释还不错:

当 Oracle 退出当前正在执行的 SQL 引擎时更新语句，并调用函数，然后这个函数——只是就像行后更新触发器会——看到中间状态执行更新语句期间存在的 EMP.这意味着我们的函数调用的返回值很重取决于行更新的顺序.

When Oracle steps out of the SQL-engine that's currently executing the update statement, and invokes the function, then this function -- just like an after row update trigger would -- sees the intermediate states of EMP as they exist during execution of the update statement. This implies that the return value of our function invocations heavily depend on the order in which the rows happen to be updated.

这篇关于由函数引起的 Oracle 11 中的变异表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！