【SQL Server】同じ行をロックしているはずだが、SELECT時に排他エラーが発生しない？

Question

2017/07/09
下記３件の質問の内、１と２について回答が得られていないのですが、一旦質問をクローズいたします。
理由としましては、
・３の質問に関してご回答いただけたことで、対応策の事例収集という目的の一つは達せられたこと。
・排他ロックについて異なる事象が発生し、１、２の質問から派生して調査等をおこなったほうがよさそうであること。
・ただ、１、２に関して自分なりの調査・検証時間がしばらく確保できない見込みであること。
です。
ご回答いただいたお二方ありがとうございました。お礼が遅くなりすみません。
今後、自分での調査・検証を進めることができましたら、追記等させていただきたいと思います。

---

SQL Serverを使用したWebアプリで発生した事象です。

##現象
先発のセッションで排他ロックがかかり、後発のセッションで排他ロック検知後、正常にエラー処理がされるかというテストをしていました。

【方法】
Session1：単テーブル（ヘッダ）に対し、PKを条件に指定してロック（NOWAIT）。
Session2：複数テーブル（ヘッダとボディ）を結合しつつ、ヘッダテーブルのみロック（結合条件はヘッダのPK）（NOWAIT）。

【期待値】
Session1でロックした行は、Session2の検索範囲に含まれるはずなので、Session2においてSELECT時に排他エラーが発生する。

【結果】
排他エラーが発生しない。
デバックすると、Session2のSELECT箇所および排他エラー処理はすり抜けてしまい、後続のヘッダテーブル更新箇所でロック開放待ち→タイムアウトしていました。

##環境
SQL Server2014
ReadCommitted Snapshot

##問題のSQL等
発生した事象で使われているテーブルやSQLについて。
テーブルやインデックス定義、ロックSQLについては、こちらに書き込む用にかなり簡略化しています。そのため、事象の再現はできませんでした。あくまでイメージと思っていただければ……。

ヘッダテーブル

