오라클 MERGE INTO 구문과 INACTIVE

24년 8월달에 개발한 소스를 운영서버에 반영후 새벽시간대 갑자기 테이블의 INACTIVE LOCK이 발생하였다. 

 

INACTIVE LOCK이 발생한 테이블은 기존에 아무런 문제가 없던 SELECT 쿼리였다. 

 

8월달에 적용된 목록은

1. 새벽시간대 영화관련 서버의 인물 정보를 연동받아 데이터를 최신화하는 배치의 로직 주석처리 
2. 쿼리 튜닝 2건
3. 실시간으로 연동되던 이미지 정보를 배치작업으로 변경해 실시간 -> 배치로 변경 

이렇게 3가지 였다. 

 

원인을 찾던 중 3번 항목의 로직에서 트랜잭션 처리가 제대로 되지 않아 발생할 수 있다는 의견이 있어 트랜잭션 처리를 추가후 긴급빌드를 진행하게 되었다. 

 

빌드후 모니터링시에는 아무 문제가 없어 해결된줄 알았지만 이전과 동일한 새벽 시간대에 INACTIVE LOCK이 발생하였다. 이후 원인을 명확하게 발견하지 못해 우선 서비스는 정상적으로 진행되어야 하기 때문에 8월달 개발사항을 적용하기 전의 배포버전으로 원복을 진행하였다. 

 

8월에 적용된 목록중 1번과 2번 항목은 문제가 없을 것으로 판단하여 차주 하나씩 적용 후 모니터링을 진행하기로 하였고, 원인이라고 생각이 되는 3번에 대해 모든 로직과 쿼리등을 전수조사 하였다.

 

해당 로직을 확인해보니 과도하게 모든 쿼리가 MERGE INTO 문으로 처리되어 있다는 것을 발견하였다. 

 

오라클 MERGE

 

MERGE INTO 구문은 두 가지 작업을 결합한 구문이다.

• UPDATE : 이미 존재하는 데이터를 업데이트 한다.
• INSERT : 해당 조건에 맞는 데이터가 없으면 새 데이터를 삽입한다.

업데이트와 삽입을 동시에 처리할 수 있는 매력적인 구문으로 조건에 맞는 경우에는 업데이트, 조건에 맞지 않으면 삽입을 진행한다. UPDATE 와 INSERT 구문을 따로 따로 만들지 않아도 되고, 로직이 보기에 더 간결해져 해당 구문으로 INSERT 가 발생할 수 있는 배포작업 테이블을 해당 구문으로 처리하였다. 

 

하지만 MERGE INTO 구문은 업데이트와 삽입 작업이 모두 수행될 수 있기 때문에 이 작업들에 따른 락(LOCK)이 필요하다는 것을 알았다. 

 

오라클에서 MERGE 구문에 의해 발생하는 락은 주로 DML 락 (Data Manipulation Lock)이다. 

• UPDATE 작업 : UPDATE를 수행하는 동안, 대상 행에 대해 TM락(Table Lock, Row-Level Lock)이 발생하여, 해당 행이 다른 트랜잭션에서 수정되지 않도록 보호한다.              

        * TM 락은 테이블에 대한 락이지만, 행 수준에서 발생하여 동시에 다른 행은 수정 가능하게 한다.

        * UPDATE 작업은 행 수준에서 공유 락(S) 또는 공유 - 행 - SRX 락을 발생시킬 수 있다.

 

• INSERT 작업 : 새로운 데이터를 삽입할 때는, 새로 삽입되는 행에 대해 TX 락(Transactional Lock, Row Exclusive Lock)이 발생한다. 

        *삽입되는 동안 새롭게 생성되는 행들은 다른 트랜잭션에서 조회하거나 수정할 수 없다.

 

 

오라클의 MERGE INTO는 기본적으로 업데이트와 삽입 작업을 하나의 트랜잭션 안에서 처리하는 방식이므로, 다음과 같은 락 메커니즘이 발생한다.

1. 매칭되는 행에 대한 UPDATE:
   • UPDATE 조건에 해당하는 행에 대해 Row Exclusive (RX) 락이 발생한다. 이는 해당 행이 수정 중이기 때문에 다른 트랜잭션이 해당 행에 접근하여 업데이트하거나 삭제할 수 없도록 보호한다.
   • 동시에 다른 트랜잭션에서 해당 행을 조회(SELECT)하는 것은 가능합니다. 즉, 읽기에는 영향을 주지 않으나 쓰기에는 제한이 발생한다.
2. 매칭되지 않는 행에 대한 INSERT:
   • INSERT 구문은 새로운 행을 삽입하기 때문에 삽입되는 행에 대해 Row Exclusive (RX) 락이 발생한다.
   • 새로 삽입된 행은 트랜잭션이 커밋되기 전까지는 다른 트랜잭션에서 조회하거나 수정할 수 없다.
3. 테이블 수준의 락(TX 락):
   • MERGE가 수행되는 테이블 자체에는 TX 락이 걸립니다. 이는 트랜잭션 단위로 데이터를 일관되게 관리하기 위한 락으로, 트랜잭션이 완료될 때까지 해당 테이블에 대한 DML 작업이 순차적으로 처리된다

 

락 경쟁과 동시성 문제

• 경쟁 조건: MERGE INTO 구문은 업데이트와 삽입이 동시에 발생하기 때문에, 다수의 세션에서 같은 테이블에 대해 동시에 MERGE 작업을 수행할 경우 락 경쟁이 발생할 수 있다. 이는 다음과 같은 상황에서 발생할 수 있다.
  • 두 개 이상의 트랜잭션이 같은 행에 대해 업데이트하려고 시도할 때.
  • 두 개 이상의 트랜잭션이 동일한 조건에서 삽입하려고 할 때.
• 해결 방안: 락 경쟁을 피하기 위해 행 수준에서 락을 최소화하는 방식으로 처리하거나, 가능한 비동시적으로 작업이 이루어질 수 있도록 스케줄링하는 것이 좋다.

 

 

이걸 알았다면 배포작업에 대한 로직을 MERGE INTO 구문으로 처리하지 않았을 것이다....

 

개발을 진행할때 여러가지 요소를 인지하고 시야를 넓게 가져야 겠다. 또 내가 개발하려는 방향이 맞는지 한번 더 확인해봐야겠다.