[解決済み] SQL Server 2005 のデッドロックを診断する

質問

Stack Overflow SQL Server 2005 データベースで、悪質ではあるが稀なデッドロック状態を確認しています。

プロファイラーをアタッチし、次のような方法でトレース プロファイルをセットアップしました。 デッドロックのトラブルシューティングに関するこの優れた記事 を使用してトレース プロファイルを設定し、多くの例をキャプチャしました。奇妙なことに デッドロックが発生している書き込みが 常に 同じ :

UPDATE [dbo].[Posts]
SET [AnswerCount] = @p1, [LastActivityDate] = @p2, [LastActivityUserId] = @p3
WHERE [Id] = @p0

もうひとつのデッドロックの文はさまざまですが、たいていは何らかの些細な、単純な を読む のようなものです。これはいつもデッドロックで殺されます。以下はその例です。

SELECT
[t0].[Id], [t0].[PostTypeId], [t0].[Score], [t0].[Views], [t0].[AnswerCount], 
[t0].[AcceptedAnswerId], [t0].[IsLocked], [t0].[IsLockedEdit], [t0].[ParentId], 
[t0].[CurrentRevisionId], [t0].[FirstRevisionId], [t0].[LockedReason],
[t0].[LastActivityDate], [t0].[LastActivityUserId]
FROM [dbo].[Posts] AS [t0]
WHERE [t0].[ParentId] = @p0

はっきり言って、私たちは書き込み/書き込みのデッドロックではなく、読み込み/書き込みのデッドロックを見ています。

現在、LINQ とパラメータ化された SQL クエリの混合物を使用しています。私たちは with (nolock) をすべてのSQLクエリに追加しました。これはいくらか役に立ったかもしれません。また、昨日修正した 1 つの（非常に）お粗末な badge クエリがあり、これは毎回実行に 20 秒以上かかり、その上 1 分ごとに実行されていたのです。私は、これがロック問題のいくつかの原因であることを望んでいました。

残念ながら、2 時間ほど前に別のデッドロック エラーが発生しました。同じ正確な症状で、同じ正確な犯人の書き込みです。

本当に不思議なのは、上で見たロック書き込み SQL 文が、非常に特殊なコード パスの一部であることです。それは だけです。 新しい回答が質問に追加されたときに実行され、親質問を新しい回答数および最終日付/ユーザーで更新します。これは明らかに、私たちが行っている膨大な数の読み取りと比較して、それほど一般的ではありません! 私が知る限り、アプリのどこかで膨大な数の書き込みを行っているわけではありません。

NOLOCK が巨大なハンマーのようなものであることは理解していますが、ここで実行するクエリのほとんどは、それほど正確である必要はありません。ユーザー プロファイルが数秒古くても気にしますか?

NOLOCKをLinqで使用するのは、次のように少し難しいです。 Scott Hanselmanがここで議論している .

を使うというアイデアをちらつかせています。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

をベースデータベースコンテキストに追加することで、すべてのLINQクエリにこのセットを持たせることができます。これがなければ、私たちが行うすべてのLINQ呼び出し（まあ、単純な読み込みのもの、それが大部分ですが）を3-4行のトランザクションコードブロックでラップしなければならず、これは醜いことです。

私は、SQL 2005 の些細な読み取りが書き込みでデッドロックすることに少し不満を感じているようです。書き込み/書き込みのデッドロックが大きな問題であることはわかりますが の読み取りはどうでしょうか。 私たちは銀行サイトを運営しているわけではないので、毎回完璧な精度を必要とするわけではありません。

アイデア？ご意見は？

操作ごとに新しい LINQ to SQL DataContext オブジェクトをインスタンス化していますか、それともすべての呼び出しに対して同じ静的コンテキストを共有していますか。

Jeremy、私たちはほとんどの場合、ベースとなるControllerで1つの静的データコンテキストを共有しています。

private DBContext _db;
/// <summary>
/// Gets the DataContext to be used by a Request's controllers.
/// </summary>
public DBContext DB
{
    get
    {
        if (_db == null)
        {
            _db = new DBContext() { SessionName = GetType().Name };
            //_db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
        }
        return _db;
    }
}

新しいコンテキストはコントローラごとに作成するのがよいでしょうか、それともページごとに、あるいはもっと頻繁に作成するのがよいでしょうか？

どのように解決するのですか？

MSDNによると

http://msdn.microsoft.com/en-us/library/ms191242.aspx

このとき READ COMMITTED SNAPSHOT または ALLOW SNAPSHOT ISOLATION データベース オプションがONの場合、すべてのデータについて論理コピー (バージョン)が維持されます。 データベースで行われたすべてのデータ変更に対して データベースで行われた全てのデータ変更に対して論理コピー（バージョン）が維持されます。行が特定のトランザクションによって変更されるたびに 特定のトランザクションによって変更されるたびに データベースエンジンのインスタンスは のインスタンスは、以前にコミットされた行のイメージのバージョンを の画像を tempdb に保存します。各 バージョンには、そのトランザクションの シーケンス番号で示されます。 のトランザクション番号で示されます。変更された行のバージョンは 変更された行のバージョンは、リンクリストを使用して連結されます。 リストで連結されます。最新の行の値は、常に現在のデータベースに 現在のデータベースに格納され tempdbに格納されているバージョン管理された行に連鎖します。 に連鎖します。

短時間実行のトランザクションでは 変更された行のバージョンは バッファプールにキャッシュされ ディスクに書き込まれることなく に書き込まれることなく、バッファプールにキャッシュされるかもしれません。もし、バージョン管理された行の必要性が バージョン管理された行の必要性が短い場合、それは単に は単にバッファプールから削除されます。 バッファプールから削除され、必ずしも I/Oオーバーヘッドを発生させることはありません。

余分なオーバーヘッドによる若干のパフォーマンスペナルティがあるように見えますが、無視できる程度かもしれません。 念のため、テストしてみる必要があります。

このオプションを設定し、本当に必要でない限り、コード クエリからすべての NOLOCK を削除してみてください。 NOLOCKs や、データベース トランザクション分離レベルに対処するためにデータベース コンテキスト ハンドラーでグローバル メソッドを使用することは、問題に対する応急処置です。 NOLOCK はデータ レイヤーの根本的な問題を覆い隠し、信頼性のないデータを選択する可能性があります。

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON