長崎です。
Tech Ed に参加中です。
松本（み）さんのデットロックとスナップショット分離レベルのセッション中に考えたことをまとめました。
この内容は、1年前くらいに松本（た）さんと話していた内容です...（フィードバックしてない...）
非常に重要な問題なのではないか、と私は考えています。
-------------------------------------------------------------------------------------------------------
■サンプルテーブル、データ
--[col_A]がクラスタ化インデックスな主キーとなっている[tab_A]
CREATE TABLE [dbo].[tab_A](
[col_A] [nchar](1) COLLATE Japanese_CI_AS NOT NULL,
[col_B] [nchar](1) COLLATE Japanese_CI_AS NULL
CONSTRAINT [PK_tab_A] PRIMARY KEY CLUSTERED
(
[col_A] ASC
) ON [PRIMARY]
) ON [PRIMARY]
--サンプルデータを2行
INSERT INTO [dbo].[tab_A] ([col_A], [col_B]) VALUES ('1', '1') --セッションAで共有/更新/排他ロック
INSERT INTO [dbo].[tab_A] ([col_A], [col_B]) VALUES ('2', '2') --セッションBで共有/更新/排他ロック
-------------------------------------------------------------------------------------------------------
■前提知識(1)
SQL Server 2000 では、「排他ロック」がかかっている列を含んだクラスタ化インデックスを「クラスタ化インデックスのスキャン」すると
ロック開放待ちが発生する。
--セッションA：排他ロックのまま10秒WAIT
BEGIN TRAN
UPDATE [dbo].[tab_A]
SET [col_B] = [col_B]
WHERE [col_B] = '1'
WAITFOR DELAY '00:00:10'
COMMIT TRAN
--セッションB：デフォルトの分離レベルで、共有ロックのクラスタ化インデックスのスキャン
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM [dbo].[tab_A]
WHERE [col_B] = '2'
※「テーブルのスキャン」でも発生する。
※「クラスタ化インデックスのシーク」では発生しない。
-------------------------------------------------------------------------------------------------------
■前提知識(2)
SQL Server 2005 でも、上の状況は変わらない。
しかし、新しい"スナップショット"分離レベルを使用すると、ロック開放待ちを回避することができる。
--下準備
ALTER DATABASE <データベース名> SET ALLOW_SNAPSHOT_ISOLATION ON
--セッションA：排他ロックのまま10秒WAIT
上記と同じ。
--セッションB："スナップショット"分離レベルで、共有ロックのクラスタ化インデックスのスキャン
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM [dbo].[tab_A]
WHERE [col_B] = '2'
※ロック開放待ちを回避しているはず。
-------------------------------------------------------------------------------------------------------
■問題点
"スナップショット"分離レベルでロック開放待ちを回避できるのは以下の組み合わせだけである。
・セッションAが「排他ロック」で、セッションBが「共有ロックのテーブル／クラスタ化インデックスのスキャン」
以下の組み合わせではロック開放待ちを回避できていない。
・セッションAが「更新ロック」で、セッションBが「更新ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「更新ロック」で、セッションBが「排他ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「排他ロック」で、セッションBが「更新ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「排他ロック」で、セッションBが「排他ロックのテーブル／クラスタ化インデックスのスキャン」
ちなみに、以下はもともと問題ない。
・セッションAが「共有ロック」で、セッションBが「共有ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「共有ロック」で、セッションBが「更新ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「共有ロック」で、セッションBが「排他ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「更新ロック」で、セッションBが「共有ロックのテーブル／クラスタ化インデックスのスキャン」
（例）更新ロック×更新ロック
--セッションA：更新ロックのまま10秒WAIT
BEGIN TRAN
SELECT [col_B]
FROM [test].[dbo].[tab_B] WITH(UPDLOCK)
WHERE [col_B] = '1'
WAITFOR DELAY '00:00:10'
COMMIT TRAN
--セッションB："スナップショット"分離レベルで、更新ロックのクラスタ化インデックスのスキャン
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT [col_A]
,[col_B]
FROM [test].[dbo].[tab_B] WITH(UPDLOCK)
WHERE [col_B] = '2'
-------------------------------------------------------------------------------------------------------
■理由
"スナップショット"側ではロックを管理しない。ロックは"本物"側で一元管理しなければならないからである。
よって、
"スナップショット"分離レベルのセッションが共有ロックの場合は期待通りに動作する（"本物"側のロック状況を無視する）が、
"スナップショット"分離レベルのセッションが更新ロックや排他ロックの場合、"本物"側をロックする必要があるらしい。
結局、"スナップショット"としての実装では上記の問題は解決しない。
-------------------------------------------------------------------------------------------------------
■提言（または妄想）
スキャンの際に、いきなり更新ロックや排他ロックをかけようとしないで、まずスキャン内部でデータの状況を参照してから
スキップするかウェイトするか決めればいいのでは？
ちなみに最新バージョンのDB2では、ロック開放待ちの回避策として
「スキャンにおいて、未コミットのINSERT行をスキップする」
「スキャンにおいて、未コミットのDELETE行をスキップする」
「スキャンにおいて、未コミットのINSERT行／UPDATE行／DELETE行がロック対象になる可能性を吟味して、スキップするかウェイトするか決める」
というようなオプションがあるそうです。（DB2 & RedBrick Day 2005 にて）
こんなオプションが考えられますね。
「更新ロックでのスキャンにおいて、排他ロック状態のINSERT行をスキップする」Yes or No
「更新ロックでのスキャンにおいて、排他ロック状態のDELETE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「更新ロックでのスキャンにおいて、更新ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「更新ロックでのスキャンにおいて、排他ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「排他ロックでのスキャンにおいて、排他ロック状態のINSERT行をスキップする」Yes or No
「排他ロックでのスキャンにおいて、排他ロック状態のDELETE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「排他ロックでのスキャンにおいて、更新ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「排他ロックでのスキャンにおいて、排他ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
-------------------------------------------------------------------------------------------------------
■実装時の注意点
で、現実的には...以下の点を注意しましょう。
・"スナップショット"分離レベルのセッションは共有ロックに限定するように実装しましょう。
INSERT／UPDATE／DELETE処理を行わないようにしましょう。更新ロックも避ましょう。
・更新ロックでのテーブル／クラスタ化インデックスのスキャンが発生しないように設計しましょう。
→更新ロックかけてあとで更新するような長いトランザクションの対象テーブルは、非クラスタなインデックスを作りましょう。（って物理依存...）
・排他ロックでのテーブル／クラスタ化インデックスのスキャンが発生しないように設計しましょう。
→同一テーブルの多くの行を更新するクエリがある場合は、非クラスタなインデックスを作りましょう。
→同一テーブルの多くの行を更新するクエリは分割しましょう。
っていう感じですかね。
-------------------------------------------------------------------------------------------------------
以上です。