DONGBUM KIM

Beat Yesterday

3 분 소요

오랫만에 이곳에 글을 쓴다. 신입사원으로 지원시 열심히 썼었던 잊고 있던 오답노트를 다시 정리해본다. 경력이 3년이 넘었는데 아직도 이러한 문제조차 모르는 내 자신을 반성하며 다시 정리한다.

판교의 N 게임사에서 받은 질문.

Q. DB 머신이 물리적으로 서로 다른 머신일 때 어떠한 DB 작업시 트랜잭션 처리를 어떻게 하는가?

A. 하나의 단일 머신에서 처리해서 그러한 것에 신경 쓰지 못했다.

역시 정답을 말하지 못했다. 사실 이러한 처리가 필요하다는 것을 꽤 오래전에 인지했지만 실제로 해본적이 없어서 제대로 대답을 할 수 없었다. 실제로 3년간 했던 프로젝트들 전부 단일 DB 머신에서 처리했던게 사실이고.

2026년 수정 안내 이 글에는 XACT_ABORT_ON / XACT_ABORT_OFF를 “두 개의 명령어”라고 쓴 오류가 있었다. 그런 명령어는 없다. SET XACT_ABORT 하나의 세션 옵션이며 값이 ON/OFF다. 표기를 바로잡고, 분산 트랜잭션에 대한 설명과 트리거 예제의 문제점도 함께 정리한다.

다시 찾아본 정답은 다음과 같다.

출처 : 네이버지식인 (http://kin.naver.com/qna/detail.nhn?d1id=1&dirId=10205&docId=68228257)

MSSQL을 기준으로하며, 서로 링크드 서버로 연결되어 있어야 한다는 전제가 붙는다.

변경할 테이블에 트리거를 생성한다.

CREATE TRIGGER dbo.트리거명 ON dbo.A서버테이블명
FOR UPDATE
AS

DECLARE @I_NO char(4) --변경 전
DECLARE @D_NO char(4) --변경 후

Select  @D_NO =NO --변경 전 값
From  DELETED

Select  @I_NO =NO --변경 후 값
From  INSERTED

--분산트랜잭션 처리(링크드 서버 UPDATE시 에러 없으면 에러 나는 경우가 존재합니다.)
SET XACT_ABORT ON

UPDATE [220.123.123.120].B서버DB명.DBO.B서버테이블명
SET NO = @I_NO
WHERE ID = @D_NO

SET XACT_ABORT OFF

SET XACT_ABORT

XACT_ABORT_ON / XACT_ABORT_OFF라는 별개의 명령어가 있는 것이 아니다. SET XACT_ABORT라는 하나의 세션 옵션이 있고, 여기에 ON 또는 OFF를 준다.

SET XACT_ABORT ON
SET XACT_ABORT OFF

동작 차이는 이렇다. 프로시저 안에 INSERT가 두 개 있고 두 번째에서 런타임 오류가 났다고 하자.

  • SET XACT_ABORT ON — 오류가 나는 즉시 트랜잭션 전체를 중단하고 롤백한다. 첫 번째 INSERT도 취소된다.
  • SET XACT_ABORT OFF (기본값) — 오류가 난 문장만 취소되고 트랜잭션은 계속 진행된다. 첫 번째 INSERT는 살아남는다.

다만 OFF일 때의 동작은 오류의 심각도에 따라 달라진다. 심각도가 높은 오류는 OFF여도 배치 전체가 중단된다. 그래서 “무엇이 롤백되는지”를 예측 가능하게 만들려면 ON으로 두는 편이 낫다.

분산 트랜잭션에서는 SET XACT_ABORT ON이 사실상 필수다. 링크드 서버를 통해 데이터를 변경하면 MS DTC가 개입한 분산 트랜잭션이 되는데, 대부분의 OLE DB 공급자는 이 설정이 켜져 있어야 정상 동작한다.

면접하면서 배치쿼리를 사용해봤냐는 질문도 받았는데 이 내용도 관련되어 있던 내용이었다.

배치쿼리와 트랜잭션에 대해 앞으로 잘 알아두어야 할 것 같다.

트리거 예제의 문제점

위 예제 코드를 그대로 쓰면 안 된다. 참고용으로 옮겨온 코드지만 결함이 있다.

다중 행 UPDATE에서 깨진다. 트리거의 INSERTED/DELETED는 테이블이다. 한 번에 여러 행이 갱신되면 여러 행이 들어 있는데, SELECT @D_NO = NO FROM DELETED처럼 스칼라 변수에 담으면 그중 임의의 한 행만 처리되고 나머지는 조용히 누락된다. 집합 기반으로 써야 한다.

UPDATE  b
SET     b.NO = i.NO
FROM    [220.123.123.120].B서버DB명.dbo.B서버테이블명 AS b
JOIN    INSERTED AS i ON b.ID = i.ID

SET XACT_ABORT OFF로 되돌리는 것도 위험하다. SET XACT_ABORT는 트리거나 프로시저 범위가 아니라 연결(세션) 전체에 적용되는 옵션이라, 트리거 안에서 껐다고 해서 트리거가 끝날 때 자동으로 원래 값으로 복원되지 않는다. 그대로 두면 그 연결에서 실행되는 이후 모든 배치가 의도치 않게 OFF 상태로 남는다. 애초에 끌 필요 없이 켜둔 채로 두는 것이 안전하다.

또한 원본 코드의 DECLARE 줄에 붙은 --변경 전 / --변경 후 주석은 아래 SELECT 절의 주석과 서로 반대로 달려 있다. DELETED가 변경 전, INSERTED가 변경 후다.

2016년 4월 4일 추가

회사 동료 광연씨와 얘기해본 결과, 메시지큐나 웹서버 등의 미들웨어를 두고 여기에서 트랜잭션 처리를 하는게 더 좋지 않느냐는 결론에 이르렀다. 링크드서버로 연결된 DB를 이용해 트랜잭션을 이용하는 것은 적절하지 않은 해답일 것 같다.

지금이라면 이렇게 답하겠다

10년쯤 지나 다시 보니, 이 면접 질문의 요지는 “링크드 서버 트리거를 쓸 줄 아느냐”가 아니라 “물리적으로 분리된 저장소 사이의 원자성을 어떻게 보장할 것인가” 였다고 생각한다.

선택지는 크게 셋이다.

  1. 2PC(2단계 커밋) / 분산 트랜잭션 — MS DTC, XA가 여기에 해당한다. 링크드 서버 방식이 이 범주다. 진짜 원자성을 보장하지만, 코디네이터가 단일 장애점이 되고 커밋 지연 동안 락이 유지되어 처리량이 크게 떨어진다. 샤드가 많은 게임 DB에는 잘 맞지 않는다.
  2. 사가(Saga) 패턴 — 각 DB에서 로컬 트랜잭션으로 커밋하고, 실패하면 보상 트랜잭션으로 되돌린다. 원자성 대신 결과적 일관성을 받아들이는 방식이다. 대부분의 분산 시스템이 택하는 현실적인 답이다.
  3. 트랜잭셔널 아웃박스 — DB 변경과 “보낼 메시지”를 같은 로컬 트랜잭션에 함께 커밋하고, 별도 프로세스가 그 메시지를 읽어 다른 시스템에 전파한다. 메시지 유실 없이 사가를 구현하는 실무적인 방법이다.

당시 동료와 이야기하며 “미들웨어를 두자”고 결론 낸 것이 방향으로는 3번에 가까웠던 셈이다.

댓글 남기기

참고