- 追加された行はこの色です。
- 削除された行はこの色です。
[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]
「[[マイクロソフト系技術情報 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は参照処理にもロック(共有ロック)を~
かけるということを知っておくことが重要です。
ロックの種類は
-DMLの種類
-分離レベルの指定
によって変わってきます。(ただし、DDLによるロックもある)~
各ロックの互換性についても考慮が必要です。~
-ロックの互換性 (データベース エンジン)~
http://msdn.microsoft.com/ja-jp/library/ms186396.aspx
また、指定された分離レベルを実現するため、~
トランザクション中のロックがホールドされるか?~
・・・などで、ロックの動きが変わってきます。
-分離レベルについて~
http://msdn.microsoft.com/ja-jp/library/ms378149.aspx
-参考
--[[DBMSのロック・分離戦略と同時実行制御]]
--[[SQL Server のロックのエスカレーション]]
*確認方法 [#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 のオプティマイザ]]
を参照して下さい。
*案外知られていない [#b0e7e0f6]
以下の2つのトランザクションで、ブロッキングされます。
ポイントは、参照処理が、インデックスをキー範囲ロックしている点です。
-トランザクション1(先発)
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(後発)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM [Northwind].[dbo].[Orders]
WHERE [OrderID] BETWEEN 11070 AND 11080
--COMMIT TRANSACTION
--ROLLBACK TRANSACTION
こちらが後発なので、共有ロックをホールドしなくてもブロックされる。
このため、
-メモリの少ないクライアントOS上のExpress Edition等で、
-クエリ・プランがインデックス・スキャンを選択した場合、
-主キーでのSELECTが、主キーのキー範囲ロックになってしまい、
-他の排他ロック(INSERTやDELETEを含む)にブロッキングされる
というケースもありました(本番環境では再現せずでした)。
*参考情報 [#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]], [[:障害対応]], [[:性能]], [[:デバッグ]]