![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
SQL Serverは参照処理にもロック（共有ロック）を
かけるということを知っておくことが重要です。
ロックの種類は
によって変わってきます。（ただし、DDLによるロックもある）
各ロックの互換性についても考慮が必要です。
また、指定された分離レベルを実現するため、
トランザクション中のロックがホールドされるか？
・・・などで、ロックの動きが変わってきます。
こちら（SQL Server 問題の分析方法）も参考にして下さい。
以下のSQLで、
ロックをホールドしておきます。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT * FROM Shippers WHERE ShipperID = 1 --COMMIT
SQL Serverでロック タイムアウト値を設定する方法は以下のとおりである。
※ この設定は、接続確立毎に実行する必要がある。
SET LOCK_TIMEOUT timeout_period
現在のロック タイムアウト値を調べるには、次に示す@@LOCK_TIMEOUT関数を実行する。
SELECT @@lock_timeout;
ロック タイムアウトであった場合は、以下のＨＰにある方法でロック状況を確認すると良い。
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
ロック状況の監視には、
で紹介されている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名を入力する。
SQL Server 2005 以降では、代わりに sys.dm_tran_locks 動的管理ビューを使用
上記の「エンティティ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';
SQLトレースを取って問題を起こしているSQLを確認できます。
以下の様な問題に起因していることが多いです。
よくあるパターンに、
があります。
また、統計情報が更新されていないと、
インデックス・シークが適用されず、
により、広範囲にロックがかかることがあります。
なお、統計情報の更新タイミングについては、
を参照して下さい。
Oracleなど、多バージョン法（MultiVersion Concurrency Control:MVCC)のDBMSに慣れてしまうと、
驚くことになりますが、以下の非常に簡単な２つのトランザクションで、ブロッキングが発生します。
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
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; SELECT * FROM [Northwind].[dbo].[Orders] WHERE [OrderID] BETWEEN 11070 AND 11080 --COMMIT TRANSACTION --ROLLBACK TRANSACTION
ポイントは、後発の参照処理が、インデックスをキー範囲ロックしようとしている点です。
特に、メモリの少ないクライアントOS上のSQL Server Express Edition等で、
「クエリ・プランがインデックス・スキャンを選択した場合、
主キーでのSELECTが、主キーのキー範囲ロックになってしまい、
他の排他ロック（INSERTやDELETEを含む）にブロッキングされる。」
というケースもありました（本番環境では再現せずでした）。
なお、SQL Serverの動作をMVCCに変更することも可能です。
Tags: :データアクセス, :SQL Server, :障害対応, :性能, :デバッグ
