質問: 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](https://teratail.storage.googleapis.com/uploads/contributed_images/c438ddbbb1a5fcc55930d6850234d7bb.png) **3)WITH句のヒント句にインデックス指定** WITH句に、PKのインデックスを指定したところ、期待値どおりになりました。 **推定実行プラン** **Session2** Clustered Index Seek[PK] 受注情報ヘッダ 51% Index Seek(NonClustered)[受注情報ボディのインデックス] 受注情報ボディ 49% **検索結果** ![検索結果2](https://teratail.storage.googleapis.com/uploads/contributed_images/ca7acea75d0b1b05cece8249f273e6c2.png) ※ちなみに、nowait指定を外すとSession2は待ちとなりますが、そのときの検索結果は次のようになりました。 ![検索結果3](https://teratail.storage.googleapis.com/uploads/contributed_images/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のイメージが強いせいもあるのですが......。 結構困っているので、お知恵をお借りできれば幸いです。
回答: 質問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が必要なのかもしれないと推測しました。