デッドロックの相手を調べる方法

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

このエラーが出た場合は、どう対処すればよいだろうか？
順を追って説明する。

■デットロックとは
デットロックとは、相手（別のＳＱＬ）と自分（自分が流したＳＱＬ）とで、
ロックをかけ合い、お互い相手を待つことである。

これは、どういう事かというと、処理が一切できなくなることを意味する。
データベースは、それを自動的に解除する仕組みを持っていて、心配はいらない。

しかし、片方のＳＱＬがロールバックされてしまう。どちらがロールバックされるかだが
コストが小さい（戻す処理が少ない）方が優先されるようである。

上記のエラーが出た場合、デットロックが起きてしまったことは分かるのだが、
SQL Serverの初期設定では、デットロックの相手が分からない。

■デットロックの相手を調べる

ログを残すには、以下のコマンドを実行する。
---------------------------------------------------
DBCC TRACEON(1204, 3605, -1)
GO
---------------------------------------------------
このコマンドを実行した後に、デットロックが発生すると
ログファイルに記録されるようになる。


■デットロックの再現方法

デットロックは、タイミングによって起きたり起きなかったりする。
これでは、デットロックがどんな風に起きたのか、スッキリしないであろう。
ここでは、シンプルな条件で実際にデットロックを起こしてみる。

以下のスクリプトをテスト環境などで流してもらいたい。
まず、デットロックが起こせる環境を準備する。
---------------------------------------------------

--テーブルの作成
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

---------------------------------------------------
ここからが、デットロックの再現方法である

SQL Server Management Studeioを起動する
また別に、SQL Server Management Studeioを起動する

初めに起動した方を「Ａ」、後から起動した方を「Ｂ」と呼ぶとする。

まず、「Ａ」で以下を実行
---------------------------------------------------
BEGIN TRANSACTION

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

次に、「Ｂ」で以下を実行
---------------------------------------------------
BEGIN TRANSACTION

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

さらに、「Ａ」で以下を実行
---------------------------------------------------
UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
---------------------------------------------------

最後に、「Ｂ」で以下を実行
---------------------------------------------------
UPDATE T_TEST SET VALUE = '11' WHERE ID = 1
---------------------------------------------------

ここで、例のデットロックエラーが発生したはずである。
もし出なかった場合は、「ROLLBACK」コマンドを複数回実行して
トランザクションが開始されていないことを確認後、再実行してほしい。


■デットロックをログで確認する

SQL Server Management Studeioを起動する
オブジェクトエクスプローラ内で、
「管理」→「SQL Serverログ」→「現在」をクリックする

画面の上にある「検索」ボタンを押下する
検索する文字列を「Deadlock」と入力して検索を実行する

検索でヒットしたレコードから上に眺めていくと
「Node:1」「Node:2」が見つかるはずである。これは先ほど
SQL Server Management Studeioで実行した「Ａ」「Ｂ」のことである。

実際のログデータを抜き出してみた。
★の箇所に、デットロックしたＳＱＬが記録されている
---------------------------------------------------
2011-12-XX 18:37:07.06 ResType:LockOwner Stype:'OR'Xdes:0x83FDB200 Mode: X SPID:74
2011-12-XX 18:37:07.06 Requested By:
★2011-12-XX 18:37:07.06 Input Buf: Language Event: UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
2011-12-XX 18:37:07.06 SPID: 76 ECID: 0 Statement Type: UPDATE Line #: 2
2011-12-XX 18:37:07.06 Owner:0x8016E120 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:76
2011-12-XX 18:37:07.06 Grant List 3:
2011-12-XX 18:37:07.06 KEY: 5:72057594088062976 (010086470766) CleanCnt:2 Mode:X
2011-12-XX 18:37:07.06 Node:2
2011-12-XX 18:37:07.06 ResType:LockOwner Stype:'OR'Xdes:0x8549BA00 Mode: X SPID:76
2011-12-XX 18:37:07.06 Requested By:
★2011-12-XX 18:37:07.06 Input Buf: Language Event: UPDATE T_TEST SET VALUE = '11' WHERE ID = 1
2011-12-XX 18:37:07.06 SPID: 74 ECID: 0 Statement Type: UPDATE Line #: 2
2011-12-XX 18:37:07.06 Owner:0x8016FDE0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
2011-12-XX 18:37:07.06 Grant List 3:
2011-12-XX 18:37:07.06 KEY: 5:72057594088062976 (020068e8b274) CleanCnt:3 Mode:X
2011-12-XX 18:37:07.06 Node:1
---------------------------------------------------

■デットロック対策

これまでに実践したものを書き出してみる

・データへのアクセス順番をそろえる（Order byでソートしてアクセス）
・ロックの単位をなるべく小さくする（一意キー単位での更新）
・トランザクションはなるべく短くする（細かくCOMMITする）
・ロックに影響を受けにくくする「SET READ_COMMITTED_SNAPSHOT ON」
・UPDATEを実行する前に、あらかじめ対象データを排他ロックさせる
SELECT ID FROM T_TEST WITH ( ROWLOCK, UPDLOCK ) WHERE ID = 1 --行ロック＋更新ロック
・デットロックは、起きてしまうものと楽観的にとらえ、起きたらやり直しさせる
ERROR_NUMBER() = 1205 でデットロックと判断できるため、ループして再実行
・複数端末で同時アクセスされないようアプリ自体で排他する

■最後に

デットロックは、時としてとんでもないトラブルの原因となる。
さらに厄介なのは、再現が難しく、簡単には修正できないことも多い。
処理量が増えてくると急に頻発し、担当者を長期間悩ませたりする。

こんなにもイヤな状況に陥らないためには、テーブル設計やコーディング段階で
「ここではデットロックは起きないだろうか？」と自問自答しながら、
ＳＱＬレベルで意識して開発していくことが極めて重要である。