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

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

新規登録して質問してみよう
ただいま回答率
87.20%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

解決済

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

chida3
chida3

総合スコア0

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

2回答

1評価

0クリップ

187閲覧

投稿2017/06/11 13:04

編集2022/01/12 10:58

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

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

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

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

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

##環境
SQL Server2014
ReadCommitted Snapshot

##問題のSQL等
発生した事象で使われているテーブルや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\]

ボディテーブル

SQL

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以外にいくつかインデックスが定義されています)

SQL

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

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」の値が異なりました。

SQL

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](c438ddbbb1a5fcc55930d6850234d7bb.png)

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

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

**検索結果**
![検索結果2](ca7acea75d0b1b05cece8249f273e6c2.png)

※ちなみに、nowait指定を外すとSession2は待ちとなりますが、そのときの検索結果は次のようになりました。
![検索結果3](27a2469c949f3598c197dfe276dd1237.png)

##質問と仮説
**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ページの「注目」タブのフィードに表示されやすくなります。

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

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

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

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

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

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

まだ回答がついていません

会員登録して回答してみよう

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

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

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

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。