기본 강좌 46 - 트랜잭션 처리

트랜잭션테스트.dtsx

트랜잭션

 

SQL Server에서와 같이 SSIS에서도 데이터베이스 작업에 대해 트랜잭션을 지원합니다. 하나의 트랜잭션으로 설정된 여러 작업들이 있을 때, 모든 작업이 성공할 때에만 커밋되고 그렇지 않을 때에는 전체 작업이 다 롤백 되도록 설정할 수 있습니다. 이는 패키지 전체 또는 트랜잭션이 필요한 작업 영역에 대해 데이터 무결성을 유지 관리할 수 있는 기능입니다.

 

트랜잭션의 속성은 각 작업 개체 또는 컨테이너, 패키지의 속성 중 TransactionOption 에서 설정할 수 있습니다.

 

 

 

[참고] 트랜잭션을 사용하기 위한 설정 – DTC(Distributed Transaction Coordinator) 실행   SSIS에서 트랜잭션을 사용하기 위해서는 패키지가 수행되는 서버 또는 PC에 MSDTC 서비스가 실행되고 있어야 합니다. DTC 서비스가 실행되고 있지 않은 상태에서 트랜잭션 설정을 적용한 패키지를 수행하면 진행률 부분에서 다음과 같은 메시지가 나타납니다.     DTC 서비스는 [제어판] à [관리도구] à [서비스] 내에서 Distributed Transaction Coordinator 서비스를 시작하면 실행됩니다.

 

 

SSIS의 모든 작업 개체 및 컨테이너의 속성에서 TransactionOption을 설정할 수는 있지만, 트랜잭션 기능은 데이터베이스와 관련된 작업에 대해서만 적용됩니다. 예를 들어 아래 그림과 같이 작업 1과 작업 3은 SQL 실행 작업이며, 작업 2는 파일 복사를 수행하는 파일 시스템 작업일 때, 작업 3에서 실패가 나서 해당 컨테이너가 커밋을 하지 못할 경우 작업 1인 SQL 작업은 롤백이 일어나서 데이터 처리가 되지 않지만, 작업 2인 파일 복사 작업은 롤백이 되지 않습니다. 즉, 이미 복사가 이루어진 파일이 다시 삭제되지는 않습니다.

 

  (작업 1, 작업 2, 작업 3  | TransactionOption : Supported)

 

하지만, 데이터베이스와 관련된 작업이 아닌 경우에도 트랜잭션이 필요한 이유는 다음과 같습니다.

 

작업 1과 작업 2는 데이터베이스와 관련된 작업이며, 작업 3은 파일을 복사하는 작업입니다. 세 개의 작업이 트랜잭션을 생성시키는 하나의 컨테이너 내에서 수행된다고 할 때, 데이터베이스 작업이 아닌 작업 3에서 실패가 나더라도 작업 1과 작업 2는 롤백이 됩니다.

 

(작업 1, 작업 2, 작업 3  | TransactionOption : Supported)

 

이는 작업 3이 작업 1이나 작업 2와 같이 트랜잭션의 처리 결과에 대해 커밋이나 롤백을 수행할 대상은 아니지만, 트랜잭션에 영향을 미칠 수 있는 개체로 설정되어 있기 때문입니다.

 

 

트랜잭션에서 설정할 수 있는 옵션은 다음과 같습니다.

 

Ÿ           Required – 현재 개체가 트랜잭션을 생성합니다. 여기서의 개체는 패키지 또는 컨테이너, 개별 작업을 말합니다. 만약 이 개체가 부모 컨테이너에 포함되어 있으며, 부모 컨테이너에서 트랜잭션이 생성되도록 설정되어 있는 경우에는 Supported 설정과 같이 부모의 트랜잭션에 참여합니다.
 패키지는 트랜잭션을 생성하지 않는 Not Required로 설정되어 있더라도, 패키지 내에 있는 시퀀스 컨테이너의 트랜잭션 속성이 Required로 설정된 경우, 시퀀스 컨테이너는 트랜잭션을 생성하며, 이 컨테이너 내에 포함되는 작업들(Not Supported로 설정된 작업 제외)에 대해 트랜잭션을 적용합니다.

Ÿ           Supported – 현재 개체가 트랜잭션을 생성하지는 않고 단지 부모 컨테이너의 트랜잭션에 참여만 합니다. 예를 들어, 패키지는 트랜잭션을 생성하는 Required로 설정이 되어 있으며, 패키지 내에 포함된 3개의 SQL 작업들의 트랜잭션 옵션은 Supported로 설정되어 있는 경우, 각각의 SQL 작업은 부모 컨테이너인 패키지의 트랜잭션에 참여하게 되며, 세 개의 작업 중 하나라도 실패하게 되면 전체 작업이 롤백 됩니다.

Ÿ           Not Required – 현재 개체가 새로운 트랜잭션을 생성하지도 않으며, 기존 트랜잭션에 참여하지도 않습니다.

 

 

다양한 경우를 예로 들어 설명하겠습니다.

 

