わたろぐ

仕事、読書、ガジェット、グルメ、写真、旅行など雑多な備忘

デッドロックを発生させてログを拾ってみる

SQLServer SQL SQLServer

先日、DBサーバーSQLの処理に時々時間がかかってしまうという問題が発生した。 おそらくデッドロックが原因ではないのだが、「そういえば、デッドロックのログってどうなってんだ？」って話になって、調べてみるとトレースフラグなるものを設定しておかなければ、デッドロックが発生してもログに残らないらしい。

トレースフラグはSQLServer Management Studioのクエリウィンドウから、以下のコマンドを実行すれば良い。

-- SQLServerログにデッドロックのログを出力する
DBCC TRACEON(1204,-1)

-- エラーログにデッドロックのログを出力する
DBCC TRACEON(3605,-1)

-- トレースフラグの設定確認
DBCC TRACESTATUS

-1をはグローバルフラグで、このオプションをつけなければセッションを終了したらトレースフラグはオフになってしまう。

これでログを採る準備は完了。 デッドロックが発生すればログを出力してくれる。はず。。。

気になったので、自分でデッドロックを起こしてみた。 方法としては、一つのテーブルに対して、複数のセッションから1行ずつアップデートをかけ、 デッドロックを発生させる。 まずはテーブルの作成から。簡単なテーブルと、レコードが2件あれば良い。

--テーブルの作成
CREATE TABLE T_TEST
(
  ID INT NOT NULL,
  VALUE CHAR(2) NOT NULL
)
GO

--テストデータを入れる
INSERT INTO T_TEST VALUES
(
  1,
  '11'
)

INSERT INTO T_TEST VALUES
(
  2,
  '22'
)

-- 一意キーをセットする
ALTER TABLE T_TEST
ADD CONSTRAINT PK_T_TEST
PRIMARY KEY(ID)
GO

COMMIT

--データが２レコード表示される
SELECT * FROM T_TEST

そして、SQLServerManagementStudioをもう一つ立ち上げる。 片方のSQLServerManagementStudioをA、もう片方をBとする。 まずはAにて以下のSQLを実行。

BEGIN TRANSACTION

UPDATE T_TEST SET VALUE = '11' WHERE ID = 1

次にBにて以下のSQLを実行

BEGIN TRANSACTION

UPDATE T_TEST SET VALUE = '22' WHERE ID = 2

これでID=1の行はセッションAのロックがかけられ、ID=2にはセッションBのロックがかけらた状態に。

コミットはせず、続けてセッションAにて以下のSQLを実行。

UPDATE T_TEST SET VALUE = '222' WHERE ID = 2

するとID=2のレコードにはセッションBのロックがかかっているため、解放待ちとなる。

ここでセッションBにて以下のSQLを実行する。

UPDATE T_TEST SET VALUE = '111' WHERE ID = 1

このSQLはセッションAの解放待ちとなるため、ここでデッドロックが発生。 数秒後(設定による？)に片方のSQLは実行され、もう片方のトランザクションはロールバックされる。

ログの確認方法は、オブジェクトエクスプローラにて 管理」→「SQL Serverログ」→「現在」で確認することができる。 少し分かりにくいが、Node1、Node2がそれぞれのセッションを表しているようで、それぞれどのSQLを発行した際にデッドロックとなったか、どちらをロールバックしたかを確認することができる。

ちなみに、トレースフラグの解除は以下のコマンドをクエリウィンドウから実行する。

-- SQLServerログにデッドロックのログを出力する
DBCC TRACEOFF(1204,-1)

-- エラーログにデッドロックのログを出力する
DBCC TRACEOFF(3605,-1)

-- トレースフラグの設定確認
DBCC TRACESTATUS