Подтвердить что ты не робот

Мутационная таблица в Oracle 11, вызванная функцией

Недавно мы обновили с Oracle 10 до Oracle 11.2. После обновления я начал видеть ошибку мутирующей таблицы, вызванную функцией, а не триггером (которого я никогда не встречал раньше). Это старый код, который работал в предыдущих версиях Oracle.

Здесь сценарий, который вызовет ошибку:

create table mutate (
    x NUMBER,
    y NUMBER
);

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

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

Я создал две строки. Теперь я удвою свои строки, вызвав это утверждение:

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

Не обязательно дублировать ошибку, но это помогает с моей демонстрацией позже. Итак, содержимое таблицы теперь выглядит следующим образом:

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

Все хорошо. Теперь для веселой части:

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:

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 это сделал, не было бы более раннего утверждения:

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

запустить бесконечный цикл?

UPDATE:

Через ссылку Джеффри я нашел это в документах Oracle:

По умолчанию Oracle гарантирует согласованность чтения на уровне инструкций. набор данных, возвращаемых одним запросом, согласуется с единая точка времени.

Там также комментарий автора в его сообщение:

Можно было бы утверждать, почему Oracle не гарантирует, что это "чтение на уровне инструкций" согласованность "для повторных вызовов функций, которые появляются внутри SQL выражение. Это можно считать ошибкой, насколько я могу судить. Но так оно и работает.

Правильно ли я предполагаю, что это поведение изменилось между версиями Oracle версии 10 и 11?

4b9b3361

Ответ 1

Во-первых,

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

Не запускает бесконечный цикл, потому что в запросе не будут отображаться данные, которые были вставлены - только данные, которые существовали с начала инструкции. Новые строки будут видны только для последующих операторов.

Это объясняет это довольно хорошо:

Когда Oracle выходит из SQL-движка, который в настоящее время выполняет update, и вызывает функцию, тогда эта функция - просто как триггер обновления после строки, - видит промежуточные состояния EMP, поскольку они существуют во время выполнения инструкции обновления. Эта подразумевает, что возвращаемое значение нашей функции вызывает серьезные зависят от порядка, в котором строки обновляются.

Ответ 2

Согласованность чтения на уровне отчета и согласованность чтения на уровне транзакций ".

Из руководства:

"Если список SELECT содержит функцию, то применяется база данных согласованность чтения на уровне инструкции на уровне уровня для запуска SQL в функциональном коде PL/SQL, , а не в родительском SQL уровень. Например, функция может получить доступ к таблице, данные которой изменено и совершено другим пользователем. Для каждого выполнения SELECT в функции, новый согласованный моментальный снимок установлено" .

Обе концепции описаны в "Концепции баз данных Oracle®":

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref1955


- → > ОБНОВЛЕНИЕ

- → > * Раздел добавлен после закрыт OP

Правило

Техническое правило, хорошо связанное г-ном Кемпом (@jeffrey-kemp) и хорошо объясненное Toon Koppelaars здесь, сообщается в "Ссылка на язык PL/Sql - Управление побочными эффектами подпрограмм PL/SQL" (ваша функция нарушает RNDS не считывает состояние базы данных)

При вызове из инструкции INSERT, UPDATE или DELETE функция не может запрашивать или изменять любые таблицы базы данных, измененные этим утверждением.

Если функция запрашивает или изменяет таблицу, а оператор DML в этой таблице вызывается функция, тогда ORA-04091 (мутирующий стол ошибка).

Функции PL/SQL, которые могут вызывать SQL-выражения