MS SQL Server でのロック処理は、特にハングアップしたりタイムアウトしたりし続けるクエリを実行しようとしている場合は、非常に頭の痛い問題です。トランザクションがスタックし、他のプロセスを遅延させ、応答速度の低下が連鎖的に発生することはよくあります。これは通常、コミットされていない、または完了していないトランザクションが原因です。たとえば、誰かがコミットまたはロールバックを忘れた場合など、SQL がロックを保持したままになっているのです。状況が悪化すると、クエリがフリーズしたり、完全にブロックされたりすることもあります。そのため、これらのロックを特定してクリアする方法を知っておくことは、非常に役立ちます。
特にサーバーが混雑している場合や、問題の原因となったトランザクションが不明な場合は、少し難しい場合があります。しかし幸いなことに、SQL Serverには原因究明に役立つツールやコマンドが組み込まれています。sysprocessesテーブルを確認したり、アクティビティモニターを使用したり、簡単なストアドプロシージャを作成したりすることで、かなりの手間を省くことができます。ただし、プロセスを強制終了する際は注意が必要です。killコマンドが何をしているのかよくわからないと、状況が悪化してしまうことがあります。それでも、テーブルやセッションのロックを強制的に解除する必要がある場合は、基本を理解しておくと、プロセス全体の謎が解けるでしょう。
MS SQL Server でロックを見つけて削除する方法
ロックの原因と確認方法
まず、なぜこれが役立つのでしょうか？それは、どのセッションやクエリがブロックを引き起こしているかがわかれば、何をターゲットにすべきかがわかるからです。例えば、あるプロセスが長時間停止したまま何かを実行しようとしていて、それがスタックしている場合、そのプロセスを強制終了するか、さらに詳しく調査することができます。クエリの応答が遅い場合や、SQL Serverがブロック率が高いことを報告している場合は、これらのコマンドが出発点となります。
したがって、ブロックされたプロセスを確認するには、SQL Server Management Studio (SSMS) でこれを実行します。
select cmd, spid, blocked, waittype, waittime, waitresource from sys.sysprocesses where blocked > 0ブロックされている実行中のすべてのプロセスと、それらをブロックしているプロセスの一覧が表示されます。列を見ると、セッションの待機時間が長すぎる場合（例えば数秒以上）、つまりロックの問題が継続しているかどうかが分かります。
特定のデータベースに絞り込みたい場合は、次のコマンドを実行します。
SELECT spid, blocked, dbid, lastwaittype, waittime FROM master.dbo.sysprocesses WHERE dbid = DB_ID('yourdatabase') AND blocked <> 0 ORDER BY waittime DESCここでは、プロセスID（SPID）、データベース、そして各プロセスの待機時間を確認できます。「ブロック」列には、どのプロセスが遅延を引き起こしているかが表示されます。しばらくブロックされているプロセスを見つけた場合は、以下のコマンドでそのセッションを調査することをお勧めします。
DBCC INPUTBUFFER(spid)これにより、実行された最後のクエリが正確に示されます。肩越しに覗き込んで何をしようとしていたかを確認するようなものです。
ロックの原因を追跡する
一部のロックはトランザクションツリー全体、つまり複数の子プロセスを持つ親プロセスによって発生します。ロックを保持している最初のプロセスを見つけるには、次のコマンドを実行します。
select * from master.dbo.sysprocesses where spid = your_spid先ほど確認したプロセスIDに置き換えてくださいyour_spid。通常、根本原因はblocked=0の最初のプロセス、つまりロックを保持しているプロセスです。設定によっては、このIDによってトランザクションが外部リソースを待機しているのか、それとも長いトランザクションのどこかでスタックしているのかを特定できます。
プロセスを強制終了してロックを解除する方法
これは少し厳しいかもしれませんが、場合によっては唯一の選択肢となることもあります。ロックの原因となっているプロセスを特定し、それを安全に終了できる場合は、以下を実行してください。
KILL spid例えば：
KILL 59一部のサーバーでは、プロセスを強制終了すると予期せぬ状態になったり、ロールバックが発生したりする可能性があることに注意してください。本番環境で作業している場合は、実行前に必ず確認してください。SQL Server は必要以上にプロセスを強制終了させる必要があるためです。プロセスが重要な処理を実行していないことが確実な場合、またはバックアップが確保されている場合にのみ、プロセスを強制終了することをお勧めします。
ストアドプロシージャによるロックチェックの自動化
面倒に感じたり、もっと早く監視したいと思ったりしていませんか？ロックの原因となったクエリを取得する、次のような小さなストアド プロシージャを作成できます。
CREATE PROCEDURE GetCurrentQueryCode @SPID int AS DECLARE @sql_handle varbinary(64) DECLARE @sql_text nvarchar(max) SELECT @sql_handle = sql_handle FROM sys.dm_exec_sessions WHERE session_id = @SPID SET @sql_text = (SELECT text FROM sys.dm_exec_sql_text(@sql_handle)) PRINT @sql_text 次に、次のコマンドを実行します。
EXEC GetCurrentQueryCode 51これにより、そのセッションで最後に実行されたSQLコマンドを簡単に確認できます。また、以下のコマンドでキャッシュからSQLを直接取得することも可能です。
select * from sys.dm_exec_sql_text(0xYOURSQLHANDLE)0xYOURSQLHANDLE以前のクエリから取得したバイナリハンドルはどこにありますか？これは複雑になることもありますが、高負荷のサーバーでロックの問題を診断する際に非常に役立ちます。
SQL Server Management Studioの組み込みツールの使用
もちろん、SSMSにはロックを視覚的に確認するための便利な方法がいくつかあります。サーバーを右クリックし、「アクティビティモニター」を開いて「プロセス」を展開します。すると、待機中のプロセス、ブロック中のプロセス、そしてその時間を示す一連のリクエスト情報が表示されます。特に「SUSPENDED 」状態を確認すると、その詳細が分かります。あるいは、 「レポート」 > 「すべてのブロック中のトランザクション」を選択することもできます。ブロックされたクエリ、SPID、ロックの原因の一覧が表示されます。状況がおかしい場合は、拡張イベントやSQL Server Profilerを有効にするのも便利ですが、こちらは少し高度な機能です。
結局のところ、ロックの管理とは、舞台裏で何が起こっているかを把握しつつ、混乱を招かずに適切な対応を取ることのバランスを取ることです。通常、ピンチの時は、迅速に特定して解除するアプローチが有効です。ただし、ロックが発生するには理由があり、解除するには単に待つだけの場合もあります。しかし、これらのコマンドを駆使しておけば、何かがすべてを遅らせている場合でも、状況を把握しやすくなります。