Open棟梁Project - マイクロソフト系技術情報 Wiki
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;
ロック タイムアウトであった場合は、以下のHPにある方法でロック状況を確認すると良い。
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を確認できます。
以下の様な問題に起因していることが多いです。
よくあるパターンに、
があります。
また、統計情報が更新されていないと、
インデックス・シークが適用されず、
により、広範囲にロックがかかることがあります。
なお、統計情報の更新タイミングについては、
を参照して下さい。
以下の2つのトランザクションで、ブロッキングされます。
ポイントは、参照処理が、インデックスをキー範囲ロックしている点です。
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
こちらが後発なので、共有ロックをホールドしなくてもブロックされる。
このため、
というケースもありました(本番環境では再現せずでした)。
Tags: :データアクセス, :SQL Server, :障害対応, :性能, :デバッグ