서로 다른 DB 머신에서 데이터 트랜잭션 처리

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

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

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

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

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

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

출처 : 네이버지식인 (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

결론적으로 문제의 해답은 변경할 테이블에 트리거 처리를 한다. 이고 그것을 위해 XACT_ABORT_ONXACT_ABORT_OFF 두개의 명령어를 사용해야한다는 것이다.

그렇다면 방금 처음 본 XACT_ABORT_ON/OFF가 도대체 뭔지 찾아보았다. 이에 대한 내용을 가장 잘 설명해준 글이 있었다.

http://fbshot.blog.me/80162695844

가장 중요한 차이점은 다음과 같다.

만약 프로시져 안에 INSERT 쿼리가 두개가 있는 상태에서 두번째 INSERT 쿼리에서 오류가 난다면,

  • XACT_ABORT_ON을 해놓으면 트랜잭션 안에 묶인 여러 쿼리 중 하나만 실패해도 전부다 실패처리하고 롤백한다.
  • XACT_ABORT_OFF일 때에는 첫번째 INSERT는 적용되고 두번째 INSERT 쿼리만 롤백한다.

이것을 이용해 배치쿼리에서 트랜잭션 처리에 대한 문제를 해결할 수 있다.

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

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

 

2016년 4월 4일 추가.

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

MSSQL 로그 파일 용량 줄이는 방법

개발용 MSSQL 서버를 사용하면서 로그 파일 용량이 점점 늘어갔다. 평소에는 별 상관 없는데 데이터베이스를 복사 떠가려고 하거나 백업할 때마다 엄청나게 많은 시간이 소요되니… 로그 파일이 무려 3기가가 넘었다. 그래서 로그 파일 용량을 줄였다. 데이터베이스 속성을 보면 다음과 같이 크기가 엄청나게 크다.

다음의 코드를 입력한다.

USE GameDB
SELECT DATABASEPROPERTYEX('GameDB', 'Recovery');
ALTER DATABASE GameDB SET RECOVERY SIMPLE
DBCC SHRINKFILE (GameDB_New_log, 10)
ALTER DATABASE GameDB SET RECOVERY FULL

쿼리 실행시 해당하는 데이터베이스에 가서 실행해야 하므로 USE를 한번 써준다. GameDB는 사용하는 데이터베이스 이름, GameDB_New_log는 로그 파일의 논리적 이름을 써주면 된다.

쿼리를 실행하면 몇초 걸리지 않고 굉장히 빨리 처리된다.

다시 데이터베이스 속성을 열어보면 다음과 같이 로그 파일 크기가 줄어있다.

WITH(UPDLOCK) 힌트와 MSSQL에서 락 해제하는 방법

MSSQL에서 의도치 않게 락이 걸리는 상황이 발생했다. 내가 만든게 아니니 뭐…

에러가 나는 프로시져를 찾아보니 UPDATE 쿼리에서 WITH(UPDLOCK) 구분이 있었다.

쭈기님의 블로그에 있는 글 (http://jjugi0606.tistory.com/30)에 의하면 이 힌트를 쓰는 경우, WHERE 검색에 들어가는 컬럼들은 인덱스 설정이 되어야만 한다고 한다. 그래서 인덱스를 일단 설정. 앞으로 또 락이 걸리는지는 두고봐야 알 수 있을 것 같다.

어찌됐던 MSSQL에서 걸려있는 락을 해제하기 위한 방법도 찾아보았다. 이 글 (http://yongandju.tistory.com/74)에서 방법을 찾았다.

EXEC sp_lock

명령어를 통해 락이 걸린 부분을 찾는다. (Mode 부분이 X 표시가 된 것이 락이 걸린 것이다.)

dbcc inputbuffer(해당 spid)

를 입력해서 어떤 쿼리에서 락이 걸렸는지 확인한다.

kill 해당spid

를 입력해서 강제종료시킨다.

어찌됐던 위 과정을 통해서 락은 해제되었다.