SQL SERVERにおける更新の罠〜インデックス更新時にレコードが消える？〜

インデックスのキー項目となっているカラムの更新と、
nolockオプションを使用しての更新対象レコードの参照が同時に発生した場合、
（分離レベルが「read uncommitted」の場合も同様。）
対象レコードが無いものとして扱われる場合がある。

以下、それぞれクラスタ化インデックスと非クラスタ化インデックスの場合で検証を行う。

テスト環境

SQL SERVER 2008 Express SP1

テーブル構成

• 列
  • PKey:char(10) ※主キー
  • Memo:char(10)
• インデックス
  • クラスタ化インデックス
    • PKey
  • 非クラスタ化インデックス
    • Memo
• 内容

Pkey	Memo
Yes	Boss
I	Pen
Hello	World

計3件

クラスタ化インデックスの場合

更新

while 1=1
begin  
    update   [Sample].[dbo].[PUpdate]
    set Pkey = 'Hello'
    where Pkey = 'Hello'
end

選択

declare @i int 
set @i=0 
  
while 1=1
begin
    --1件取得できなかった場合は、取得件数をPrintする
    select @i = COUNT(*)
    from [Sample].[dbo].[PUpdate] with(nolock)
    where PKey = 'Hello'
	
    if @i <> 1
    begin
        print @i
    end
end

結果

Update文実行前

Update文実行後

取得件数が0になってる。

基本的に主キーを更新することはないと思うので実用上問題になることは少ないでしょう。
が、主キー（クラスタ化インデックス）の場合のみならず、非クラスタ化インデックスでも同様の現象が発生する。

非クラスタ化インデックスの場合

更新

declare @i int
set @i = 0

while 1=1
begin  
    --非クラスタ化インデックスの場合は同じ値で更新しても発生しないようなので、
    --毎回更新する値を変えて実行。
    --※推測：通常、クラスタ化インデックスのデータ更新後、非クラスタ化インデックスの更新を行うが、
    --　　　　値が更新されていないことを検知して非クラスタ化インデックスの更新を行わない？
    update   [Sample].[dbo].[PUpdate]
    set Memo = 'Boss' + convert( char(10), @i)
    where Memo like  'Boss%'
    
    set @i = @i+1
end

選択

declare @i int 
set @i=0 
  
while 1=1
begin
    --1件取得できなかった場合は、取得件数をPrintする
    select @i = COUNT(*)
    from [Sample].[dbo].[PUpdate] with(nolock)
    where Memo like 'Boss%'
	
    if @i <> 1
    begin
        print @i
    end
end  

結果

Update文実行前

Update文実行後

取得件数が0になってる。

原因

内部的にはインデックスの更新を行う場合、
インデックスツリーの該当レコードを一度削除して、再度追加というやり方をしているらしい。
なので、削除と追加の間で参照した場合に、該当レコードが無いと判断されてしまう。
※nolockじゃなければ起こらない。

結論

主キーの更新は普通しないだろうけど、
非クラスタ化インデックスキー項目の更新は普通に行われるので、
nolockを使うときは注意。