z
「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-[[戻る>SQL Server]]

* 目次 [#r7a52c02]
#contents
*前提知識 [#b6f315d4]
SQL Serverは参照処理にもロック(共有ロック)を~
かけるということを知っておくことが重要。

**ロックの種類 [#t4f727ff]
ロックの種類は
-DMLの種類
-[[分離レベル>#f0ab9db5]]の指定

によって変わってくる。(ただし、DDLによるロックもある)~

**ロックの互換性 [#le819818]
各ロックの互換性についても考慮が必要。~

-ロックの互換性 (データベース エンジン)~
http://msdn.microsoft.com/ja-jp/library/ms186396.aspx

また、指定された[[分離レベル>#f0ab9db5]]を実現するため、~
トランザクション中のロックがホールドされるか?~
・・・などで、ロックの動きが変わってくる。

**分離レベル [#f0ab9db5]
-分離レベルについて~
http://msdn.microsoft.com/ja-jp/library/ms378149.aspx

-[[DBMSのロック・分離戦略と同時実行制御]]

*確認方法 [#i2f35481]
こちら([[SQL Server 問題の分析方法]])も参考にできる。

**ロックをかける [#j73ca180]
以下のSQLで、
-トランザクションを開始し
-参照処理することで

ロックをホールドしておく。

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRANSACTION
 SELECT * FROM Shippers WHERE ShipperID = 1
 --COMMIT

**ロック タイムアウト値を設定 [#u9e506b4]
SQL Serverでロック タイムアウト値を設定する方法は以下のとおりである。~
※ この設定は、接続確立毎に実行する必要がある。

 SET LOCK_TIMEOUT timeout_period

現在のロック タイムアウト値を調べるには、次に示す@@LOCK_TIMEOUT関数を実行する。

 SELECT @@lock_timeout;

-なお、ロック タイムアウトとコマンド タイムアウトは別物であり、
--コマンド タイムアウトはデータプロバイダに設定する。
--値の設定方法は、データプロバイダ毎に個別となる。

**ロック状況の確認方法 [#ie3da205]
-ロック タイムアウトやコマンド タイムアウトが発生した場合、~
そのタイムアウトがロックによるものか、コマンドによるものかを切り分ける必要がある。

-基本的に、ロック タイムアウト値をコマンド タイムアウト値より短く設定しておけば、~
ロック タイムアウトであるか、コマンド タイムアウトであるかを切り分けることが出来る。

-参考 : 「[[ASP.NETで考慮すべきタイムアウト値]]」

***sp_lock_detailストアドプロシージャ [#jd968bd2]
ロック タイムアウトであった場合は、以下のHPにある方法でロック状況を確認すると良い。

-@IT > データを効率的に処理するエッセンスを教えます~
' > 連載:SQL実践講座(28・最終回) - SQL Serverで「デッドロック」を回避する~
http://www.atmarkit.co.jp/fnetwork/rensai/sql28/sql1.html

--以下はロック状況を確認するsp_lock_detailストアドプロシージャの作成スクリプトをコピペ利用可能にしたもの。
--このsp_lock_detailストアドプロシージャは、次の1ステートメントで実行可能である。

 sp_lock_detail

 CREATE PROCEDURE sp_lock_detail
   @spid1 int = NULL, /* server process id to check for locks */
   @spid2 int = NULL  /* other process id to check for locks */
 AS
 
 SET NOCOUNT ON
 
 /* Show the locks for both parameters. */
 IF @spid1 IS NOT NULL
 
   BEGIN
 
     SELECT CONVERT (SMALLINT, req_spid) AS spid,
       db_name(rsc_dbid) AS dbid,
       object_name(rsc_objid) AS ObjId,
       rsc_indid AS IndId,
       SUBSTRING (v.name, 1, 4) AS Type,
       SUBSTRING (rsc_text, 1, 16) AS Resource,
       SUBSTRING (u.name, 1, 8) AS Mode,
       SUBSTRING (x.name, 1, 5) AS Status
 
     FROM master.dbo.syslockinfo,
       master.dbo.spt_values v,
       master.dbo.spt_values x,
       master.dbo.spt_values u
 
     WHERE master.dbo.syslockinfo.rsc_type = v.number
       and v.type = 'LR'
       and master.dbo.syslockinfo.req_status = x.number
       and x.type = 'LS'
       and master.dbo.syslockinfo.req_mode + 1 = u.number
       and u.type = 'L'
       and req_spid in (@spid1, @spid2)
   END

 /* No parameters, so show all the locks. */
 ELSE
 
   BEGIN
 
     SELECT CONVERT (SMALLINT, req_spid) AS spid,
       db_name(rsc_dbid) AS dbid,
       object_name(rsc_objid) AS ObjId,
       rsc_indid AS IndId,
       SUBSTRING (v.name, 1, 4) AS Type,
       SUBSTRING (rsc_text, 1, 16) AS Resource,
       SUBSTRING (u.name, 1, 8) AS Mode,
       SUBSTRING (x.name, 1, 5) AS Status
 
     FROM master.dbo.syslockinfo,
       .dbo.spt_values v,
       .dbo.spt_values x,
       .dbo.spt_values u
     
     WHERE master.dbo.syslockinfo.rsc_type = v.number
       and v.type = 'LR'
       and master.dbo.syslockinfo.req_status = x.number
       and x.type = 'LS'
       and master.dbo.syslockinfo.req_mode + 1 = u.number
       and u.type = 'L'
     ORDER BY spid
 
   END
 
 RETURN (0) -- sp_lock

***動的管理ビュー [#ue59307f]
ロック状況の監視には、

-ご機嫌斜めの天国 【SQL Server】ロック一覧を表示する~
http://cloudyheaven.blog130.fc2.com/blog-entry-49.html

で紹介されているSQLを使用できる。~
(sys.dm_tran_locks 動的管理ビューを使用する)~

 SELECT
  resource_type as オブジェクトの種類,
  resource_associated_entity_id as エンティティID,
  request_mode as ロックの種類,
  request_type as 要求の種類,
  request_status as 状態
 From sys.dm_tran_locks
 WHERE [resource_type]<>'Northwind';

※ Northwindの所にはDB名を入力する。~

-ロックに関する情報の表示 (データベース エンジン)~
http://msdn.microsoft.com/ja-jp/library/ms177475.aspx
--sp_lock (Transact-SQL) 
--sys.syslockinfo (Transact-SQL) 

>SQL Server 2005 以降では、代わりに sys.dm_tran_locks 動的管理ビューを使用

***CASEを使った拡張 [#kf22be05]
上記の「エンティティID」をCASEを使って自己解決するバージョン。

 SELECT
   resource_type as オブジェクトの種類,
   (CASE 
     WHEN resource_type = N'ALLOCATION_UNIT' THEN 
       (SELECT 
         OBJECT_NAME(sp.object_id) + N':' +  si.name
         FROM 
           sys.allocation_units 
           LEFT JOIN 
             sys.partitions sp 
             ON 
               container_id = hobt_id 
           LEFT JOIN 
             sys.indexes si 
             ON 
               sp.object_id = si.object_id 
               AND sp.index_id = si.index_id
         WHERE 
           allocation_unit_id = resource_associated_entity_id)  
     WHEN  resource_type = N'OBJECT' THEN 
       OBJECT_NAME(CONVERT(bigint, resource_associated_entity_id))
     WHEN resource_type IN(N'HOBT', N'KEY', N'PAGE') THEN 
       (SELECT 
         OBJECT_NAME(sp.object_id) + N':' +  si.name
       FROM 
         sys.partitions sp 
         LEFT JOIN 
           sys.indexes si 
           ON 
             sp.object_id = si.object_id 
             AND sp.index_id = si.index_id 
       WHERE 
         hobt_id = resource_associated_entity_id) 
     ELSE    
       CONVERT(nvarchar(40), resource_associated_entity_id) 
   END) as エンティティID,
   request_mode as ロックの種類,
   request_type as 要求の種類,
   request_status as 状態
  From sys.dm_tran_locks
  WHERE [resource_type]<>'Northwind';

-[[非クラスター化インデックス再構築時の同時実行性について « SE の雑記>http://engineermemo.wordpress.com/2011/06/18/%E9%9D%9E%E3%82%AF%E3%83%A9%E3%82%B9%E3%82%BF%E3%83%BC%E5%8C%96%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E5%86%8D%E6%A7%8B%E7%AF%89%E6%99%82%E3%81%AE%E5%90%8C%E6%99%82%E5%AE%9F%E8%A1%8C/]]

**[[SQLトレース>SQLプロファイラ(SQLトレース)]]を使用して確認 [#c295bf32]
[[SQLトレース>SQLプロファイラ(SQLトレース)]]を取って問題を起こしているSQLを確認できる。~

以下の様な問題に起因していることが多い。
-スキャンにより広範囲にロックがかかる。
-ロック・エスカレーションにより広範囲にロックがかかる。

-なお、デッドロック チェーンは基本的に直ちに検出されるので、~
プログラミングにおけるデッドロックのように、~
ハングアップ(ロック・タイムアウト)にはならない。

よくあるパターンに、~

-テーブル・スキャンによる ロック待ち~
http://www.shoeisha.com/mag/windev/pdf/870602/windev0602_129_SQLServer.pdf

-暗黙の型変換によるインデックス・スキャンによる、~
インデックス・ロック(広範囲にロックがかかる)

がある。

また、統計情報が更新されていないと、~
インデックス・シークが適用されず、~
-インデックス・スキャン
-テーブル・スキャン

により、広範囲にロックがかかることがある。

なお、統計情報の更新タイミングについては、

-[[SQL Server のオプティマイザ]]

を参照にできる。

*対策 [#lb9591d0]

**適切な[[分離レベル>#f0ab9db5]]を選択する。 [#i5623668]

**MVCCに変更する。 [#s977682f]
SQL Server2005から、動作を[[MVCC>DBMSのロック・分離戦略と同時実行制御#we9e7704]]に変更することも可能。

**エスカレーションを抑止する。 [#hbe78258]
[[SQL Server のロックのエスカレーション]]の発生を抑止できる。

**ロックをカスタマイズする。 [#l6012292]

***ロック ヒント [#sd92ab00]
テーブル ヒントにロック手法をWITH (ROWLOCK)などと指定する。

-テーブル ヒント (Transact-SQL) | Microsoft Docs~
https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-table

-行ロックの動作についてお試ししてみる - 都内で働くSEの技術的なひとりごと~
http://ryuchan.hatenablog.com/entry/2016/08/28/162627

***sp_indexoption [#e68065e6]
アクセス パターンが一定していることがわかっている場合、~
ページまたは行のロックを禁止することが効果的なケースもある。

-インデックスのロックのカスタマイズ~
https://technet.microsoft.com/ja-jp/library/ms189076.aspx
-sp_indexoption (TRANSACT-SQL)~
https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-indexoption-transact-sql

*事例 [#a1170e91]

**こんな単純なSQLでもブロッキングになる。 [#b0e7e0f6]
Oracleなど、[[多バージョン法(MultiVersion Concurrency Control:MVCC)>DBMSのロック・分離戦略と同時実行制御#z1614545]]のDBMSに慣れると、~
驚くことになるが、以下の非常に簡単な2つのトランザクションで、ブロッキングが発生する。

***トランザクション1(先発) [#fd15b8c0]
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 BEGIN TRANSACTION;
 
 INSERT INTO [Northwind].[dbo].[Orders]
  ([OrderID], ・・・) VALUES (11078, ・・・);
 
   若しくは
 
 DELETE FROM [Northwind].[dbo].[Orders] WHERE [OrderID] = 11078;
 
 --COMMIT TRANSACTION
 --ROLLBACK TRANSACTION

***トランザクション2(後発) [#ed307e13]
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 BEGIN TRANSACTION;
 
 SELECT * FROM [Northwind].[dbo].[Orders]
  WHERE [OrderID] BETWEEN 11070 AND 11080
 
 --COMMIT TRANSACTION
 --ROLLBACK TRANSACTION

***ポイント [#hc7510b8]
ポイントは、後発の参照処理が、インデックスをキー範囲ロックしようとしている点。

特に、メモリの少ないクライアントOS上のSQL Server Express Edition等で、

>「[[クエリ・プランがインデックス・スキャンを選択した場合>SQL Server のオプティマイザ]]、~
主キーでのSELECTが、主キーのキー範囲ロックになってしまい、~
他の排他ロック(INSERTやDELETEを含む)にブロッキングされる。」

というケースもあった(本番環境では再現せず)。

**逆に、これでブロッキングしない。 [#f1f70626]
使用するインデックスが異り、ブロッキングしなくなる。

***トランザクション1(先発) [#dfa97441]
 SELECT * FORM T WITH(UPDLOCK) WHERE PK=1

***トランザクション2(後発) [#p96939e8]
 SELECT * FORM T WITH(UPDLOCK) WHERE PK=1 AND XXX=YYY

***ポイント [#vd0b01e5]
上記で、PK=1がロック非互換でブロッキングされないのは、

使用するインデックスが、

-主キーの「クラスタ化インデックス」
-複合インデックスの「非クラスタ化インデックス」

と、使用するインデックスが異り、ブロッキングしなくなる。

*参考 [#d3618988]

-[[ブロッキングとデッドロックを後追い確認する方法 « SE の雑記>http://engineermemo.wordpress.com/2012/08/30/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%81%A8%E3%83%87%E3%83%83%E3%83%89%E3%83%AD%E3%83%83%E3%82%AF%E3%82%92%E5%BE%8C%E8%BF%BD%E3%81%84%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B%E6%96%B9/]]

-Microsoft SQL Server Japan Support Team Blog > [SQL Troubleshooting]~
 第6回:ブロッキング情報を採取する (SQL Server 2000 ~ 2008 R2)~
 http://blogs.msdn.com/b/jpsql/archive/2012/07/24/sql-troubleshooting-6-sql-server-2000-2008-r2.aspx

-SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法~
http://support.microsoft.com/kb/271509/ja

----
Tags: [[:データアクセス]], [[:SQL Server]], [[:障害対応]], [[:性能]], [[:デバッグ]]


トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS