함수로 인해 발생한 Oracle 11의 테이블 돌연변이

함수로 인해 발생한 Oracle 11의 테이블 돌연변이

최근 오라클 10에서 오라클 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;

무한 루프를 시작하시겠습니까?

업데이트:

Jeffrey의 링크를 통해 Oracle 문서에서 다음을 발견했습니다.

기본적으로 Oracle은 문 수준의 읽기 일관성을 보장합니다.단일 쿼리에 의해 반환되는 데이터 집합은 단일 시점에 대해 일치합니다.

저자의 글에는 다음과 같은 글도 있습니다.

Oracle이 SQL 문 안에 나타나는 반복 함수 호출에 대해 이러한 '문 수준 읽기 일관성'을 보장하지 않는 이유를 설명할 수 있습니다.제가 보기에는 버그로 간주될 수 있습니다.하지만 이것이 현재의 방식입니다.

이 동작이 Oracle 버전 10과 11 사이에 변경되었다고 가정하는 것이 맞습니까?

일단은.

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

쿼리는 삽입된 데이터를 보지 않고 문 시작 시점에 존재했던 데이터만 볼 수 있으므로 무한 루프를 시작하지 않습니다.새 행은 이후 문에만 표시됩니다.

이것은 매우 잘 설명됩니다.

Oracle이 현재 업데이트 문을 실행 중인 SQL 엔진에서 나와 함수를 호출하면 이 함수는 다음 행 업데이트 트리거와 마찬가지로 업데이트 문을 실행하는 동안 EMP의 중간 상태를 확인합니다.이는 함수 호출의 반환 값이 행이 업데이트되는 순서에 크게 의존한다는 것을 의미합니다.

문 수준 읽기 일관성 및 트랜잭션 수준 읽기 일관성"입니다.

설명서에서:

"SELECT 목록에 함수가 포함되어 있으면 데이터베이스는 상위 SQL 수준이 아닌 PL/SQL 함수 코드 내에서 실행되는 SQL에 대해 문 수준 읽기 일관성을 적용합니다.예를 들어, 함수는 다른 사용자가 데이터를 변경하고 커밋한 테이블에 액세스할 수 있습니다.기능에서 SELECT를 실행할 때마다 읽기 일관성 있는 새 스냅샷이 설정됩니다."

두 개념 모두 "Oracle® 데이터베이스 개념"에 설명되어 있습니다.

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

---

->>UPDATE

->>*OP 마감 후 섹션 추가

규칙

켐프 씨(@jeffrey-kemp)가 잘 연결되고 여기서 툰 코펠라르스가 잘 설명한 기술 규칙은 "Pl/Sql 언어 참조 - PL/SQL 하위 프로그램의 부작용 제어"에 보고됩니다(사용자의 함수는 RNDS 읽기 없음 데이터베이스 상태를 위반함).

INSERT, UPDATE 또는 DELETE 문에서 호출될 때 함수는 해당 문으로 수정된 데이터베이스 테이블을 쿼리하거나 수정할 수 없습니다.

함수가 테이블을 쿼리하거나 수정하고 해당 테이블의 DML 문이 함수를 호출하면 ORA-04091(변종 테이블 오류)이 발생합니다.

SQL 문이 호출할 수 있는 PL/SQL 함수

언급URL : https://stackoverflow.com/questions/9935239/mutating-table-in-oracle-11-caused-by-a-function