[解決済み] UPDLOCK、HOLDLOCKの混乱について

質問

の使用について調べているうちに テーブルヒント の使用について調べているときに、次の 2 つの質問に出くわしました。

• どのロックヒントを使うべきですか (T-SQL)？

• HOLDLOCKはUPDLOCKにどのような影響を与えるのでしょうか？

両方の質問に対する回答では (UPDLOCK, HOLDLOCK) を使用すると、他のプロセスはそのテーブルのデータを読み取ることができなくなる、とありますが、私はこれを見ませんでした。 テストするために、私はテーブルを作成し、2 つの SSMS ウィンドウを立ち上げました。 最初のウィンドウから、さまざまなテーブルヒントを使用してテーブルから選択するトランザクションを実行しました。 トランザクションが実行されている間、2 番目のウィンドウからさまざまなステートメントを実行し、どれがブロックされるかを確認しました。

テスト テーブルです。

CREATE TABLE [dbo].[Test](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](50) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SSMSウィンドウから1．

BEGIN TRANSACTION

SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:10'

COMMIT TRANSACTION

SSMSウィンドウ2より（以下のいずれかを実行）。

SELECT * FROM dbo.Test
INSERT dbo.Test(Value) VALUES ('bar')
UPDATE dbo.Test SET Value = 'baz' WHERE Value = 'bar'
DELETE dbo.Test WHERE Value= 'baz'

Window 2で実行されるステートメントに対する異なるテーブルヒントの効果。

           (UPDLOCK)       (HOLDLOCK)    (UPDLOCK, HOLDLOCK)    (TABLOCKX)
---------------------------------------------------------------------------
SELECT    not blocked      not blocked       not blocked         blocked
INSERT    not blocked        blocked           blocked           blocked
UPDATE      blocked          blocked           blocked           blocked
DELETE      blocked          blocked           blocked           blocked

私はこれらの質問で与えられた答えを誤解していたのでしょうか、それとも私のテストに間違いがあったのでしょうか？ もしそうでないなら、なぜ (UPDLOCK, HOLDLOCK) と比較して (HOLDLOCK) のみですか？

私が達成しようとしていることをさらに説明します。

あるテーブルから行を選択し、そのテーブルのデータが処理中に変更されないようにしたいのです。 私はそのデータを変更しないので、読み取りが行われるようにしたいと思います。

この回答 は明確に次のように言っています。 (UPDLOCK, HOLDLOCK) は読み込みをブロックする（私が望むものではない）。 のコメントは この答え が暗示するのは HOLDLOCK であることを意味します。 テーブルヒントの効果をよりよく理解するために、そして、もし UPDLOCK が単独で私が望むことを行うかどうかを確認するために、私は上記の実験を行い、これらの答えと矛盾する結果を得ました。

現在、私が考えるに (HOLDLOCK) を使うべきだと考えていますが、間違いを犯したり、何かを見落としたりして、それが将来私を苦しめることにならないか心配なので、この質問をさせていただきました。

どのように解決するのですか？

なぜUPDLOCKはセレクトをブロックするのですか？その ロック互換性マトリックス を見ると、明らかに N のように、S/UとU/Sのコンテンションに対して ノーコンフリクト .

については ホールドロック のヒントについて、ドキュメントにはこう書かれています。

HOLDLOCK: SERIALIZABLEと同等です。詳細については、このトピックで後述する SERIALIZABLE を参照してください。

...

SERIALIZABLE: ... スキャンはSERIALIZABLE分離レベルで実行されているトランザクションと同じセマンティクスで実行されます...

となり トランザクションの分離レベル トピックでは、SERIALIZABLE の意味について説明しています。

他のトランザクションは、現在のトランザクションが完了するまで、そのトランザクションによって読み取られたデータを変更できません。 他のトランザクションは、現在のトランザクションが完了するまで、そのデータを変更することはできません。

他のトランザクションは、現在のトランザクションのステートメントによって読み取られたキーの範囲に入るようなキー値を持つ新しい行を挿入することはできません。 現在のトランザクションのステートメントによって読み取られたキーの範囲内にある のステートメントが読み取るキーの範囲に入るキー値を持つ新しい行を挿入することはできません。

したがって、あなたが見た動作は、製品のドキュメントによって完全に説明されます。

• UPDLOCK は同時実行の SELECT や INSERT をブロックしませんが、T1 によって選択された行のすべての UPDATE または DELETE をブロックします。
• HOLDLOCKはSERALIZABLEを意味するため、SELECTSは許可しますが、T1によって選択された行のUPDATEとDELETESはブロックします。 と同様に T1 によって選択された範囲での INSERT (これはテーブル全体であり、したがって は任意の を挿入します)。
• (UPDLOCK, HOLDLOCK): あなたの実験は、上記のケースに加えて何がブロックされるかを示していません、すなわち T2 の UPDLOCK を持つ別のトランザクション :
  
  SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
• TABLOCKX 説明の必要なし

本当の疑問は 何を達成しようとしているのか ? ロック セマンティクスを完全に 110% 理解していない状態でロック ヒントを使用することは、トラブルの元です...。

OP編集後。

あるテーブルから行を選択し、処理中にそのテーブルのデータが変更されないようにしたい。 テーブルのデータが変更されないようにしたいのですが。

より高いトランザクション分離レベルのいずれかを使用する必要があります。REPEATABLE READは読み込んだデータが変更されることを防ぎます。SERIALIZABLEは読み込んだデータが変更されることを防ぎます。 と 新しいデータが挿入されるのを防ぎます。トランザクション分離レベルを使用することは、クエリヒントの使用とは対照的に、正しいアプローチです。Kendra Little は に、分離レベルについて説明した素晴らしいポスターがあります。 .