「メッセージ 1205、レベル 13、状態 51、行 2」
「トランザクション (プロセス ID 99) が、ロック 個のリソースで
他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。
トランザクションを再実行してください。」
このエラーが出た場合は、どう対処すればよいだろうか?
順を追って説明する。
■デットロックとは
デットロックとは、相手(別のSQL)と自分(自分が流したSQL)とで、
ロックをかけ合い、お互い相手を待つことである。
これは、どういう事かというと、処理が一切できなくなることを意味する。
データベースは、それを自動的に解除する仕組みを持っていて、心配はいらない。
しかし、片方のSQLがロールバックされてしまう。どちらがロールバックされるかだが
コストが小さい(戻す処理が少ない)方が優先されるようである。
上記のエラーが出た場合、デットロックが起きてしまったことは分かるのだが、
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
--データが2レコード表示される
SELECT * FROM T_TEST
---------------------------------------------------
ここからが、デットロックの再現方法である
SQL Server Management Studeioを起動する
また別に、SQL Server Management Studeioを起動する
初めに起動した方を「A」、後から起動した方を「B」と呼ぶとする。
まず、「A」で以下を実行
---------------------------------------------------
BEGIN TRANSACTION
UPDATE T_TEST SET VALUE = '11' WHERE ID = 1
---------------------------------------------------
次に、「B」で以下を実行
---------------------------------------------------
BEGIN TRANSACTION
UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
---------------------------------------------------
さらに、「A」で以下を実行
---------------------------------------------------
UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
---------------------------------------------------
最後に、「B」で以下を実行
---------------------------------------------------
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で実行した「A」「B」のことである。
実際のログデータを抜き出してみた。
★の箇所に、デットロックしたSQLが記録されている
---------------------------------------------------
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 でデットロックと判断できるため、ループして再実行
・複数端末で同時アクセスされないようアプリ自体で排他する
■最後に
デットロックは、時としてとんでもないトラブルの原因となる。
さらに厄介なのは、再現が難しく、簡単には修正できないことも多い。
処理量が増えてくると急に頻発し、担当者を長期間悩ませたりする。
こんなにもイヤな状況に陥らないためには、テーブル設計やコーディング段階で
「ここではデットロックは起きないだろうか?」と自問自答しながら、
SQLレベルで意識して開発していくことが極めて重要である。