SQL Server 長時間排他ロックしているセッションを探し出せ！
デットロックが発生した場合、原因となっているセッションを切るしかない。
そのために、SQL Serverでは、sp_lock を実行して確認すればいい、というところまではだいたい誰もがわかっている。
しかし、実環境では教科書通りに行かないものだ。
まず、WEBシステムだと100を超えるセッションがあり、sp_lockを実行すると、とても目でいちいち確認していられない数のデータが表示される場合がある。
しかも、こういう時は予断を許さない。解決に向けて一刻を争う。
そこで手早く確認するための便利スクリプトを紹介する。
これはsp_lockの実行結果を、実行日時とともにテーブルに落とす方法だ。
このテーブルはローカルテンプテーブルを使っているところがミソだ。
1) テンプテーブルを作成する。
create table #splock(spid int,dbid int,objid int,indid int,type varchar(100),resource varchar(100),mode varchar(100), status varchar(100),checktime datetime default getdate())
2) sp_lock を実行し、結果をテーブルに保存する。
insert into #splock(spid,dbid,objid,indid,type,resource,mode,status) exec sp_lock
これを何回か実行する。
3) テンプテーブルを検索し長時間排他ロックしているセッションを見つける。
select * from #splock where mode='X' order by checktime asc
select * from #splock where mode='IX' order by checktime asc
4) 排他ロックされているオブジェクトを見つけたら、そのオブジェクトに対して待ち状態 waiting のセッションがあることを確認する。
select * from #splock where objid='オブジェクトID' order by checktime asc
5) 排他ロックしているセッション sipd を切る
kill セッションID