우선 예제 설명을 위해 다음과 같은 예제 패키지를 구성합니다.

 

1.       새로운 패키지 파일을 하나 추가한 후, 임의의 데이터 베이스 연결을 설정합니다. 본 예제에서는 localhost의 tempdb를 사용하도록 하겠습니다.

 

2.       1에서 설정한 데이터베이스에 다음과 같은 형태의 간단한 테이블을 추가합니다.

CREATE TABLE TestTable (              SEQ INT )

 

3.       제어 흐름 영역에 시퀀스 컨테이너를 하나 추가한 후, 시퀀스 컨테이너 내에 SQL 실행 작업 세 개를 추가한 후, 세 개의 작업을 연결합니다. 또한 시퀀스 컨테이너 밖에 SQL 실행 작업을 하나 더 추가합니다. 이 작업은 시퀀스 컨테이너와 연결시키지는 않습니다.

4.       각 SQL 작업의 속성에서 Connection을 1에서 설정한 연결로 지정해 주고, SQLStatement에 다음과 같은 형태의 쿼리를 입력합니다.

       작업 1 : INSERT TestTable VALUES(1)
       작업 2 : INSERT TestTable VALUES(2)
       작업 3 : INSERT TestTable VALUES(‘c’)
       작업 4 : INSERT TestTable VALUES(4)

다음과 같은 형태의 패키지가 완성되었습니다. 작업 3에서는 int 형의 열에 ‘c’라는 문자형 데이터를 입력하기 때문에 에러가 발생됩니다.

 

 

 

 

CASE 1) 트랜잭션 발생 없음

 

개체	TransactionOption
Package	Supported
시퀀스 컨테이너	Supported
작업 1	Supported
작업 2	Supported
작업 3	Supported
작업 4	Supported

 

 

 

최상위 컨테이너인 패키지 및 시퀀스 컨테이너 모두 Supported로만 설정되어 있기 때문에 어떠한 트랜잭션도 생성되지 않습니다. 따라서, 작업 1과 작업 2, 작업 4가 트랜잭션에 참여 없이 정상적으로 커밋됩니다.

 

 

 

CASE 2) 상위 레벨에서 트랜잭션 발생

 

개체	TransactionOption
Package	Required
시퀀스 컨테이너	Supported
작업 1	Supported
작업 2	Supported
작업 3	Supported
작업 4	Supported

 

 

 

최상위 컨테이너인 패키지의 설정이 Required이기 때문에 패키지 수준의 트랜잭션을 발생시켰으며, 패키지에 포함된 컨테이너는 Supported로 설정되어 있기 때문에 트랜잭션에 참여합니다. 또한 작업 1, 2, 3, 4도 마찬가지로 이 트랜잭션에 참여하게 됩니다. 따라서 작업 3이 실패가 나면, 해당 트랜잭션이 롤백되고 이 트랜잭션에 포함되어 컨터이너 내의 작업 1, 작업 2뿐만 아니라, 패키지에 포함된 작업 4도 커밋하지 못하고 롤백하게 됩니다.

 

 

 

CASE 3) 상위 레벨에서 트랜잭션 발생하지만, 컨테이너에서 트랜잭션 미 지원

 

개체	TransactionOption
Package	Required
시퀀스 컨테이너	Not Required
작업 1	Supported
작업 2	Supported
작업 3	Supported
작업 4	Supported

 

 

 

상위 수준인 패키지에서는 트랜잭션을 발생시키지만, 컨테이너에서는 트랜잭션을 지원하지 않도록 설정한 상태입니다. 즉, 상위 레벨에서 트랜잭션이 생성되었더라도 컨테이너 내부의 개체인 작업 1, 2, 3은 참여를 하지 않습니다. 따라서 작업 1과 작업 2는 수행된 후, 바로 커밋됩니다. 하지만, 작업 3의 실패로 인해 트랜잭션 테스트 컨테이너의 최종 수행 결과는 실패로 처리가 되며, 이로 인해 패키지 수준에서 생성된 트랜잭션은 자식 개체인 시퀀스 컨테이너의 수행 결과가 실패이기 때문에 트랜잭션을 실패로 처리하게 되어 작업 4는 롤백됩니다.

 

 

 

CASE 4) 컨테이너에서 트랜잭션 생성

개체	TransactionOption
Package	Supported
시퀀스 컨테이너	Required
작업 1	Supported
작업 2	Supported
작업 3	Supported
작업 4	Supported

 

 

패키지 수준에서는 Supported로 설정되어 있기 때문에 트랜잭션을 발생시키지는 않습니다. 시퀀스 컨테이너의 설정이 Required이기 때문에 컨테이너에서 트랜잭션을 발생시키고, 해당 컨테이너에 포함되어 있는 작업 1, 2, 3은 모두 트랜잭션에 참여하게 됩니다. 따라서 작업 3의 실패로 인하여 시퀀스 컨테이너 수준의 트랜잭션은 실패가 되어 작업 1과 작업 2는 롤백합니다. 작업 4는 트랜잭션에 참여하지 않았기 때문에 바로 커밋되었습니다.