SSIS의 트랜잭션에 대해서

SSIS의 트랜잭션에 대해서

 

 

백도훈

에이디컨설팅 선임 컨설턴트

 

 

며칠 전 이러한 질문을 듣게 됐습니다.

“하나의 트랜잭션 안에 여러 쿼리가 있는데 그 중 어떤 쿼리에 대해서는 트랜잭션에 참가시키고 싶지 않습니다. 어떻게 하면 될까요?”

 

위의 질문을 쿼리로 직접 예를 들어보면,

-- 테스트 DB를 생성합니다. CREATE DATABASE TESTDB2 GO USE TESTDB2 GO   -- 테스트 테이블을 생성합니다. CREATE TABLE TABLEA (COL1 INT) GO CREATE TABLE TABLEB (COL1 INT) GO CREATE TABLE TABLEC (COL1 INT) GO   -- 하나의 트랜잭션안에 4개의 쿼리가 있습니다. -- 마지막 쿼리는 반드시 에러가 발생합니다. -- 따라서 아래의 쿼리는 ROLLBACK됩니다. BEGIN TRAN INSERT TABLEA VALUES (1) INSERT TABLEB VALUES (2) INSERT TABLEC VALUES (3) SELECT 1/0   IF @@ERROR <> 0              ROLLBACK TRAN ELSE              COMMIT TRAN   -- 입력확인 SELECT * FROM TABLEA SELECT * FROM TABLEB SELECT * FROM TABLEC

 

SELECT 1/0 쿼리 때문에 한 건도 입력되지 않습니다. 이게 일반적으로 사용하는 트랜잭션의 모습인데요.
질문하신 분께서 원하신 것은 두 번째 INSERT 문은 트랜잭션에 참여시키고 싶지 않은 것입니다. 항상 2가 입력되기를 원하시는 것입니다. 그러던 중 태스크 단위로 실행되는 SSIS로 구현한다면 가능할 것 같다는 생각이 들었습니다.

 

지금까지 글을 쓰게 된 동기에 대해서 설명했습니다. -_-;;

짧게 나마 SSIS의 트랜잭션에 대해서 이야기해 보고자 합니다.

 

 

 

- SSIS에서 트랜잭션을 사용하려면

우선 SSIS에서 트랜잭션을 사용하려면 MS DTC 서비스가 실행 중이어야 합니다.

 

서비스를 실행시키지 않고 트랜잭션을 이용하려고 하면 다음과 같은 에러를 만나게 됩니다.

오류: 오류 0x8004D01B "트랜잭션 관리자를 사용할 수 없습니다."(으)로 인해 SSIS 런타임이 분산 트랜잭션을 시작하지 못했습니다. DTS 트랜잭션을 시작하지 못했습니다. 이 오류는 MSDTC 서비스가 실행되지 않는 경우에 발생할 수 있습니다.

 

 

 

- 패키지에서 트랜잭션을 사용하려면

SSIS의 트랜잭션은 패키지와 제어 흐름 요소 단위로 참여시킬 수 있습니다. 패키지와 제어 흐름 요소의 속성을 살펴보면 트랜잭션 그룹이 있습니다. TransactionOpion을 어떻게 주느냐에 따라 참여 여부를 결정할 수 있습니다.

 

-          NotSupported는 이름에서도 느껴지다시피 트랜잭션에 참여시키지 않는 값입니다.

-          Supported(기본값)는 부모 개체의 TransactionOpion을 따르겠다는 값입니다. 예를 들어 트랜잭션에 참여를 하는 Foreach 루프 컨테이너가 있습니다. 컨테이너 안에 SQL 실행 작업 태스크가 있고 TransactionOpion 속성의 값이 Supported였다면 Foreach 루프 컨테이너가 트랜잭션에 참여한다고 했기 때문에 SQL 실행 작업 태스크도 트랜잭션에 참여하게 됩니다.

-          Required는 내가 트랜잭션을 생성하겠다는 값입니다. 트랜잭션의 주체가 되겠다는 의미가 되겠습니다.

 

 

 

실제로 위에 있는 쿼리를 SSIS로 구현했습니다.

 

- 패키지, 시퀀스 컨테이너, SQL 실행 작업 모두 TransactionOpion 속성을 Supported로 설정했을 경우

테이블 3개 모두에 각각 1, 2, 3의 값이 입력됩니다. 그 어떤 요소도 트랜잭션의 주체가 되지 않았기 때문에 마지막에 에러가 발생해도 그 전에 성공된 작업은 DB에 반영이 된 것입니다.

 

- 시퀀스 컨테이너의 TransactionOpion 속성을 Required로 설정했을 경우

테이블 3개에 모두 ROLLBACK이 발생해서 아무 데이터도 입력되지 않습니다. 시퀀스 컨테이너 내의 모든 태스크 들이 Supported로 설정되어 있기 때문에 자동적으로 시퀀스 컨테이너가 만든 트랜잭션에 참여합니다. ERROR 태스크가 실패 되면서 부모인 시퀀스 컨테이너도 실패되고, 이 컨테이너가 생성한 트랜잭션이 ROLLBACK 되기 때문에 TABLE A, B, C 작업은 모두 ROLLBACK 됩니다.

 

- 시퀀스 컨테이너의 TransactionOpion 속성을 Required로 TABLE B 태스크를 NotSupported로 설정했을 경우

TABLE B 태스크는 부모 컨테이너의 트랜잭션에 참여하지 않기 때문에 부모의 트랜잭션이 ROLLBACK이 되더라도 데이터 입력작업(값: 2)을 ROLLBACK하지 않습니다.

 

 

 

저장 프로시저와 SSIS로 트랜잭션을 처리하는 방법을 비교해봤을 때, 저장 프로시저에서는 DBMS에서 트랜잭션을 관리하지만 SSIS는 MS DTC를 이용하게 됩니다. 결국 저장 프로시저에서 BEGIN TRAN 대신에 BEGIN DISTRIBUTED TRAN으로 트랜잭션을 설정하는 것이기에 처리 비용이 더 커질 수도 있습니다. 하지만 SSIS는 쿼리뿐만 아니라 어플리케이션 실행이나 파일 시스템 작업 등과 같은 DB 이외의 기능에 대해서도 트랜잭션을 관리해야 되며, 이 기종 DB 간에도 트랜잭션을 관리해야 하기 때문에 MS DTC를 쓰는 것은 당연한 것이라고 볼 수 있습니다.