// Net planet-es V2

T-SQL でトランザクションを扱う方法を掲載します。構造化された例外処理の中でトランザクションを使用する方法は[T-SQL] T-SQLで例外処理 を参照してください。

動作か確認は SQL Server 2005 Developer Edition で行っています。

1. 暗黙のトランザクションモード

通常使用されることはないことですが、明示的トランザクションと場別に暗黙のトランザクションを使用することもできます。暗黙のトランザクションモードを使用する場合は、SQL 文実行中に IMPLICIT_TRANSACTRIONS オプションを指定します。サンプルでは有効、無効にする方法を記載しています。SET IMPLICIT_TRANSACTIONS が ON の場合は、接続は暗黙のトランザクション モードに設定されます。OFF の場合、接続は自動コミット トランザクション モードに戻ります。

-- 暗黙のトランザクションモード
-- 有効
SET IMPLICIT_TRANSACTIONS ON
-- 無効
SET IMPLICIT_TRANSACTIONS OFF

接続が暗黙のトランザクション モードで、トランザクションにが開始されていない場合は、次のいずれかのステートメントを実行するとトランザクションが開始されます。

暗黙なトランザクションが開始されている場合は、コミットまたはロールバックを行うまで変更の確定、取り消しが確定しません。コミットせずに接続が切れた場合はロールバックされます。

2. 明示的なトランザクション

明示的なトランザクションはT-SQL内にトランザクションの開始を記述します。明示的なトランザクションに使用するステートメントや関数は次のようなものです。BEGIN TRANSACTION などのステートメントはBEGIN TRAN と記述することもできます。ステートメントの詳細は説明はBooks Online を参照してください。

使用するサンプルを掲載します。ArticleID 列がIDENTITY なテーブルに値をINSERTしてエラーを起こさせ、ロールバックを行うようにしています。構造な例外処理は行っていません。その場合は[T-SQL] T-SQLで例外処理を参照してください。

BEGIN TRAN

DECLARE @Err int
INSERT dbo.Article(Title, Body)
            VALUES(N'title', N'Body')
INSERT dbo.Article(ArticleID, Title)
            VALUES(1, N'test')

SET @Err = @@Error
PRINT @Err
If(@Err <> 0)  GOTO Error

COMMIT TRAN

GOTO End_Sample

Error:
  ROLLBACK TRAN

End_Sample:

3. そのほか便利な情報

3.1 トランザクション分離レベルを設定する

SET TRANSACTION ISOLATION LEVEコマンドを使用するとトランザクションの分離レベルを変更できます。分離レベルはREAD UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE を指定できます。分離レベルによるうロックモードの違いの詳細はBooks Online を参照してください。

-- SET TRANSAQCTION ISOLATION LEVEL を使用する
-- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ
-- SNAPSHOT, SERIALIZABLE を指定できる
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SNAPSHOT 分離レベルを使用するトランザクションを開始する前には、ALLOW_SNAPSHOT_ISOLATION データベース オプションをON に設定する必要があります。

3.2 ロック時間を設定する

SET LOCK_TIMEOUTを使用するとロックの待機時間を設定できます-1の場合は無期限にロックの待機を行います。

SET LOCK_TIMEOUT 100
SELECT @@LOCK_TIMEOUT

3.3 ロック状態を確認する

sys.dm_tran_locks 動的管理ビューを使用すると、現在アクティブなロック マネージャのリソースに関する情報を取得できます。サンプルでは、テーブルロックを取得した後、sys.dm_tran_locks 動的管理ビューを検索してロックされているテーブルとデータベースの情報とロックモードを選択しています。

BEGIN TRAN

SELECT TOP 1 *
  FROM dbo.Article
 WITH (TABLOCKX)

select resource_type
     , resource_subtype
     , resource_database_id
     , DB_NAME(resource_database_id) resouce_database_name
     , resource_associated_entity_id
     , OBJECT_NAME(resource_associated_entity_id) associated_entity_name
     , request_mode
     , request_type
     , request_status
     , request_reference_count
     , request_session_id
     , request_owner_type
  from sys.dm_tran_locks

ROLLBACK TRAN

3.4 待機が発生しているプロセスを調べる、プロセスを停止する

ロックによる待機が発生するプロセスを検索するには、 sys.dm_os_waiting_tasks 動的管理ビューを使用します。動的管理ビューを使用すると、リソースを待機しているタスクの待機キューに関する情報を取得できます。サンプルでは、そのほか、DBCC INPUTBUFFER を使用して、接続しているクライアントから最後に送信されたステートメントを表示し、KILL ステートメントでプロセスを削除しています。KILL は待機が発生して対処ができなくなった場合に、最後の手段として使用するようにしてください。

-- リソースを待機しているタスクの待機キューに関する情報を返します。 
select session_id, blocking_session_id, wait_duration_ms, wait_type
 from sys.dm_os_waiting_tasks

-- クライアントから Microsoft SQL Server 2005 インスタンスに最後に送信されたステートメントを表示します。
DBCC INPUTBUFFER( 2)
-- プロセスを終了する
KILL 2

4. まとめ

今回の説明は以上です。業務アプリを作成しているトランザクションをT-SQL から開始することはあまりないかもしれませんが、Management Studio 上でSQL の確認をする場合など知っていると便利だと思います。

間違い、指摘点などがありましたらご連絡ください。