본문 바로가기
ETC

[ETC] Merge 문의 동시성

by 기몬식 2024. 3. 24.

최근 회사에서 검수 기능을 고도화하는 업무가 있었습니다. 상품을 스캔한 후 적절한 비즈니스 로직에 따라 수량을 데이터베이스에 자동으로 저장하는 로직입니다.

 

@Transactional
public void inspectGoods(goods request) {
  // 상품 정보 조회
  Goods goods = findGoods(request);

  // 상품 상태에 따른 입고 수량 설정 로직

  // 상품 검수
  merge(goods);
}

 

검수 기능의 흐름은 대략적으로 위 코드와 같습니다. 기존에 존재하던 기능에서 수량 설정 로직만 추가되었고 추가로 QC를 진행 후 정상적인 과정을 통해 운영에 신규 기능이 배포되었습니다. 하지만 며칠 뒤 APM 모니터링을 통해 신규 개발 API 에 primary key violation 에러가 간헐적으로 발생되는 것이 확인됐습니다.

 

해당 API 네트워크 워터폴을 분석해보니 매우 짧은 시간(거의 동시에)에 동일한 API 를 n 번 호출하여 발생한 문제였습니다. 조치를 위해 확인해보니 클라이언트 사이드에서 다음과 같은 문제점이 있었습니다.

 

  1. 두개의 상품(마스터, 검수) 조회 로직으로 잘못된 비즈니스 플로우로 진행된다.
  2. 네트워크 지연이 발생하면 자체적으로 검수 상품 조회 API 를 호출한다.

부끄럽지만 기능 구현 요건을 잘못 이해하여 실수가 발생했고 해당 부분을 담당자와 문제를 신속하게 파악하고 수정을 통해 정상적으로 복구했습니다.

 

비록 원인이 비정상적이었던 해프닝(?)이었지만 위 문제를 해결하기 위해 여러 자료를 조사했습니다. 그 중 하나인  'merge 문의 동시성'에 대해 알게 된 내용을 공유하고자 이렇게 포스팅을 작성하게 되었습니다.

 

결론부터 말하자면 merge 구문은 all or nothing 의 의미로 atomic 을 보장하지만 로우 레벨의 오퍼레이션에서 독점적으로 lock 을 획득하는 과정은 atomic 하지 않습니다.

시나리오

먼저 실무에서 발생한 오류 사항은 먼저 매우 짧은 시간에 동일한 merge 문이 n 번 호출됨으로 인해 발생한 race condition 문제입니다. 즉 선행 트랜잭션이 종료되기 이전에 후행 트랜잭션에 의해 pk 충돌이 발생하여 tx lock 경합이 발생한 상황입니다.

 

이 상황을 테스트해보기 위해 먼저 로컬 환경에서 테스트용 테이블을 생성합니다.

 

create talbe foo (
  id number primary key,
  name varchar2(10),
);

 

그 후 두개의 세션에서 순차적으로 실행할 쿼리를 다음과 같이 작성합니다.

 

merge into foo a using (select 1 as id, 'test' as name from dual) b on (a.id = b.id)
when matched then
  update set a.name = 'test1'
when not matched then
  insert (id, name) values (b.id, b.name);

 

먼저 첫번째 세션에서 merge 문을 실행 후 활성화된 락 정보를 확인하기 위해 v$lock 뷰를 조회합니다.

 

select * from v$lock where type = 'TX';

 

V$LOCK

v$lock 은 Oracle 에서 현재 활성화된 락 정보를 통해 락 상태를 모니터링하고 분석하는데 사용되는 뷰로 주요 컬럼에 대한 설명은 다음과 같습니다.

주요 컬럼

 

  • ADDR: 락의 주소.
  • SID: 락을 보유한 세션의 식별자(ID).
  • TYPE: 락의 유형으로 DML, DDL, CF, TM, UL 등이 있습니다.
  • LMODE: 락을 보유하거나 획득하기 위해 대기 중인 세션의 락 모드.
  • REQUEST: 락을 요청한 세션의 락 모드(lock mode)로 세션이 락을 요청하고 있는 경우에만 사용됩니다.
  • BLOCK: 락을 획득하거나 획득하기 위해 대기 중인 세션의 식별자(ID)로 락이 블로킹 상태인 경우에만 해당됩니다.
  • BLOCKER: 락을 획득하거나 획득하기 위해 대기 중인 세션을 블로킹하는 세션의 식별자(ID)로 락이 블로킹 상태인 경우에만 해당됩니다.

