- 追加された行はこの色です。
- 削除された行はこの色です。
「[[マイクロソフト系技術情報 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によるロックもある）~
によって変わってくる。（ただし、DDLによるロックもある）~
各ロックの互換性についても考慮が必要です。~
**ロックの互換性 [#le819818]
各ロックの互換性についても考慮が必要。~
-ロックの互換性 (データベース エンジン)~
http://msdn.microsoft.com/ja-jp/library/ms186396.aspx
また、指定された分離レベルを実現するため、~
また、指定された[[分離レベル>#f0ab9db5]]を実現するため、~
トランザクション中のロックがホールドされるか？~
・・・などで、ロックの動きが変わってきます。
・・・などで、ロックの動きが変わってくる。
**分離レベル [#f0ab9db5]
-分離レベルについて~
http://msdn.microsoft.com/ja-jp/library/ms378149.aspx
-参考
--[[DBMSのロック・分離戦略と同時実行制御]]
--[[SQL Server のロックのエスカレーション]]
-[[DBMSのロック・分離戦略と同時実行制御]]
*確認方法 [#i2f35481]
こちら（[[SQL Server 問題の分析方法]]）も参考にして下さい。
こちら（[[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]
ロック タイムアウトであった場合は、以下のＨＰにある方法でロック状況を確認すると良い。
-＠IT > データを効率的に処理するエッセンスを教えます~
' > 連載：SQL実践講座（28・最終回） － SQL Serverで「デッドロック」を回避する~
http://www.atmarkit.co.jp/fnetwork/rensai/sql28/sql1.html
--以下はロック状況を確認するsp_lock_detailストアドプロシージャの作成スクリプトをコピペ利用可能にしたもの。
--このsp_lock_detailストアドプロシージャは、次の１ステートメントで実行可能である。
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を使用できそうです。~
で紹介されている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を使って自己解決するバージョンです。
上記の「エンティティ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を確認できます。~
[[SQLトレース>SQLプロファイラ（SQLトレース）]]を取って問題を起こしているSQLを確認できる。~
以下の様な問題に起因していることが多いです。
以下の様な問題に起因していることが多い。
-スキャンにより広範囲にロックがかかる。
-ロック・エスカレーションにより広範囲にロックがかかる。
-なお、デッドロック チェーンは基本的に直ちに検出されるので、~
プログラミングにおけるデッドロックのように、~
ハングアップ（ロック・タイムアウト）にはなりません。
ハングアップ（ロック・タイムアウト）にはならない。
よくあるパターンに、~
-テーブル・スキャンによる ロック待ち~
http://www.shoeisha.com/mag/windev/pdf/870602/windev0602_129_SQLServer.pdf
-暗黙の型変換によるインデックス・スキャンによる、~
インデックス・ロック（広範囲にロックがかかる）
があります。
がある。
また、統計情報が更新されていないと、~
インデックス・シークが適用されず、~
-インデックス・スキャン
-テーブル・スキャン
により、広範囲にロックがかかることがあります。
により、広範囲にロックがかかることがある。
なお、統計情報の更新タイミングについては、
-[[SQL Server のオプティマイザ]]
を参照して下さい。
を参照にできる。
*こんな単純なSQLでもブロッキングになる。 [#b0e7e0f6]
Oracleなど、[[多バージョン法（MultiVersion Concurrency Control:MVCC)>DBMSのロック・分離戦略と同時実行制御#z1614545]]のDBMSに慣れてしまうと、~
驚くことになりますが、以下の非常に簡単な２つのトランザクションで、ブロッキングが発生します。
*対策 [#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に慣れると、~
驚くことになるが、以下の非常に簡単な２つのトランザクションで、ブロッキングが発生する。
***トランザクション１（先発） [#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
-トランザクション２（後発）
***トランザクション２（後発） [#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を含む）にブロッキングされる。」
というケースもありました（本番環境では再現せずでした）。
というケースもあった（本番環境では再現せず）。
なお、[[SQL Serverの動作をMVCCに変更することも可能>DBMSのロック・分離戦略と同時実行制御#we9e7704]]です。
**逆に、これでブロッキングしない。 [#f1f70626]
使用するインデックスが異り、ブロッキングしなくなる。
*参考情報 [#d3618988]
***トランザクション１（先発） [#dfa97441]
SELECT * FORM T WITH(UPDLOCK) WHERE PK=1
***トランザクション２（後発） [#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]], [[:障害対応]], [[:性能]], [[:デバッグ]]
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
