SE の雑記

SQL Server には「NOLOCK」というヒント句があります。

基本的な動作については ヒント (Transact-SQL) – Table に記述があり、このドキュメントには次のように記載されています。

READUNCOMMITTED ヒントと NOLOCK ヒントはデータのロックにのみ適用されます。 READUNCOMMITTED ヒントおよび NOLOCK ヒントを含むクエリを含め、すべてのクエリは、コンパイル中と実行中にスキーマ安定度 (Sch-S) ロックを取得します。 このため、同時実行トランザクションがテーブルの Sch-M (スキーマ修正) ロックを保持している場合、クエリはブロックされます。 たとえば、データ定義言語 (DDL) 操作では、テーブルのスキーマ情報を変更する前にスキーマ修正 (Sch-M) ロックを取得します。 READUNCOMMITTED ヒントまたは NOLOCK ヒントを指定して実行しているクエリを含め、すべての同時実行クエリは、スキーマ安定度 (Sch-S) ロックを取得しようとするとブロックされます。 一方、スキーマ安定度 (Sch-S) ロックを保持するクエリによって、スキーマ修正 (Sch-M) ロックを取得しようとする同時実行トランザクションはブロックされます。

ロックについては「Sch-S」のロックが取得され、それ以外のロックについては取られないという動作となる記述がありますが、これ以外の動作として「ダーティーリードを許可する」という動作については意識をしておく必要があります。
これについては、次の記事でも触れられています。

• Previously committed rows might be missed if NOLOCK hint is used
• Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.
• “But NOLOCK Is Okay When The Data Isn’t Changing, Right?”

単純なパターンであれば、「コミット前のデータが読み取られるがロック競合を防ぎながらデータにアクセスができる」という用途で使うケースがありますが、それ以外のダーティーページが読み取られる可能性についても考慮しておく必要があります。

代表的な例としては「UPDATE」を実行されている場合に、データの件数が想定とは異なるという可能性については考慮する必要があります。
テストとして、次のようなクエリでデータを生成してみます。

DROP TABLE IF EXISTS NOCLOCK_TEST
GO
CREATE TABLE NOCLOCK_TEST(C1 int PRIMARY KEY,C2 varchar(100), C3 varchar(100))
GO
SET NOCOUNT ON
GO
DECLARE @cnt int = 1
BEGIN TRAN
WHILE(@cnt <= 300000)
BEGIN
	INSERT INTO NOCLOCK_TEST VALUES(@cnt, NEWID(), NEWID())
	SET @cnt += 1
END
COMMIT TRAN
GO
ALTER INDEX ALL ON NOCLOCK_TEST REBUILD
GO
&#91;/sourcecode&#93;
</pre>
</div>
実行が完了すると「300,000 件」のレコードが格納された状態になります。
この状態で、次のクエリを実行してみます。
<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:45604ede-0112-4c1a-9ff7-9b4e94934ac1" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>
[sourcecode language='sql' ]
DECLARE @id varchar(100) = (SELECT CAST(NEWID() as varchar(36)) + CAST(NEWID() as varchar(36)))
UPDATE NOCLOCK_TEST SET C2 = @id, C3 = @id
GO

 
全件に対しての UPDATE は実行していますが、更新だけですのでデータの件数としては変わっていません。

この状態で、次のようなスクリプトを実行して件数をチェックするとどうなるでしょうか。

$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=.;Database=testdb;Integrated Security=SSPI"
$con.Open()
$cmd = $con.CreateCommand()
Clear-Host
$cmd.CommandText = "SELECT DISTINCT COUNT(*) FROM NOCLOCK_TEST WITH(NOLOCK)"
while($true){
    $ret = $cmd.ExecuteScalar()
    if ($ret -ne 300000){
        Write-Host $ret
    }
    Start-Sleep -Milliseconds 100
}
$con.Close()

 
実行結果としては次のようになります。

全行に対しての UPDATE を実行しているだけなのですが、SELECT されたタイミングによって件数が変化していることが確認できます。
必ずしもこのような動作になるというわけではないのですが、NOLOCK でダーティーリードによる読み込みを実施している場合で「ページ分割により新規ページへのデータの移動」が発生しているようなケースでは、このような現象が発生しやすいかと思います。
ページ分割は、ページ内にデータが入りきらない際にページ内の行を 50/50 に分割して、2 つのページに再配置する動作となります。
この動作が行われている際に NOLOCK でデータに対してアクセスされると、

• 移動前のページに入っている状態のデータ
• 移動後のページに入っている状態のデータ

の両方が瞬間的に見えてしまう可能性があり、NOLOCK を使用した検索では瞬間的に「異なるページに入っている同一のデータ」としてデータが重複して見えてしまう可能性があります。
上記の画像ではデータは重複による増加が発生しているように見えたパターンですが、タイミングによってはデータが減っているように見えることもあります。
「NOLOCK」による検索は行に対してのロックをかけないだけではなく「ダーティーリードを許可したことにより、一貫性のないデータに対してアクセスを行うことを許容した」状態となるということが重要となります
これにはページ分割の用な内部的なデータの分割による影響についても受ける可能性があるということになります。
これを回避するためには、

• NOLOCK を外し、ブロッキングは発生するが一貫性のあるデータにアクセスするようにする
• Read Committed Snapshot のような読み取り一貫性のある分離レベルを使用する

というようなことを検討する必要があります。

(今回のケースであれば、DISTINCT をつけても重複排除にはなりません)
Read Committed Snapshot を使用していても NOLOCK ヒントをつけてしまいますと、ダーティーリードを許可したという動作となりますので、現象を回避することはできませんので気を付けてください。
Read Committed Snapshot を使用している場合は、NOLOCK ヒントを設定せずにデータにアクセスを行わないと効果がありませんので。