CREATE TABLE [dbo].[受注情報ヘッダ](
	[支社コード] [nvarchar](4) NOT NULL,
	[受注伝票番号] [nvarchar](10) NOT NULL,
	[納品先コード] [nvarchar](10) NOT NULL,
	[出荷日] [nvarchar](8) NOT NULL,
	[納品日] [nvarchar](8) NOT NULL,
	[処理ステータス] [nvarchar](1) NOT NULL,
 CONSTRAINT [PK_受注情報ヘッダ] PRIMARY KEY CLUSTERED 
(
	[支社コード] ASC,
	[受注伝票番号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

ボディテーブル

CREATE TABLE [dbo].[受注情報ボディ](
	[支社コード] [nvarchar](4) NOT NULL,
	[受注伝票番号] [nvarchar](10) NOT NULL,
	[受注伝票行番号] [int] NOT NULL,
	[商品コード] [nvarchar](10) NOT NULL,
	[ロット番号] [nvarchar](8) NOT NULL,
	[受注数量] [numeric](10, 0) NOT NULL,
	[処理ステータス] [nvarchar](1) NOT NULL,
 CONSTRAINT [PK_受注情報ボディ] PRIMARY KEY CLUSTERED 
(
	[支社コード] ASC,
	[受注伝票番号] ASC,
	[受注伝票行番号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

インデックス（※性能の関係で、PK以外にいくつかインデックスが定義されています）

CREATE NONCLUSTERED INDEX [IDX_受注情報ヘッダ01] ON [dbo].[受注情報ヘッダ]
(
	[支社コード] ASC,
	[出荷日] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)

アプリ内のロックSQL

SELECT * 
FROM 受注情報ヘッダ H WITH(ROWLOCK,UPDLOCK,NOWAIT)
INNER JOIN 受注情報ボディ B
ON 1=1
AND H.支社コード = B.支社コード
AND H.受注伝票番号 = B.受注伝票番号
WHERE 1=1
AND B.支社コード = '1111'
AND B.商品コード = 'ITEM0001'

##検証
事象の発生後、下記の調査をしました。

１）実行計画の調査
Session2のアプリ内SQLの実行計画を見てみたところ、ヘッダテーブルのスキャンにPK以外のインデックス（例：上記のIDX_受注情報ヘッダ01）が使われていました。

推定実行プラン
Session1：
Clustered Index Seek[PK] 受注情報ヘッダ
Session2：
Index Seek(NonClustered)[IDX_受注情報ヘッダ01] 受注情報ヘッダ 50%
Index Seek(NonClustered)[受注情報ボディのインデックス] 受注情報ボディ 50%

２）ロック状況の確認
いくつかサイトで紹介されていた、下記のロック状況確認SQLを実行しました。
結果、Session1とSession2それぞれ、「resource_associated_entity_id」の値が異なりました。

SELECT
T1.resource_type
,T1.resource_associated_entity_id
,(CASE
	WHEN T1.resource_type = 'OBJECT'
	THEN OBJECT_NAME(T1.resource_associated_entity_id)
	ELSE (
		SELECT
		OBJECT_NAME(OBJECT_ID)
		FROM sys.partitions
		WHERE
		hobt_id = T1.resource_associated_entity_id
	)
	END
) AS object_name
,T1.request_mode
,T1.request_type
,T1.request_status
,T1.request_session_id
,(
SELECT hostname
FROM sys.sysprocesses
WHERE spid = T1.request_session_id
) AS PROCESSNAME
FROM
sys.dm_tran_locks T1
WHERE 1=1
AND T1.resource_type <> 'DATABASE'
ORDER BY
T1.request_session_id

検索結果
Session1と2のSQLを実行後、確認SQLを実行したときの検索結果は下図のようになりました。
両セッション間で、resource_associated_entity_idの値が違います。

３）WITH句のヒント句にインデックス指定
WITH句に、PKのインデックスを指定したところ、期待値どおりになりました。

推定実行プラン
Session2
Clustered Index Seek[PK] 受注情報ヘッダ 51%
Index Seek(NonClustered)[受注情報ボディのインデックス] 受注情報ボディ 49%

検索結果

※ちなみに、nowait指定を外すとSession2は待ちとなりますが、そのときの検索結果は次のようになりました。

##質問と仮説
１）resource_associated_entity_idとは何を指すのか？
sys.dm_tran_locksの「resource_associated_entity_id」の項目に表示される値は何なのでしょうか？
ロックされているオブジェクトのID（＝ロックされているヘッダテーブルの行）だと思っていたのですが、調査２で、スキャンのインデックスが異なる場合に違うIDになっていたのを疑問に思いました。

２）仮説：SQL Serverはロック対象を取得できる行そのものではなくスキャンに使用したインデックスで判定している？
調査１～３の結果から、SELECT結果そのもので同一行をロックしているか判定するのではなく、スキャンに使用するインデックスで判定しているのかなと考えました。

３）SQL Serverにおけるロックの実装・設計はどうすべきか
上記仮説が正しい場合、厳密にロックを制御したければ、1行1行ループしながらロックするとか、インデックスをいちいちヒントにしていするとか、やはり必要なのでしょうか。
ロックエスカレーションの件もありますし、正直SQL Serverって難しいなという印象があります。
個人的にOracleのイメージが強いせいもあるのですが……。

結構困っているので、お知恵をお借りできれば幸いです。

Answer 1

質問１，２は勉強不足で判りませんが３に関してはロックパラメータをUPDLOCK
->XLOCKとしてはどうでしょうか。

FROM 受注情報ヘッダ H WITH(ROWLOCK,XLOCK,NOWAIT)

実証はしていないのでご了承下さい。参考にしたコンテンツです。
Isolation Levelも適切に設定する必要が有るかと思います。

実装・設計に関する部分でコメントします。
デフォルトのロック機能（読み込みは共有ロック、書き込みは排他ロック）のもと、DBのAtomicity特性に依存してテーブル設計、アプリケーション設計するのが一般的です（例えば受注処理にて、最後の在庫引き当てでマイナスになったらロールバックするようなモデル）。

個別レコードの排他ロックを制御するとなると、リトライ処理（タイマー持ちになると思いますが）などアプリケーションがトランザクション制御を担うことになります。もし自分であれば該当のトランザクションのみSerializable指定する方法を検討します。SQLServer+ADOであればIsolationLevelはトランザクション単位で指定可能です。その場合、Serializableとしたトランザクションのサイズ（レコード数・時間）が他の処理に影響しないものか評価が必要です。

---

「DBMSがトランザクションのACID特性を実現するための機能」を指すつもりで「トランザクション制御」という言葉を使いました。Oracle Masterではcommit文やrollback文をトランザクション制御文と呼称されていてアプリケーションがこれ等の文を発行することも「アプリケーションのトランザクション制御」に該当すると思います。まぎらわしい表現で済みませんでした。
文の意図としては、DBMSはトランザクションのACID特性を実現するために内部でリソース排他制御（リソース獲得、解放、タイムアウト監視）などの機能を実装していて、通常の（option無しの）SQL文の処理でも機能しています。なのでその上にAP側でも排他判定やタイムアウト監視を実装するのではなく、DBMS側の機能を利用する方向でデザインを検討されてはどうかというつもりでした。
それでもアプリケーション側の事情としてロック制御が必要なケースも有るかと思い、IsolationLevel変更の事例も書いてみました。質問３に関しては事例やアドバイスの収集が目的と思い、このような書きかたをしました。追加の回答はしないかも知れませんが、ご了承下さい。

XLOCKについては再現環境も無く追加情報は提示できませんが、行ロックにあたり対象の行がユニークでなければデッドロック要因ともなり得るのでその意味でPKが必要なのかもしれないと推測しました。

Comments

[chida3]

アドバイスありがとうございます。
分離レベルについてはシステム上READ_COMMITTED_SNAPSHOTで設定されているため、よほどの理由がない限り変更はできないです。
XLOCKへの変更につきましては、今週中、再現可能な環境での検証時間が取れ次第やってみます。
取り急ぎ失礼します。

[chida3]

ご報告遅くなりすみません。
XLOCKを試してみましたが、排他エラーとならず、今回質問させていただいた事象と同様になりました。

[chida3]

＞個別レコードの排他ロックを制御するとなると、リトライ処理（タイマー持ちになると思いますが）などアプリケーションがトランザクション制御を担うことになります

「アプリケーションがトランザクション制御を担う」という部分がよくわかりませんでした。
個人的に、トランザクションの管理はアプリケーションがするものだと思っていたのですが、誤解しているのでしょうか。
続きの回答で書いておられるように、「アプリケーション単位でデフォルトの分離レベルを変更する」という意味で捉えればよいのでしょうか？
頓珍漢なことをお伺いしていたらすみません……。

＞該当のトランザクションのみSerializable指定する方法を検討

そのようなやり方もあるのですね。
今回自分がテストしていた機能については、よくよく考えてみたら運用上ほかに処理が動いていないタイミングで使用するものなので、あまりロックのためにコストをかける必要はないのではと思い至ったのですが、他機能についてはご回答いただいたような精査が必要になってくるのかもしれませんね……。

[chida3]

追記ありがとうございます。意図がつかめました。ご提示いただいた解決法も勉強になりました。
質問１・２に関しては、自分でももう少し調べてみます。

[chida3]

自分の想定していなかった対応策だったため、遅くなりましたが、ベストアンサーとさせていただきました。

Answer 2

結合しながら排他ロックするのでなく
単純に下記だけの場合はどうなりますか？
SELECT *
FROM 受注情報ヘッダ H WITH(ROWLOCK,UPDLOCK,NOWAIT)

Comments

[chida3]

アドバイスありがとうございます。
設計的には、
・ボディのみがもつ項目で絞った上で、ひもづくヘッダも同時に取得したい。
・その時点でロックもしておきたい。
ので、単テーブルでのロックは次善策として考えています。

今週中、再現可能な環境での検証時間が取れ次第やってみますね。
検証方法としては、書いていただいたように単テーブルに対しロックをおこない、
①インデックス指定なしの場合
②ヒント句にPKのインデックスを指定した場合
③ヒント句にPK以外のインデックスを指定した場合
で比較してみようと思っています。

取り急ぎ失礼します。

[cutedog]

設計的な事もあるのは承知の上ですが、
まずは、原因の切り分けが優先すると思います。
①~③のようなややこしいことは後にして
単純なやり方から検証したほうが解決の早道にもなると思います。
また、基幹テーブルのみの排他判定からの個別処理のほうが、排他判定も早く、排他処理を共通処理にもできますし私的にはお薦めです。

[chida3]

ご報告遅くなりすみません。
単テーブル同士で同一PKを検索条件にした場合は、期待通り排他エラーとなりました。
（想定されていた検証の仕方になっていますでしょうか……？）

[cutedog]

こんにちは。
こちらでManagementStudioで試してみましたが
たしかに記載のＳＱＬではロックがかかってしまうので再現できませんね。

ＳＱＬの書き方を下記のようにEXISTSに変えてみてはどうですか？

BEGIN TRANSACTION
SELECT *
FROM 受注情報ヘッダ H WITH(ROWLOCK,XLOCK,NOWAIT)
WHERE EXISTS(
Select * FROM 受注情報ボディ B where
H.支社コード = B.支社コード
AND H.受注伝票番号 = B.受注伝票番号
AND B.支社コード = '1111'
AND B.商品コード = 'ITEM0001'
)

[chida3]

アドバイスありがとうございます。
こちらの相関サブクエリの場合、質問した事象と同様になり、期待値のようになりませんでした。

SQL仕様の時点でロックを確実にしたい場合、当初回答いただいたとおり、単テーブルでおこなったほうがよいかもしれませんね。

[cutedog]

こんにちは。
そうですね。こちらも実際の取得SQLでの再現できないので何とも言えないのですが
確かに単テーブルロックもしくはプライマリキーでのジョインまでくらいが一番確実だと思います。
あと他の手段として、排他方法が楽観的排他ロックであればchida3さんの思うような
SQLの発行方法でも問題ないんですけどね。
私は、WEBはもちらんですが、C/Sでも楽観的排他ロックを採用しています。