[SQL Server] デッドロックのトレース方法

SQL Serverではロックモニタスレッドによってデッドロックが自動検出されます。

デッドロックが検出された場合、デッドロックの対象となったスレッドのプロセスのいずれか一方がキャンセルされ、以下のエラーが返されます。

サーバー : メッセージ 1205、レベル 13、状態 50、行 1 トランザクション (プロセス ID 54) が、lock リソースでほかのプロセスとデッドロックしました。トランザクションがデッドロックの対象として選択されています。トランザクションを再実行してください。

ただし、返される情報はこれだけなので、何が原因でデッドロックが発生したのかまではわかりません。

デッドロックの原因を知りたい場合は、事前に以下のコマンドを実行し、トレース機能をＯＮにしておかなくてはなりません。

DBCC TRACEON (1204)

トレース機能をＯＮにした状態で、デッドロックが発生すると、上記のエラーメッセージ以外に、エラーログファイルに以下のようなトレース情報が出力されます。

2005-02-17 11:02:45.20 spid4     
Deadlock encountered .... Printing deadlock information
2005-02-17 11:02:45.20 spid4     
2005-02-17 11:02:45.20 spid4     Wait-for graph
2005-02-17 11:02:45.20 spid4     
2005-02-17 11:02:45.20 spid4     Node:1
2005-02-17 11:02:45.20 spid4     RID: 8:1:557:1                 CleanCnt:1 Mode: X Flags: 0x2
2005-02-17 11:02:45.20 spid4      Grant List 0::
2005-02-17 11:02:45.20 spid4        Owner:0x42bdf4e0 Mode: X        Flg:0x0 Ref:0 Life:...
2005-02-17 11:02:45.20 spid4        SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 1
2005-02-17 11:02:45.21 spid4        Input Buf: Language Event: UPDATE TABLE1
		SET COL2 = 'A'
		WHERE COL1 = 1


2005-02-17 11:02:45.21 spid4      Requested By: 
2005-02-17 11:02:45.21 spid4        ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:...
2005-02-17 11:02:45.21 spid4     
2005-02-17 11:02:45.21 spid4     Node:2
2005-02-17 11:02:45.21 spid4     RID: 8:1:555:0                 CleanCnt:1 Mode: X Flags: 0x2
2005-02-17 11:02:45.21 spid4      Grant List 0::
2005-02-17 11:02:45.21 spid4        Owner:0x42bdf2c0 Mode: X        Flg:0x0 Ref:0 Life:...
2005-02-17 11:02:45.21 spid4        SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 1
2005-02-17 11:02:45.21 spid4        Input Buf: Language Event: UPDATE TABLE2
		SET COL2 = 'bbb'
		WHERE COL1 = 2


2005-02-17 11:02:45.21 spid4      Requested By: 
2005-02-17 11:02:45.21 spid4        ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:...
2005-02-17 11:02:45.21 spid4     Victim Resource Owner:
2005-02-17 11:02:45.21 spid4      ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:...

この情報の見方についてはBooks Onlineなどで解説されていますが、解説を見なくともデッドロック対象となっているスレッドが保持しているロックとそのロックの解放を待っているロック要求がリストされているということは大体想像が付きます。

この情報を元にアプリケーションの作り（更新順序など）を見直してデッドロックの発生を回避させればいいということです。

なお、デッドロック発生時にはどちらか一方の要求がキャンセルされますが、この時キャンセルされるのは”やり直しに最も手間のかからないトランザクションを実行しているスレッド”のプロセスということになっています。

おそらく更新対象のテーブル数やレコード数が多いとか少ないとかいう基準で決定しているのだと思われますが、セッションに対して以下のコマンドを実行することによってデッドロック発生時に優先的に要求をキャンセルさせるということも可能なようです。

SET DEADLOCK_PRIORITY LOW