[Oracle] ORA-04091: table 000 is mutating 에러와 autonomous transactions(자율 트랜잭션)

오라클에서 function을 이용해 update 하는 중에 발생한 에러이다.(사실 이 에러는 회사 선임님쪽에서 발생했다.) mutating?이 뭐지. 찾아봤는데'변이'라는 뜻을 가지고 있었다. 흔히 우리가 말하는 돌연변이(mutant)를 말할 때 같은 의미겠지? ㅎㅎ

변이하고 있는 테이블에 참조를 걸려고 하니 에러가 생긴 것이었다. 아니나 다를까 function을 보니 update 해주는 table을 참조해서 값을 가져오는 기능이었다. 근데 이게 mariaDB(혹은 mysql)에서는 잘 돌아간다. 

회사에서 항상 우리끼리 'mariaDB는 너무 관용적이다'라고 말한다. 안될 것 같은 쿼리도 mariaDB에서 잘 돌아간다.

회사에서 아래와 같은 방법으로 짜여져 있었다.(매우매우 간소화시켜서 올려둡니다)

query:

update chocolate_table
   set brand_name = get_brand_name(1)
 where chocolate_seq = 91

function:

create or replace function get_brand_name
    (in_seq in number)
 
return varchar2
 
is
 
 v_brand_name varchar2(10 char);
 
begin
 
    select brand_name
      into v_brand_name
      from chocolate_table
     where chocolate_seq = in_seq;
 
    return v_brand_name;
 
end;

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it

위의 에러를 보고나서 구글링을 해보니 아주 좋은 해결법이 있었다.

여기의 글 하나만 보고 문제의 원인과 해결법을 알 수 있었다. 사실 권장사항은 이런 쿼리를 짜지 말아라. 가 최우선 방법이었다.

이런 에러가 발생하는 이유는 '트리거' 때문이다. mutating table은 행 수준 트리거(row level trigger)에 영향을 받는다.  

* 행 수준 트리거에 대한 설명

출처를 달아둔 글에서 여러가지 이유가 있지만, 가장 눈에 띄는 건 '트리거는 읽고 있는 테이블을 바꿀 수 없다. 이것이 mutating table error issue를 발생시킨다.'

• A trigger can not change a table that it has read from. This is the mutating table error issue.

이러한 에러를 피할 수 있는 방법도 알려주는데,

첫째, 쓰지마! 객체지향적인 오라클은 테이블과 연관있는 메소드를 제공하지만, 대게 알려진 PL/SQL 개발자들은 꼭 필요한 경우가 아니면 트리거를 피한다.

둘째, 꼭 써야겠다면 'after' 트리거를 이용해라

셋째, (이건 사이트를 참조하는게 좋을 듯.. 홀 박사란 사람이 좋은 방법을 알려줬대요.)

넷째, 자율 트랜잭션(autonomous transactions)을 사용해라. - 내가 사용한 방법.

네번째 방법을 사용한 이유는 가장 간단하게 구현할 수 있었고, 단순히 select해서 update를 하고, 많은 사람들이 동시에 이용할 가능성이 낮은 서비스였기 때문이다. 

자율 트랜잭션이란 기존의 실행중인 트랜잭션과 상관없이 독립적인 트랜잭션을 생성하는 것을 말한다. 독립적으로 행동하는 트랜잭션이기 때문에 꼭 마지막에 commit 혹은 rollback을 선언해주어야 한다.

사용법은 function 사이에 pragma autonomous_transaction 하나만 선언해주면 된다.

create or replace function get_brand_name
    (in_seq in number)
 
return varchar2
 
is
 pragma autonomous_transaction; <== '요기에'
 v_brand_name varchar2(10 char);
 
begin
 
    select brand_name
      into v_brand_name
      from chocolate_table
     where chocolate_seq = in_seq;
    rollback;
    return v_brand_name;
 
end;

해결법은 쉬웠지만 어떤 부작용이 있을까 궁금해서 스택오브플로우를 찾아봤다.

출처: https://stackoverflow.com/questions/1335331/autonomous-transaction

대충 해석해보자면, 메인 트랜잭션에 영향을 받지 않고, 독립적인 세션을 가진다. 그래서 메인 트랜잭션이 끝나기를 기다리지 않는다. logging 테이블이 자율 트랜잭션으로 채워질지도 모른다.(??? 로깅을 제외하고 딱히 부작용이 없는건가 )