トリガーをスナップショット・アイソレーションと一緒に使うときは要注意

Kalen Delaney

2007.04.26

　トリガーは，古くからあるSQL Serverの機能だ。だが，MicrosoftはSQL Server 2005でトリガーの再設計を行ったため，内部の細かい仕様は以前とはかなり異なるものになっている。

　前回の記事「行レベルのバージョン管理の隠れたコスト」で学習したように，SQL Server 2005においてトリガーは，「行レベルのバージョン管理（RLV）」と呼ばれる新しい技術を使っている。トリガー・コードの内側では，以前と同様に「inserted」や「deleted」と呼ばれる2種類の仮のテーブルにアクセスできる。実を言うと，これら仮のテーブルで参照できる変更済みデータの旧バージョンと新バージョンは，SQL Server 2005ではRLVを使って管理されているのだ。

　筆者は前回の記事で，バージョン・ストアが筆者のトリガーによって処理された列を含んでいることを説明するために，スナップショット・アイソレーションが有効になっていないデータベースにおけるトリガーの例を紹介した。データベース・アドミニストレータ（DBA）は，「たとえスナップショット・アイソレーションを使わない場合でも，tempdbでバージョン・ストアを管理する必要がある」という点を，筆者は強調した。

　今回も引き続きトリガーがバージョン・ストアに与える影響を考察していこう。特に，スナップショット・アイソレーションが有効になっているデータベースを見ていく。なおここでは，AFTERトリガーのみを取り上げる。SQL Server 2005は，INSTEAD OFトリガーに渡されるinsertedテーブルとdeletedテーブルの処理にバージョン・ストアを使わないからだ。

トリガーとバージョン・ストア

　バージョン・ストア内の行数を調べるには，以下のシンプルなクエリーを実行すればいい。

SELECT count(*) AS NumRows FROM sys.dm_tran_version_store

] 　リスト1のコードを実行して，筆者が前回使ったDepartmentテーブルをもう一度作成しよう（スペースの都合上，本記事に掲載されているコードのいくつかの行はワードラップ処理されている）。今回，このスクリプトはテーブル上にいかなるトリガーも作成しないが，スナップショット・アイソレーションを有効にするという働きがある。

リスト1●Departmentテーブルを作ってスナップショット・アイソレーションを可能にする

-- Turn off the snapshot options to start with
ALTER DATABASE AdventureWorks
  SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE AdventureWorks
  SET READ_COMMITTED_SNAPSHOT OFF
GO
USE AdventureWorks
GO
-- Make a copy of the Department table (15 rows)
IF  OBJECTPROPERTY(object_id('Department'),
  'IsTable') = 1
  DROP TABLE Department
GO
SELECT * 
INTO Department 
FROM  HumanResources.Department
GO
ALTER DATABASE AdventureWorks
  SET ALLOW_SNAPSHOT_ISOLATION ON
GO

　続いて以下のUPDATEを実行して，バージョン・ストア内の行数を調べよう。

UPDATE Department
SET ModifiedDate =
  getdate()
WHERE DepartmentID = 11;

] 　前回，UPDATEトリガーを持ち，スナップショット・アイソレーションを持たないデータベース内でこのUPDATEステートメントを実行したときは，バージョン・ストア内に二つの行が生成された。だが今回は，一つの行しかないはずだ。格納する必要があるのは，「department 11」に対応する行の前のバージョンだけだからだ。スナップショット・アイソレーションを通してこのテーブルにクエリーを出している他の接続は，そのバージョンの行を使うことができる。

　前回の記事中にあるDELETEステートメントを実行し，さらにバージョン・ストアに行を追加する他の何らかのステートメントを実行してから1分以上が経過している場合，バージョン・ストア内には一つの行があるはずだ。

トリガーとスナップショット・アイソレーション

　それではトリガーを作成して，トリガーとスナップショット・アイソレーションの両方を使ったときに何が起きるのか見てみよう。リスト2のコードを実行して，前回と同様に，バージョン・ストアのサイズのみをレポートするトリガーを作成しよう。トリガー作成後，リスト3にあるUPDATEとDELETEのステートメントを実行しよう。

リスト2●二つのトリガーを生成する

CREATE TRIGGER upd_Department
ON Department 
FOR UPDATE 
AS
SELECT count(*) AS NumRows 
FROM  sys.dm_tran_version_store
GO
CREATE TRIGGER del_Department
ON Department 
FOR DELETE 
AS
SELECT count(*) AS NumRows 
FROM  sys.dm_tran_version_store
GO

リスト3●トリガーとスナップショット・アイソレーションの両方を使用するUPDATEとDELETE

UPDATE Department
SET ModifiedDate = getdate()
WHERE DepartmentID = 7;
DELETE Department
WHERE DepartmentID = 11

　行の数が累計でないことに，注意しよう。トリガーだけを使って，スナップショット・アイソレーションを使わなかったとき，バージョン・ストア内のUPDATEに対応する行は二つだった。そしてスナップショット・アイソレーションだけを使って，トリガーを使わずにUPDATEを実行した後だと，行の数は一つだった。だがトリガーとスナップショット・アイソレーションの両方を使用しても，行の数は二つだけなのである。

　DELETEの場合，バージョン・ストア内に追加で必要な行は一つだけだ。SQL Serverは，バージョン・ストア内にあるトリガー用の行とスナップショット・アイソレーション用の行を別々の目的に使用するが，情報の複製は行わない。UPDATEトリガーとスナップショット・トランザクションはどちらも，「department 7」行の前のバージョンに対応するバージョン・ストア行を一つ使うことができる。同様に， DELETEを実行するときに必要なバージョン管理された行は，一つだけだ。スナップショット・トランザクションとDELETEトリガーの両方が，この行を使うことができるからだ。