Open棟梁Project - マイクロソフト系技術情報 Wiki [[戻る>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 *確認方法 [#i2f35481] こちら([[SQL Server 問題の分析方法]])も参考にして下さい。 **SQLトレースを使用して確認 [#c295bf32] SQLトレースを取って問題を起こしているSQLを確認できます。~ 以下の様な問題に起因していることが多いです。 -スキャンにより広範囲にロックがかかる。 -ロック・エスカレーションにより広範囲にロックがかかる。 -なお、デッドロック チェーンは基本的に直ちに検出されるので、~ プログラミングにおけるデッドロックのように、~ ハングアップ(ロック・タイムアウト)にはなりません。 よくあるパターンに、~ -テーブル・スキャンによるロック待ち -暗黙の型変換によるインデックス・スキャンによる、~ インデックス・ロック(広範囲にロックがかかる) があります。 -テーブル・スキャンによる ロック待ち~ http://www.shoeisha.com/mag/windev/pdf/870602/windev0602_129_SQLServer.pdf また、統計情報が更新されていないと、~ インデックス・シークが適用されず、~ -インデックス・スキャン -テーブル・スキャン により、広範囲にロックがかかることがあります。 なお、統計情報の更新タイミングについては、 -[[SQL Server のオプティマイザ]] を参照して下さい。 **SQLを使用して確認 [#ed9b7c2d] 以下のSQLで、 -トランザクションを開始し -参照処理することで ロックをホールドしておきます。 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT * FROM Shippers WHERE ShipperID = 1 --COMMIT ***動的管理ビュー [#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/ *案外知られていない [#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