![[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ストアドプロシージャ †ロック タイムアウトであった場合は、以下のＨＰにある方法でロック状況を確認すると良い。
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を使用できる。 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名を入力する。
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';
SQLトレースを使用して確認 †SQLトレースを取って問題を起こしているSQLを確認できる。 以下の様な問題に起因していることが多い。
よくあるパターンに、
がある。 また、統計情報が更新されていないと、
により、広範囲にロックがかかることがある。 なお、統計情報の更新タイミングについては、 を参照にできる。 対策 †適切な分離レベルを選択する。 †MVCCに変更する。 †SQL Server2005から、動作をMVCCに変更することも可能。 エスカレーションを抑止する。 †SQL Server のロックのエスカレーションの発生を抑止できる。 ロックをカスタマイズする。 †ロック ヒント †テーブル ヒントにロック手法をWITH (ROWLOCK)などと指定する。
sp_indexoption †アクセス パターンが一定していることがわかっている場合、
事例 †こんな単純な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 * FORM T WITH(UPDLOCK) WHERE PK=1 トランザクション２（後発） †SELECT * FORM T WITH(UPDLOCK) WHERE PK=1 AND XXX=YYY ポイント †上記で、PK=1がロック非互換でブロッキングされないのは、 使用するインデックスが、
と、使用するインデックスが異り、ブロッキングしなくなる。 参考 †
Tags: :データアクセス, :SQL Server, :障害対応, :性能, :デバッグ |
