「[[マイクロソフト系技術情報 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は参照処理にもロック(共有ロック)を~ かけるということを知っておくことが重要。 **ロックの種類 [#t4f727ff] ロックの種類は -DMLの種類 -[[分離レベル>#f0ab9db5]]の指定 によって変わってくる。(ただし、DDLによるロックもある)~ **ロックの互換性 [#le819818] 各ロックの互換性についても考慮が必要。~ -ロックの互換性 (データベース エンジン)~ http://msdn.microsoft.com/ja-jp/library/ms186396.aspx また、指定された[[分離レベル>#f0ab9db5]]を実現するため、~ トランザクション中のロックがホールドされるか?~ ・・・などで、ロックの動きが変わってくる。 **分離レベル [#f0ab9db5] -分離レベルについて~ http://msdn.microsoft.com/ja-jp/library/ms378149.aspx -[[DBMSのロック・分離戦略と同時実行制御]] *確認方法 [#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 のオプティマイザ]] を参照にできる。 *こんな単純なSQLでもブロッキングになる。 [#b0e7e0f6] Oracleなど、[[多バージョン法(MultiVersion Concurrency Control:MVCC)>DBMSのロック・分離戦略と同時実行制御#z1614545]]のDBMSに慣れると、~ 驚くことになるが、以下の非常に簡単な2つのトランザクションで、ブロッキングが発生する。 **トランザクション1(先発) [#fd15b8c0] 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(後発) [#ed307e13] SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; SELECT * FROM [Northwind].[dbo].[Orders] WHERE [OrderID] BETWEEN 11070 AND 11080 --COMMIT TRANSACTION --ROLLBACK TRANSACTION **ポイント [#hc7510b8] ポイントは、後発の参照処理が、インデックスをキー範囲ロックしようとしている点。 特に、メモリの少ないクライアントOS上のSQL Server Express Edition等で、 >「[[クエリ・プランがインデックス・スキャンを選択した場合>SQL Server のオプティマイザ]]、~ 主キーでのSELECTが、主キーのキー範囲ロックになってしまい、~ 他の排他ロック(INSERTやDELETEを含む)にブロッキングされる。」 というケースもあった(本番環境では再現せず)。 *対策 [#lb9591d0] **適切な[[分離レベル>#f0ab9db5]]を選択する。 [#i5623668] **MVCCに変更する。 [#s977682f] SQL Server2005から、動作を[[MVCC>DBMSのロック・分離戦略と同時実行制御#we9e7704]]に変更することも可能。 **エスカレーションを抑止する。 [#hbe78258] [[SQL Server のロックのエスカレーション]]の発生を抑止できる。 **ロックをカスタマイズする。 [#l6012292] ***ロック ヒント [#sd92ab00] テーブル ヒントにロック手法をWITH (ROWLOCK)などと指定する。 -テーブル ヒント (Transact-SQL) | Microsoft Docs~ https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-table -行ロックの動作についてお試ししてみる - 都内で働くSEの技術的なひとりごと~ http://ryuchan.hatenablog.com/entry/2016/08/28/162627 ***sp_indexoption [#e68065e6] アクセス パターンが一定していることがわかっている場合、~ ページまたは行のロックを禁止することが効果的なケースもある。 -インデックスのロックのカスタマイズ~ https://technet.microsoft.com/ja-jp/library/ms189076.aspx -sp_indexoption (TRANSACT-SQL)~ https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-indexoption-transact-sql *参考情報 [#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]], [[:障害対応]], [[:性能]], [[:デバッグ]]