Known Issue: SSIS パッケージの TransactionOption オプションを Required とすると、パッケージの動作が停止しているように見える。

小林 真治
SQL Developer Support Escalation Engineer

 今回は、SQL Server Integration Services (SQL2005/2008/2008R2/2012) において、パッケージの TransactionLevel を required とした際に、パッケージの動作が停止しているように見える現象について解説します。

 問題

 下記の構成の SQL Server Integration Services (以下 SSIS) のパッケージを作成し、実行すると SSIS パッケージの動作が停止しているように見える。

 - SQL Server 上のテーブルを転送先としている。

- SQL 実行タスクで転送先のテーブルデータを truncate クエリを利用して削除する。

- データフロータスクにて、上記クエリでデータを削除したテーブルにデータを転送する。

- SQL 実行タスク >>> データフロータスクのフローで結ばれている。

- SSIS パッケージの TransactionOption オプションとして Required を選択している。

 原因

 上記構成の SSIS パッケージを実行すると、 Truncate クエリにより後続の処理がブロッキングされます。この結果、SSIS パッケージの動作が停止しているように見えます。

 詳細

まず、SQL 実行タスク - データフロータスクのフローで SSIS パッケージを実行した際の内部的について説明します。

該当フローを実行した場合、内部的には下記の処理が実施されます。また、この処理を実施するために、対象の SQL Server は必要なロックを獲得します。

a). SQL 実行タスクのために、 Truncate クエリが転送先テーブルに対して実行される（この時、SQL Server 上の該当テーブルは スキーマ修正共有ロック（LCK_M_SCH_S） というロックが保持されます）。

b). データフロータスクの実行のために、転送元、転送先のテーブルのスキーマ情報が取得される（この時、SQL Server 上の該当テーブルのスキーマを取得するためにスキーマ情報へのロックを獲得します）。

c). データフロータスク内で定義されたテーブルに対して Insert Bulk クエリによるデータ転送が実行される。

次に、SSIS パッケージの TransactionOption を Required にした際の動作について説明します。

SSIS パッケージの TransactionOption を Required にした場合、 SSIS は、分散トランザクションを利用して、パッケージのトランザクションの一貫性を管理します。
このため、SSIS パッケージから SQL Server に対して実行されるクエリは、分散トランザクション コーディネータ(MSDTC) を介して実行される動作となります。

このような前提があった上で、上記フローを実行する SSIS パッケージの TransactionOption を Required とすると、パッケージの処理が終了するまで、 MSDTC によって、上記 a). の転送先テーブルのスキーマ共有ロックが保持され続ける状態となり、後続のスキーマ情報を取得するためのロック獲得の処理 b). が、ブロックされます。
後続のスキーマ情報を取得するためには、 a). の Truncate クエリがコミットされることが条件となりますが、このクエリはパッケージの終了までコミットされませんので、結果として SSIS パッケージは動作を停止しているかのような状態となります。

対処策

この現象に回避するために下記の対処を検討します。

1). Truncate クエリではなく DELETE クエリを利用する。
------------------------------------------------------------------------------------------------------------

DELETE クエリは、該当テーブルに対するスキーマロックを獲得しません。このため、後続のスキーマ情報を取得する b) の処理が、ブロックされることを回避できます。

2). SSIS パッケージの TransactionOption を supported とする。
------------------------------------------------------------------------------------------------------------
SSIS パッケージの TransactionOption を supported とすることで、 Truncate クエリの実行完了時にトランザクションがコミットされますので、その時点でロックは解放され、後続の処理をブロックすることを防げます。