質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.49%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 21K+

score 15

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


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'

検証

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

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

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

2)ロック状況の確認
いくつかサイトで紹介されていた、下記のロック状況確認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の値が違います。
検索結果1

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

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

検索結果
検索結果2

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

質問と仮説

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

2)仮説:SQL Serverはロック対象を取得できる行そのものではなくスキャンに使用したインデックスで判定している?
調査1~3の結果から、SELECT結果そのもので同一行をロックしているか判定するのではなく、スキャンに使用するインデックスで判定しているのかなと考えました。

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

質問1,2は勉強不足で判りませんが3に関してはロックパラメータを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変更の事例も書いてみました。質問3に関しては事例やアドバイスの収集が目的と思い、このような書きかたをしました。追加の回答はしないかも知れませんが、ご了承下さい。

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/17 16:07

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

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

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

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

    キャンセル

  • 2017/06/17 21:36

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

    キャンセル

  • 2017/07/09 21:22

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/18 11:28

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

    SQLの書き方を下記のように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'
    )

    キャンセル

  • 2017/06/20 12:35

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

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

    キャンセル

  • 2017/06/23 13:26

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る