- PR -

デッドロックでデータベース全体がスローダウンするのはなぜ？(SQL Server)

1
投稿者投稿内容
高松
会議室デビュー日: 2005/10/26
投稿数: 4
投稿日時: 2005-10-26 14:30
使用環境: SQL Server 2000 SP4 , Windows 2003 Server

　こんにちは。データベース管理者をしています。
SQL Serverでデッドロックが発生すると、対象では
ないテーブルへのアクセスまで遅くなる現象が確認
できています。遅くなる原因と現象のつじつまが合
うような理由を考えているのですが、正直なところ
専門的な言葉が出てきません。
　「データベースの内部的な動作」という観点から
、みなさんのご意見をいただけないでしょうか。

私が推測している理由は、以下の３点です。
　(１)デッドロックによって、SQL Server プロセスが
エラー情報のダンプに占有されるため。
　(２)syslockinfo システムテーブルに大量の
ロック情報が格納されるため。
　(３)ロックリソースの解放とトランザクションの
ロールバックに、プロセスが占有されるため。

どうぞよろしくおねがいします。
matu_tak
会議室デビュー日: 2003/02/06
投稿数: 13
投稿日時: 2005-10-26 19:00
> SQL Serverでデッドロックが発生すると、対象では
> ないテーブルへのアクセスまで遅くなる現象が確認
> できています。
>
どのぐらいの頻度でデッドロックが発生しているのでしょうか？

>(１)デッドロックによって、SQL Server プロセスが
>エラー情報のダンプに占有されるため。
>
エラー情報のダンプは、トレースフラグ 1204 を有効にした場合ですね。

SQL Server 技術情報 - デッドロックの解決方法
http://support.microsoft.com/kb/832524

あと、ちょっと古いですが、私が昔に作った ppt も参考になるかもしれません。
PASSJ Conference 2004「パフォーマンス チューニング」セッション資料
http://www.sqlpassj.org/conf2004/dl.aspx#Expert03

>(２)syslockinfo システムテーブルに大量の
>ロック情報が格納されるため。
>
これはデッドロックが発生したときに、

SELECT * FROM syslockinfo
や
sp_lock

などを実行して、件数を確認しましたでしょうか？
もう少し詳しい状況が分ると返答がつきやすくなると思います
高松
会議室デビュー日: 2005/10/26
投稿数: 4
投稿日時: 2005-10-26 20:16
ご意見ありがとうございます。
起動パラメータの「-T3605 」をまだやっておりませんでした。
これから検証してみたいと思います。
また、デッドロックの状況をまとめました。
長文になってしまいましたが、どうぞよろしくお願いします。


一週間の統計で、デッドロックの発生頻度は、次のとおりです。
　・平均：２３回／日
　・最大：３０回／日
　・最小：１０回／日


デッドロックの解析は、次の手段を用いています。
　・トレースフラグ 1204 で、テーブルやページの情報を確認
　・sp_lockをデッドロック発生直後に自動実行してＳＱＬ文を確認
　・アプリケーションログから、同時実行しているプログラムを確認


デッドロックの傾向は、次のとおりです。
　・採番テーブルに多い。
　・ある特定のプログラムが同時実行している時に起こる。


すでに試している対策は、次のとおりです。
　・DBCC PINTABLE で、採番テーブルをキャッシュにいれる。
　・デッドロックの発生しているキーを１０個のレンジに分ける。
　　（たとえば、１〜１００万はプログラムＡ用、１０１万〜２００万はプログラムＢ用）


今後は、次の対策を考えています。
　・採番テーブルをキー毎に分割する。
　　(たとえば、出荷伝票専用採番テーブルと入荷伝票専用採番テーブルを用意する)
　　→採番テーブルに対して、ページロックが発生していたため、
　　　キーを別テーブルにすることで、ロックを軽減できると予想しています。

　・特定プログラム内での採番を別トランザクションにする。
　　→０．３秒かかるトランザクション内で採番処理をしていたのを、別トランザクション
　　　にして０．０１秒まで短くすることで、ロックを軽減できると予想しています。

※採番テーブルには、クラスタ化インデックスはありません。ヒープテーブルです。
「詳しさ」の方向性が違っていましたら、ご指摘ください。
よろしくおねがいします。
matu_tak
会議室デビュー日: 2003/02/06
投稿数: 13
投稿日時: 2005-10-26 21:07
> 起動パラメータの「-T3605 」をまだやっておりませんでした。
>
1204 を指定していれば、これはなくても大丈夫です。
また、KB にありますように

DBCC TRACEON (1204, -1)

とすれば、SQL Server サービスを再起動しないで済みます。
停止するときは、DBCC TRACEOFF (1204, -1) です。

また、デッドロックの原因が確認できたら、トレースフラグはオフ
にしておいたほうがよいです。

> デッドロックの傾向は、次のとおりです。
>　・採番テーブルに多い。
>
とのことですので、まずは採番テーブルのデッドロックを少なく
していくことが先決な気がしています。
採番テーブルは、

SELECT MAX(番号) FROM 採番テーブル

という形で使ってますでしょうか。
だとすると、

> 採番テーブルには、クラスタ化インデックスはありません。ヒープテーブルです。
>
これはまずいです。ヒープだとテーブルスキャンが発生しますので。
クラスタ化インデックスにしていれば、MAX 値を取得するときに、降順の TOP が
内部的に実行されるので、高速に値を取得できます（実行プランで確認可能です）

また、ヒープですと、

> ・デッドロックの発生しているキーを１０個のレンジに分ける。
>
これは無意味になります。
ヒープだと内部的なテーブルスキャンの発生によってロック待ちが発生してしまいますので。
おそらくこれが原因でデッドロックが多発しているのだと思います。
このデッドロックは、クラスタ化インデックスを作ることで回避できます。

また、採番テーブル取得時の分離レベルに Serializable や Repeatable Read
を使ったりしていないでしょうか？
これらの分離レベルを使うと、同時実行数が多い場合に簡単に変換デッドロック
が発生してしまいます。
これを回避するには、更新ロック（UPDLOCK）や排他（XLOCK）を使うようします。

ex)
BEGIN TRAN
SELECT MAX(番号) FROM 採番テーブル WITH(UPDLOCK)
MAX 値にプラス1
：
COMMIT

また、採番テーブルのように小さいテーブルで頻繁にアクセスされるテーブル
であれば、DBCC PINTABLE を実行しなくても、自動的にオンメモリ (ほぼ常時
バッファキャッシュに存在) にできると思います。
高松
会議室デビュー日: 2005/10/26
投稿数: 4
投稿日時: 2005-10-26 21:56
ご回答ありがとうございます。

簡略化していますが、下記のＳＱＬ文をストアドプロシージャにして採番しています。
BEGIN TRAN
UPDATE [採番テーブル] SET [採番値] = [採番値採番値]＋１
WHERE [採番値キー] = '採番キー'
SELECT @seq = [採番値] FROM [採番テーブル]
WHERE [採番値キー] = '採番キー'
COMMIT TRAN

ヒープテーブルの件は、了解しました。
クラスタ化インデックスにして検証したところ、デッドロックは減少しました。
あとは、ブロックをどのように調整すればよいか、というところまできました。

ありがとうございました。
1

スキルアップ／キャリアアップ（JOB@IT）