none
Table型変数を使用したSQL処理でDeadlockが発生するのを回避するには RRS feed

  • 質問

  • こんにちは、お世話になります。

    SQL Server 2012を使用しています。

    今回、Deadlockが発生し、どのSQL文が関係しているのかに関してxdlファイルから特定できていますが、

    認識に誤りがないか確認したい点と、回避方法のアドバイスを頂きたく投稿させて頂きました。

    環境に関しては、下記質問の後に書かれております。


    1.テーブル型変数を使用している場合でもINSERT/UPDATE/DELETEなどを含んでいるため、Deadlockを

      発生させる原因になる事があると言う認識は間違いないでしょうか?


    2.今回、テーブル型変数に対してINSERTを行なっているにも関わらずトランザクションに関する命令を指定

      している箇所がなく、.NETやSQL Serverのデフォルトに任せているのを、BEGIN TRANSACTIONとCOMMIT

      をテーブル型変数に対するINSERTの直前、直後に指定する事で多少は改善されるでしょうか?

    BEGIN TRAN T1;
    ISERT INTO  テーブル型変数B EXEC ストアドプロシージャA;
    COMMIT TRAN T1;
    

    3.問題となる各SQL文が具体的に見えていない状態でアドバイスを行う事は難しい事は理解しておりますが、

      SELECTとINSERTなどの処理順番は変えず、何かしらの指定を追加する方法や、何かしらの命令に置き換える

      事などでDeadlockを発生させないでテーブル型変数を使用する方法があれば、教えて頂けないでしょうか?


    環境は下記の通りです。

    Connection1

     ・SELECT文のみ、「テーブル WITH(NOLOCK)」の指定が全てされている

     ・複数回SELECT文を発行し、DB接続を確実に終了・切断している


    Connection2

     ・BEGIN TRANSACTIONとCOMMITのようにトランザクションとコミットに関する指定は、SQL文内、アクセス元プログラム

      側(ASP.NET)でも行なっていない。

     ・テーブル型変数を使用しており、 「INSERT テーブル型変数B EXEC ストアドプロシージャA」で値をセットしている

     ・ストアドプロシージャA内では、SELECTで複数のテーブルをジョインしてレコード抽出を行っています

      ストアドプロシージャA内のSELECTに関しても「テーブル WITH(NOLOCK)」の指定が全てされている

     ・テーブル型変数Bを SELECT文内で INNER JOIN テーブル型変数B としてジョインして使用している

     ・上記テーブル型変数を使用している箇所以外は、SELECT文のみであり「テーブル WITH(NOLOCK)」の指定が全てされている

     ・複数回SELECT文を発行し、DB接続を確実に終了・切断している

    以上、宜しくお願い致します。

    2019年7月12日 3:11

回答

  • miniHORIさん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    三つの問題につきまして、いくつかのアドバイスを提供いただければと思います。

    1.はい。 プログラム内で同時にINSERTおよびDELETEを行うと、デッドロックまたは中断が発生する可能性があります。

    2.デッドロックを回避したいでしょうか。NOLOCKを参照してください。 
    WITH (nolock) ヒントは、クエリのビュー内のテーブルまたはテーブルに対してトランザクション分離レベルを設定するために使用される、特定のテーブルまたはビューに対する明示的なコマンドです。
    一度発行されると、テーブル内のデータに対してロックは使用されません。 
    これの利点は、テーブルに対して実行されている他のクエリに対してデッドロックが発生する可能性がないことです。 
    他の間接的な利点は、そのデータに対するロックを保持するために使用されるメモリが少なくなることです。

    3.特定の環境やデータがなければ、具体的なアドバイスをするのは本当に難しいです。
    いくつかの記事を見つけて、それが役に立てれば幸いですが、ご参照してください。
    How do I reduce deadlocks?
    Lesson on SQL Server Deadlocks and how to solve
    Tips For Minimizing Deadlocks in SQL Server

    どうぞよろしくお願いいたします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~
    • 回答としてマーク miniHORI 2019年8月7日 0:52
    2019年7月19日 3:03
    モデレータ

すべての返信

  • miniHORIさん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    もっと詳細な状況を共有いただくことで、正しい方向に進んでテストを行い、
    他のユーザー様よりのご意見が集まりやすくなります。

    どうぞよろしくお願いいたします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2019年7月18日 2:26
    モデレータ
  • miniHORIさん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    三つの問題につきまして、いくつかのアドバイスを提供いただければと思います。

    1.はい。 プログラム内で同時にINSERTおよびDELETEを行うと、デッドロックまたは中断が発生する可能性があります。

    2.デッドロックを回避したいでしょうか。NOLOCKを参照してください。 
    WITH (nolock) ヒントは、クエリのビュー内のテーブルまたはテーブルに対してトランザクション分離レベルを設定するために使用される、特定のテーブルまたはビューに対する明示的なコマンドです。
    一度発行されると、テーブル内のデータに対してロックは使用されません。 
    これの利点は、テーブルに対して実行されている他のクエリに対してデッドロックが発生する可能性がないことです。 
    他の間接的な利点は、そのデータに対するロックを保持するために使用されるメモリが少なくなることです。

    3.特定の環境やデータがなければ、具体的なアドバイスをするのは本当に難しいです。
    いくつかの記事を見つけて、それが役に立てれば幸いですが、ご参照してください。
    How do I reduce deadlocks?
    Lesson on SQL Server Deadlocks and how to solve
    Tips For Minimizing Deadlocks in SQL Server

    どうぞよろしくお願いいたします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~
    • 回答としてマーク miniHORI 2019年8月7日 0:52
    2019年7月19日 3:03
    モデレータ