SQL Server でデッドロックを検知する方法

概要

SQL Server でデッドロックを検知する方法を紹介します。

内容

準備
デッドロックを検知できるようにするために、DBCC を使用してトレースを有効化します。

DBCC TRACEON (1204,-1) DBCC TRACEON (1222,-1)

デッドロックの検知
0. テスト用のテーブルを作成し、データーを入力しておきます。

CREATE TABLE TestTableA(      Code    integer,      Descr    VARCHAR(100),      Qty        INT ) CREATE TABLE TestTableB(      Code    integer,      Descr    VARCHAR(100),      Qty        INT )

INSERT INTO dbo.TestTableA(      Code,      Descr,      Qty ) VALUES      (1,'TORX T10', 1),      (2, 'TORX T20', 2) INSERT INTO dbo.TestTableB(      Code,      Descr,      Qty ) VALUES      (3,'TORX T30', 2),      (4, 'TORX T40', 3)

1. デッドロックを作成するため、以下のコマンドを実行します。

BEGIN TRAN UPDATE dbo.TestTableA SET Qty = 100 WHERE Code = 1 WAITFOR DELAY '00:00:30' UPDATE dbo.TestTableB SET Qty = 200 WHERE Code = 3

2. 他のセッションで以下のコマンドを実行し、デッドロックを発生させます。

BEGIN TRAN WAITFOR DELAY '00:00:10' UPDATE dbo.TestTableB SET Qty = 300 WHERE Code = 3 UPDATE dbo.TestTableA SET Qty = 400 WHERE Code = 1

3. デッドロックが発生したという旨のエラーが表示されます。

メッセージ 1205、レベル 13、状態 45、行 4 トランザクション (プロセス ID 52) が、ロック 個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。

4. SQL Server ログにもデッドロックが発生した旨のエラーが記録されます。



終了処理
有効化したトレースを無効にするには、以下のコマンドで無効化できます。

DBCC TRACEOFF (1204,-1) DBCC TRACEOFF (1222,-1)

---

情報元
SQL Server, How to find deadlocks: the easy way DBCC TRACEON (1204, 1222,-1) (英語)