bxdxmx3

きじれてじろあ なきがせすで あぷせとねでぶ

SQL SERVERにおけるデッドロック(内部仕様)〜クラスタ化インデックスと非クラスタ化インデックス間のデッドロック〜

ググってみてもそれらしい情報が無かったので書いてみる。
原因不明のデッドロックで悩んでる人を救えるかもしれないという期待もあり。

テスト環境

SQL SERVER 2008 Express SP1

テスト内容

概要

あるテーブルを更新する処理と参照する処理でデッドロックが発生することを確認する。

詳細

A.クラスタ化インデックスを使用して更新項目を特定し、
 非クラスタ化インデックスキー項目のカラムを更新する。
B.非クラスタ化インデックスを使用して参照項目を特定し、
 該当項目を参照する。

AとBの処理が同時に動いた場合にデッドロックになることを確認する。

テーブル構成

    • id:int
    • Status:int
    • Memo:char(256)
  • インデックス
  • 内容
    • 適当なデータ1000件
更新SQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

while 1=1
begin
	begin tran

	update [Sample].[dbo].[Table]
	set [Status] = -[Status]   --同じ値で更新しても発生しない。
	where [ID] between 1 and 10 --発生させやすくするため。単一の値でも発生する。

	commit
end
参照SQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

while 1=1
begin
	begin tran
	
	select *     --カバードインデックスにならないように、全カラム
	from [Sample].[dbo].[Table]
	where [Status] = 35
		
	commit
end

結果

f:id:eo-oe-aaaa:20090820141815j:image
f:id:eo-oe-aaaa:20090820141816j:image

プロファイラ

f:id:eo-oe-aaaa:20090820143713j:image

何故?

プロファイラの赤枠の中をみてもわかるように、



つまり、クラスタ化インデックスと非クラスタ化インデックス間でたすきがけのデッドロックが発生した。

ちなみに

SQL Server 2005、2000も同様の結果になりました。
クラスタ化インデックスと、非クラスタ化インデックスのデータ構造がある限り、
どうしても発生してしまう仕様ですね。

対処法

こんなところですかね。
・インデックスを変える
・nolockでSELECTする
・リトライするようにする