LMODE & REQUEST

 

  • 0(none): 세션이 현재 락을 보유하고 있지 않은 상태.
  • 1(null): 오라클 내부에서 사용되는 값.
  • 2(row-S(SS)): 다수의 세션 간에 동시에 데이터를 읽는 것을 명시적으로 허용하는 락 모드.
  • 3(row-X(SX)): 특정 데이터 행을 수정하기 위해 사용되는 배타적인 락 모드.
  • 4(share(S)): 공유 락 모드로 다른 세션들이 데이터를 읽을 수 있지만 수정할 수 없는 락 모드.
  • 5(S/Row-X(SSX)): 다수의 세션이 데이터를 공유하면서도 특정 데이터 행에 대한 수정이 필요한 경우 사용하는 락 모드.
  • 6(exclusive(X)): 하나의 세션이 데이터를 수정하는 동안 다른 세션들은 해당 데이터에 대한 읽기 및 쓰기 접근을 할 수 없는 락 모드.

 

위의 쿼리를 실행하면 다음과 같은 결과를 얻습니다.

 

 

첫번째 세션(314)에서 실행된 merge 문은 현재 X Lock 을 획득한 상태입니다. 그 후 동일한 mere 문을 두번째 세션에서 실행하면 아래와 같이 첫번째 세션에 의해 blocked 된 상태로 대기합니다.

 

 

락 상태를 모니터링하기 위한 시스템 카탈로그 뷰인 dba_blockers 와 dba_waiters 를 조회합니다. 먼저 다른 세션의 작업을 차단하고 있는 세션을 조회할 수 있는 dba_blockers 뷰를 조회합니다.

 

select * from dba_blockers;

 

 

선행 세션 314가 현재 블로킹 상태에 있음을 확인할 수 있습니다.

 

select * from dba_waiters;

 

dba_waiters 를 위의 쿼리를 이용하여 조회합니다. dba_waiters 뷰는 락 대기 상태에 있는 세션들에 대한 정보를 제공하며 대기 중인 세션과 해당 세션이 어떤 락을 기다리고 있는지 그리고 현재의 락 모드와 같은 정보를 제공합니다.

 

반환된 행의 내용을 해석해보자면 두번째 세션(172)은 현재 share lock 락을 요청(mode_requested)하고 대기 중인 세션(waiting_session)으로 현재 락을 점유 중인 314 세션(holding_session)의 exclusive lock(mode_held) 의해 blocked 된 상태입니다. 즉 현재 하나의 자원이 두개의 세션에 의해 경합이 발생된 상태가 식별된 것입니다.

 

그 후 v$lock 뷰를 재조회합니다.

 

 

두번째 세션이 172 에 대한 락 정보가 첫번째 세션과는 다르게 두개의 행이 추가되었습니다. 먼저 두번째 세션에서 share lock 을 요청한 것을 통해 오라클은 데이터 정합성을 위해 어떤 전략으로 데이터에 접근하는지 간접적으로 추론할 수 있습니다. 쓰기 작업이 발생하면 테이블에 로우를 추가한 후 인덱스도 함께 추가하게 되는데 이 과정에서 레코드가 unique 속성을 위반하는지 확인하기 위해 share lock 을 사용하는 것입니다.

 

결국 matched 를 판단하는 on 절의 조건은 consistent read 로 동작되기 때문에 선행 세션에서 커밋 되기 이전 후행 세션에서도 동일하게 x lock 을 획득 후 insert 문으로 계획이 세워진 후 blcoked 된 채로 대기 중입니다. 이후 선행 세션이 정상적으로 merge 문을 실행 후 커밋을 수행하게 되면 후행 세션에서는 update 문이 수행되지 않고 ORA-00001 에러가 발생합니다.

 

 

즉 정리하자면 다음과 같은 시나리오로 정리할 수 있습니다.

 

  1. 두개의 세션에서 동시에 MERGE 구문을 실행한다.
  2. 두개의 세션에서 동시에 S LOCK을 획득하지만 결국 두 개 세션 중 하나의 세션만이 X Lock 을 선점하게 된다.
  3. 따라서 후속으로 실행된 세션을 첫번째 세션이 트랜잭션이 커밋될때까지 BLOCKED 된 상태로 대기한다. 하지만 후속 세션은 조회 단계에서 데이터가 존재하지 않았기 때문에 INSERT 로 계획이 세워진다.
  4. 첫번째 세션에서 INSERT 작업 후 트랜잭션이 커밋된다.
  5. 두번째 세션이 LOCK 을 얻어 INSERT 를 실행하지만 이미 ROW 가 존재하기 때문에 PK Viloation 이 발생한다.

PS

추가로 리더분들께서 merge 문 사용 여부에 대해 열띈 토론을 하셨는데 그에 대한 최종 의사결정은 "병행해서 사용하자" 였습니다. 하지만 저희 파트는 Entity 를 개발자가 직접 제어해야한다는 맥락에서 값 존재 여부를 개발자가 알 수 없는 merge 문에 다소 회의적인 의견을 수렴하여 이번 일을 계기로 merge 문을 제거하도록 의사결정을 했습니다.

저작자표시

티스토리툴바