SQL Server の UPDATE でタイムアウト

Question

VB2008、SQL Server 2008 でアプリケーション開発を行っています。

下記のような列を持つテーブル(TestTable1)があり、それに対する数千件のINSERT、UPDATEを
１つのトランザクションでまとめてコミットする処理があります。

col1・・・char(10),PK
col2・・・char(2),PK
col3・・・char(2),PK
col4・・・int

他のテーブルをもとに取得した主キー(col1,col2,col3)に対して
１つずつINSERTまたはUPDATEを行っているのですが、
特定のキーのUPDATEでタイムアウトが発生します。

ただ、その時点でのINSERT、UPDATEの処理件数は合わせて１万件弱なのですが、
処理を行う主キーの範囲を絞り込んで数百件とすると、タイムアウトは発生しません。

また、主キーの範囲を少しだけ調節して、問題のキーより前に処理されるデータを
数件減らしてみても、必ず同じキーのところでタイムアウトします。

一連の処理の中で、このテーブルに対して変更を行っているSqlConnection、SqlCommandは
１つのみで、アプリケーションの性質上この処理を行っている間は他のクライアントから
問題のテーブルに対する変更やロックは行えないようになっています。

原因としてどういったものが考えられるでしょうか。
以下に、多少簡略化していますが、処理の流れやテーブルの構成、データベースへのアクセス方法
などがわかるサンプルコードを記載します。
（Try...Catchは省略しています。）
（"TestTable2 は上記の TestTable1 と同じ形式と考えてください。）

        Dim sqlCon1 As New SqlClient.SqlConnection
        Dim sqlCon2 As New SqlClient.SqlConnection
        Dim sqlCmd1 As SqlClient.SqlCommand
        Dim sqlCmd2 As SqlClient.SqlCommand
        Dim sqlRed1 As SqlClient.SqlDataReader
        Dim sqlRed2 As SqlClient.SqlDataReader
        Dim sqlTrn1 As SqlClient.SqlTransaction

        Dim col1 As String
        Dim col2 As String
        Dim col3 As String
        Dim rc As Integer

        sqlCon1.ConnectionString = "接続文字列"
        sqlCon1.Open()
        sqlCon2.ConnectionString = "接続文字列"
        sqlCon2.Open()

        sqlCmd1 = sqlCon1.CreateCommand()
        sqlTrn1 = sqlCon1.BeginTransaction(IsolationLevel.ReadCommitted)
        sqlCmd1.Transaction = sqlTrn1

        sqlCmd2 = sqlCon2.CreateCommand()

        sqlCmd2.CommandText = "SELECT * FROM TestTable2 " & _
                              "WHERE col1 >= '0000000001' " & _
                                "AND col1 <= '9999999999' " & _
                              "ORDER BY col1, col2, col3 "

        sqlRed2 = sqlCmd2.ExecuteReader()

        Do While sqlRed2.Read()

            col1 = sqlRed2.Item("col1")
            col2 = sqlRed2.Item("col2")
            col3 = sqlRed2.Item("col3")

            sqlCmd1.CommandText = "SELECT * FROM TestTable1 " & _
                                  "WHERE col1 = '" & col1 & "' " & _
                                    "AND col2 = '" & col2 & "' " & _
                                    "AND col3 = '" & col3 & "' "

            sqlRed1 = sqlCmd1.ExecuteReader()

            If sqlRed1.HasRows Then

                sqlCmd1.CommandText = "UPDATE TestTable1 " & _
                                      "SET col4 = 1 " & _
                                      "WHERE col1 = '" & col1 & "' " & _
                                        "AND col2 = '" & col2 & "' " & _
                                        "AND col3 = '" & col3 & "' "

            Else

                sqlCmd1.CommandText = "INSERT INTO TestTable1 " & _
                                      "SELECT " & _
                                      "'" & col1 & "', " & _
                                      "'" & col2 & "', " & _
                                      "'" & col3 & "', " & _
                                      "0 "

            End If

            sqlRed1.Close()

            rc = sqlCmd1.ExecuteNonQuery()

        Loop

        sqlRed2.Close()

        sqlTrn1.Commit()

 

Answer 1

やはり、真っ先に疑うべきはデッドロックなのではないでしょうか。
タイムアウトが発生する処理の実行中に、ロックの状態を確認することをお勧めします。

SQL Server 2008 自習書シリーズ No.14
ロックと読み取り一貫性
http://download.microsoft.com/download/D/3/1/D3199E7B-CB5B-41CB-9BD2-EB38436B1610/SQL08_SelfLearning14_Lock.pdf

# 実は変換デッドロックが発生していたりして。

Answer 2

＞ 特定のキーのUPDATEでタイムアウトが発生します。

そのキーのUPDATE文を単独でManagementStudioとかでやると問題ないんですよね？

＞ただ、その時点でのINSERT、UPDATEの処理件数は合わせて１万件弱なのですが、
＞処理を行う主キーの範囲を絞り込んで数百件とすると、タイムアウトは発生しません。

その数百件には問題になるUPDATE文は当然はいっているんですよね？

あまり詳しくないのに発言して恐縮ですが、TestTable1もデータがいっぱい入っているようですし、
毎回同じところで止まるのであればTestTable1のインデックスが壊れているのを疑うかなと思います。

あと、タイムアウトになったときの例外の内容にSQLのエラーコードとか入っているので
それも出せる範囲で共有したほうが良いように思います。

Answer 3

totojoさんのコメントより
>>やはり、真っ先に疑うべきはデッドロックなのではないでしょうか。
>>タイムアウトが発生する処理の実行中に、ロックの状態を確認することをお勧めします。

プロファイラで該当の行にロックがかかっていないことを確認済みです。
アプリケーション側でも、UPDATEの直前に更新ロックをかけることができます。

mars12さんのコメントより
>>そのキーのUPDATE文を単独でManagementStudioとかでやると問題ないんですよね？
タイムアウトが発生するタイミングでアプリケーションを中断して、
ManagementStudioで同じクエリを流すとこちらでもタイムアウトが発生します。

>>その数百件には問題になるUPDATE文は当然はいっているんですよね？
当然入っています。

>>毎回同じところで止まるのであればTestTable1のインデックスが壊れているのを疑うかなと思います。
テーブルを再作成してみます。

>>あと、タイムアウトになったときの例外の内容にSQLのエラーコードとか入っているので
>>それも出せる範囲で共有したほうが良いように思います。
情報不足でしたね・・・すみません。
↓
System.Data.SqlClient.SqlException(ErrorCode:-2146232060)
SqlErrorの内容は、
Number:-2
Message:タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。
と、
Number:3621
Message:ステートメントは終了されました。
です。

ちなみに、タイムアウトが発生するUPDATE文をスキップすると次のUPDATE文で同じくタイムアウトが発生しました。

Answer 4

>>そのキーのUPDATE文を単独でManagementStudioとかでやると問題ないんですよね？
タイムアウトが発生するタイミングでアプリケーションを中断して、
ManagementStudioで同じクエリを流すとこちらでもタイムアウトが発生します。

補足します。
アプリケーション側で処理を行っていないときであれば、問題のUPDATE文単独や
処理内の全てのINSERT文、UPDATE文を一括で流しても
ManagementStudioではタイムアウトは発生しません。

それから、テーブル再作成してみましたが変わりませんでした。

Answer 5

ちなみに、CommandTimeout でお茶を濁した場合はどうなるんでしょうか？

個人的には、アドホックな SQL を発行しまくっているのが気になります。
せめて SqlParameter を使うとかした方が、サーバーにやさしくないんではないかと。
あるいは、TestTable1 と TestTable2 とが同じデータベースにあるならですが、SQL 文を工夫するという手もあるかと思います。

SQL Server 2005 Tips and Tips | SQL Server 2005 | TechNet
http://technet.microsoft.com/ja-jp/sqlserver/gg639072

Answer 6

mars12さんのコメントより
>>そのキーのUPDATE文を単独でManagementStudioとかでやると問題ないんですよね？
タイムアウトが発生するタイミングでアプリケーションを中断して、
ManagementStudioで同じクエリを流すとこちらでもタイムアウトが発生します。

ロックがかかっていないことを確認済みだそうですが、やはりロックの問題のように思えます。疑うわけではないですが、もし、利用状況モニタで確認されていないのでしたら、念のために確認してみ下さい。

（参考）
SQL Server 2008 で「利用状況モニタ」ツールでロック待ちのプロセスをリストアップする（ロックの監視）
http://d.hatena.ne.jp/matu_tak/20091114/1258151357

でも、selectとupdate, insertは同一ドランザクション内で行っているんですよね・・・

 

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 7

ところでこのSQL文、ざっと見たところ、TestTable2のデータをTestTable1と比較し、存在すればUPDATE、存在しなければINSERTしていると読みましたが合っていますでしょうか？

であればMERGE文が使えます。こんな感じかな。

MERGE TestTable1
USING (SELECT col1, col2, col3
       FROM TestTable2
       WHERE col1 >= '0000000001' AND col1 <= '9999999999') AS SOURCE
ON (TestTable1.col1=SOURCE.col1 AND TestTable1.col2=SOURCE.col2 AND TestTable1.col3=SOURCE.col3)
WHEN MATCHED THEN
    UPDATE SET col4=1
WHEN NOT MATCHED THEN
    INSERT (col1, col2, col3, col4)
    VALUES (col1, col2, col3, 0);

 

追記

例えMERGE文を知らなかったとしても、SQL文の発行の仕方が効率悪すぎです。TestTable2にSELECTし、数千行ヒットするとのことですが、その後、SELECT数千回、INSERT+UPDATEも数千回行っています。

-- TestTable1とTestTable2で一致するものに対して更新する
UPDATE TestTable1
SET col4=1
WHERE col1 >= '0000000001'
  AND col1 <= '9999999999'
  AND EXISTS (SELECT *
              FROM TestTable2
              WHERE TestTable2.col1=TestTable1.col1
                AND TestTable2.col2=TestTable1.col2
                AND TestTable2.col3=TestTable1.col3);

-- TestTable2にあってTestTable1にないものを挿入する
INSERT INTO TestTable1 (col1, col2, col3, col4)
SELECT col1, col2, col3, 0
FROM TestTable2
WHERE col1 >= '0000000001'
  AND col1 <= '9999999999'
  AND NOT EXISTS (SELECT *
                  FROM TestTable1
                  WHERE TestTable2.col1=TestTable1.col1
                    AND TestTable2.col2=TestTable1.col2
                    AND TestTable2.col3=TestTable1.col3);

とやれば、数千行ｘ２と同等の結果が２行で完了します。

Answer 8

totojoさんのコメントより
>>ちなみに、CommandTimeout でお茶を濁した場合はどうなるんでしょうか？

その場合でも、いくら待っても返ってこないという状況です。

trapemiyaさんのコメントより
>>ロックがかかっていないことを確認済みだそうですが、やはりロックの問題のように思えます。
>>疑うわけではないですが、もし、利用状況モニタで確認されていないのでしたら、念のために確認してみ下さい。

状況からするとロック待ちそのものですよね・・・。
利用状況モニタでも確認してみました。やはりロック待ちの状態にはなっていませんでした。

totojoさん、佐祐理さんからご指摘がありました非効率なSQLの発行についてですが、
確かにこのコードではその通りですね・・・。度々の説明不足で申し訳ありません。

実際には TestTable2 からデータを取得した後、そこそこ複雑な計算を行い、その計算の結果と、
該当するデータが TestTable1 にあるかどうかをどちらも考慮した上で INSERT か UPDATE を判断し、
さらに col4 に設定する値にも計算結果を反映させています。

ただ、その計算の辺りはデータベースに無関係ですので、サンプルでは省略させていただきました。

発行するSQLの回数を減らす件についてはこちらでもテストを行っていて、
INSERT、UPDATE を数百回ごとにまとめて発行するようにしたところ、問題なく全ての処理が完了しました。

しかし現象の回避はできたものの、今のところはただ臭いものに蓋をしただけの状態ですので、
なんとか原因をはっきりさせたいのですが・・・。

Answer 9

それに関してはtrapemiyaさんも指摘されている通りで、MMTRSさんはロックが発生していないと思われているでしょうが、実際にはロックを見逃してしまっているのでは、と考えます。

例えば、sqlCmd1.ExecuteReader()、sqlRed1.Read()、sqlRed1.Close()とするのではなく、sqlCmd1.ExecuteNonQuery()の戻り値を参照するとか。

Answer 10

それに関してはtrapemiyaさんも指摘されている通りで、MMTRSさんはロックが発生していないと思われているでしょうが、実際にはロックを見逃してしまっているのでは、と考えます。

例えば、sqlCmd1.ExecuteReader()、sqlRed1.Read()、sqlRed1.Close()とするのではなく、sqlCmd1.ExecuteNonQuery()の戻り値を参照するとか。

プロファイラでも利用状況モニタでも確認し、アプリケーションからも
更新ロックがかけられることを確認しています。
それでもロックを見逃しているすると、その見逃しはどうすればみつけられますか？

理解力が乏しくて申し訳ないのですが、「sqlCmd1.ExecuteNonQuery()の戻り値を参照」というのは
どういったことでしょうか？
UPDATE してみて戻り値が 0 なら INSERT するってことですか？
すみませんが具体的なコードなんか書いていただいても良いですか・・・？

Answer 11

ロックについては現状ではわかりません。質問のSQL文は簡略化されたもので本物はもっと複雑ということですので、そこが絡んで何かロック解放待ちをしている可能性もありますし、この辺りはプロファイラーで詳しく見てみなければわかりません。

エラーがどの行で発生するのかも重要です。UPDATE文ですか？ 処理に時間がかかっていて、トランザクションがタイムアウトしてたりしませんか？ SQL Serverの接続文字列にはTransaction Bindingという設定があり、デフォルトではトランザクションがタイムアウトした際、SqlCommandはトランザクション外でコミットしようとします。
# むちゃくちゃ恐ろしい仕様です（＝＝；

SqlCommand.ExecuteNonQuery()の戻り値は影響を受けた行数です（SELECTなら選択された行数）。この値が1以上ならsqlRed1.HasRows=Trueに相当します。この機能を利用すればSqlDataReaderを取得する必要がなくなり、処理が軽くなります。

Answer 12

佐祐理さんのコメントより
>>エラーがどの行で発生するのかも重要です。UPDATE文ですか？
>>処理に時間がかかっていて、トランザクションがタイムアウトしてたりしませんか？

タイトルと最初の投稿にも書きましたが、UPDATE文です。

それとすみません。重要そうな見落としがありました。
ただそれでも原因がはっきりしないので、みなさんの知恵をお貸し下さい。

        sqlCmd2.CommandText = "SELECT * FROM TestTable2 " & _
                              "WHERE col1 >= '0000000001' " & _
                                "AND col1 <= '9999999999' " & _
                              "ORDER BY col1, col2, col3 "

としていた部分ですが、実はこのSELECT文に TestTable1 が LEFT JOIN されていました。
LEFT JOIN (SELECT col1, MAX(col4) AS t1_col4 FROM TestTable1 GROUP BY col1) AS t1 ON TestTable2.col1 = t1.col1
という形です。
本当にお恥ずかしい話です。

これは今回のタイムアウトに関係するでしょうか。

 

Answer 13

別ＤＢかなと思ってましたが、同一DB内での処理でしたか。

sqlCon1とsqlCon2という２個のコネクションで、片方だけトランザクションに入ってますが、トランザクションに入っていない方もロックされているテーブルを参照しているとロックしたりしませんかね？
読み出しで最新だけ必要なら問題ないですが。
コネクションを別ける理由がないなら、同一のコネクションとトランザクションで処理したほうが良くないですか？
#こういう接続の仕方を試したことないのでどうなるか良く分かりません。

SQLの結果のストリームがどうなってるかよく知りませんが、sqlRed2で順に読み出している途中で、参照してるテーブルが更新されることになるので、結果が不正になってしまうような気がします。

---

個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

Answer 14

今回、sqlRed2からデータを読み出している最中に、sqlCmd1を実行されています。これはsqlCmd2がロックを保持したまま、sqlCmd1が実行されることを意味します。sqlCmd1がsqlCmd2のロック解除待ちをする可能性は十分にあります。

すみません、トランザクションタイムアウトは勘違いがありました。System.Transactions.TransactionはタイムアウトするけどSqlTransactionはタイムアウトしないんでしたっけ…。

gekkaさんへ：
接続文字列Pooling=true（デフォルトでtrue）であれば、同一コネクションだろうが別コネクションだろうが大差ないと思います。同一コネクションを使い回そうとする場合、MultipleActiveResultSets=true（デフォルトでfalse）にする必要がありますが。

Answer 15

実はこのSELECT文に TestTable1 が LEFT JOIN されていました。

きちんとデータを調べたわけではないのでわかりませんが、おそらくこれが原因でしょう。
SQL Serverの既定の分離レベルはREAD COMMITTEDですので、ダーティ読み取りを許しません。つまり、コミットされていないデータを読もうとするとそのデータがコミットまたはロールバックされるまで待つことになります。既定では永遠に待ち続けます。よってタイムアウトするのでしょう。

おそらくSqlCommandの関係でコネクションを２つに分けられていると思いますが、SQL Server 2008ではMARSが使えますので、同一のコネクションで複数の結果セットを扱うことができます。よって、SqlConnectionを１つにまとめられますので、全て同一トランザクション内で動作させることができると思います。
＃MARSを用いてこのような処理をした経験が無いので、何か問題が発生するかもしれません。MARSの説明では結果セットが大きくなる場合はサーバーカーソルを使用して下さいとも書かれていました。
ちなみに、私であればストアドプロシージャでこの処理を書いてしまうと思いますし、ストアドプロシージャで処理されることをお勧めします。

 

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 16

sqlCmd2 がデータを保持した状態で sqlCmd1 が同じテーブルに更新をかけようとしているので、そこでロック待ちが発生する可能性は理解できるのですが、どうしてこの現象がある程度の処理件数がないと発生しないのか、ある程度の処理件数があると件数に関わらず必ず同じUPDATE文で発生するのか、というところがわかりません。

trapemiyaさんのコメントより
>>SQL Server 2008ではMARSが使えますので、同一のコネクションで複数の結果セットを扱うことができます。よって、SqlConnectionを１つにまとめられますので、全て同一トランザクション内で動作させることができると思います。

今回の件とは全く別の部分の関係で、アプリケーションとして統一性を保つためにもMARSはデフォルトのまま使用する方針だったのですが、これを機に再度検討してみようかと思います。
ストアドの方がいろいろな面で効率的だと思いますので、こちらを優先的に。

ひとまずは、Loop 用のデータを取得する際に、TestTable1 を直接参照しないようにSQL文を修正したところ、今のところは現象を回避できるようになりましたので、sqlCmd1 と sqlCmd2 との間で TestTable1 に対してのロック待ちが発生していたことは間違いないとは思います。
ただやはりはっきりとした原因が知りたいです・・・。

Answer 17

実際のコードや行数によっても動作が変わるので、はっきりしたことはプロファイラーで詳細な動作を確認しないとわかりません。

例えば、ロックエスカレーションといって、TestTable1に対して複数の行をロックするよりも行範囲で一括でロックしてしまうこともあります。この辺りもプロファイラーでどうなっているのか見ることができます。

逆にMMTRSさんはどのような方法で「プロファイラで該当の行にロックがかかっていないことを確認済み」なのでしょうか？

Answer 18

はっきりとした原因を探るには、やはり「実行プランを読む」とかになるんでしょうか。

# 個人的には、アドホックなクエリーを出しまくっているであろう点を何とかしたいところですが。
# 実際のコードでは、ちゃんと SqlParameter を使っているのかな。

Answer 19

TestTable2 に TestTable1 を JOIN して取得していた時に、共有ロックがロックエスカレーションしたんでしょうね。

 

処理の順番はわかりませんので想像ですが、

sqlCmd1 で Update あるデータを排他ロック

sqlCmd2 で Select あるデータを共有ロック

sqlCmd1 で Update あるデータを排他ロック

sqlCmd2 で Select あるデータを共有ロック

 

みたいな処理だとすると、例えば

sqlCmd1 で Update あるデータを 排他ロック　行ロック

sqlCmd2 で Select あるデータを 共有ロック　※ここでエスカレーションしてページロック

sqlCmd1 で Update あるデータを 排他ロック　行ロックしようとしたが、対象レコードの属するページがロックされていた為ロック待ち発生！！

 

って感じかなぁ・・・

試しにsqlCmd2 のTRANSACTION ISOLATION LEVEL を SNAPSHOT にしたらどうなります？

※あくまでためしにです。変更前と同じ動作は保証しませんのであしからず。

Answer 20

佐祐理さんのコメントより
>>逆にMMTRSさんはどのような方法で「プロファイラで該当の行にロックがかかっていないことを確認済み」なのでしょうか？

すみません。確認したのは私ではなくて、「ロックされていなかった」とだけで詳しくは聞いていませんでした。
そう考えると今更ながら不安になってきました。確認してもらった人との間にもしかしたら勘違いもあったかも知れません・・・。

再度自分で見てみます。ロックエスカレーションも確認したいのですが、確認方法の勉強からなので少し時間がかかるかと思います。

aviator__さんのコメントより
>>試しにsqlCmd2 のTRANSACTION ISOLATION LEVEL を SNAPSHOT にしたらどうなります？

sqlCmd2 で読み取るデータに影響のある更新はしていないので SNAPSHOT であれば問題なくいけそうな気がしますが、すみませんがこちらも少し時間をください。

ある程度の件数がないと現象が発生しないのは、ロックエスカレーションが発生するためのしきい値によるもので、
特定のキーで必ず現象が発生するのは、おそらくその行から SQL Server が管理しているページが切り替わっているから、
と考えてみたのですが・・・合ってますか？

Answer 21

sqlCmd2 の分離レベルを SNAPSHOT にしてみたところ、現象は発生しませんでした。

それと、プロファイラでの確認内容についてご報告します。

Lockカテゴリの DeadLock, DeadLock Chain, Escalation, Timeoutイベントをトレースしてみましたが、なにも出てきませんでした。

なにがなんだかわからなくなってきました・・・。
確認の方法間違ってますか？アドバイスお願いします・・・。

Answer 22

さすがに Blocked process report は発生しているはずだと思いますが、いかがでしょうか？

Answer 23

たぶん発生しているのは「デッドロック」ではなくて「ブロック」です。

Readしながら更新するのではなく、一旦配列などに読み込んでClose()してから、UPDATEやINSERTを実行してみてください。

SQL Serverではブロックにも気をつけないといけません。

OracleやPostgreSQLではこのような現象は発生しません。

 

---

http://systemartlaboratory.com/

Answer 24

totojoさん
>>さすがに Blocked process report は発生しているはずだと思いますが、いかがでしょうか？

Blocked process report を確認するにあたり、私が見つけた資料では SQL Server の設定を以下のように変更する必要があるとのことでした。

    EXEC sp_configure 'show advanced options', '1'
    RECONFIGURE
    EXEC sp_configure 'blocked process threshold', '5'
    RECONFIGURE

ブロックのしきい値を５秒に設定するとのことですが、できるだけ SQL Server の設定を変更したくないので、デフォルトの値やこの変更が他に与える影響、設定の戻し方などを調べていました。
ですが今のところそれらがわかっておらず、Blocked process report を確認できていないという状況です。

可能な範囲で教えていただきたいのですが・・・。

三輪の牛さん
>>Readしながら更新するのではなく、一旦配列などに読み込んでClose()してから、UPDATEやINSERTを実行してみてください。

現状の回避策としては、TestTable1 を一時テーブルにコピーして、一時テーブルを Read の対象としています。
スナップショット分離レベルと同じ動作を期待して、同じテーブルに対して SELECT と UPDATE または INSERT を同時に行わないことを目的としていますが、これは一旦配列等に読み込む方法と同様の対応と考えても良いですか？

Answer 25

すみません、Blocked process report 確認できました。
たしかに sqlCmd2 で発行した SELECT文によって UPDATE がブロックされています。

それから、こちらも申し訳ないですが「すべての列を表示する」にチェックを入れたら Blocked process report の前に Timeout も発生していました。
気になったのは Timeoutイベントの OwnerID：1 (TRANSACTION) と Type：6 (PAGE) あたりですが、これは SQL Server で何が起こっているということなんでしょうか。

 

Answer 26

一時テーブルがSQL Server上のテーブルを指しているのであれば、無関係のテーブルであってもブロックの可能性はあります。

オンメモリのテーブルを指しているのであれば確実な対策と言えます。

下記URLを参照ください。

http://msdn.microsoft.com/ja-jp/library/Aa178087

上記URLで注意すべき点は、既に確認なさっているように単一の接続であってもサーバプロセスが複数に分かれるのでブロックが発生します。

ガイドラインの１つに「すべての結果行を即座に最後まで取り出します。」と書かれています。

---

http://systemartlaboratory.com/

Answer 27

三輪の牛さん
>>一時テーブルがSQL Server上のテーブルを指しているのであれば、無関係のテーブルであってもブロックの可能性はあります。

SQL Server 上のテーブルです。
URL を見てみましたが、「アプリケーションがすべての結果行を取り出さない場合、テーブルにロックが残り、ほかのユーザーをブロックすることがあります。」とありました。
これを見る限り「テーブルにロックが残り」とあるので、一時テーブルを使用した場合はロックが残るのはあくまで一時テーブルであり、UPDATE は元のテーブルに対して行うのでブロックは発生しないのでは？と考えたのですが、違うものなのでしょうか。

それともうひとつ、仮に SELECT の結果を SqlDataReader = SqlCommand.ExecuteReader() ではなく SqlDataAdapter.Fill(DataSet) として DataSet を利用した場合は、「すべての結果行を即座に最後まで取り出す」に該当しますか？

 

Answer 28

＞「アプリケーションがすべての結果行を取り出さない場合、テーブルにロックが残り、ほかのユーザーをブロックすることが

＞あります。」とありました。

＞ これを見る限り「テーブルにロックが残り」とあるので、一時テーブルを使用した場合はロックが残るのはあくまで一時

＞テーブルであり、UPDATE は元のテーブルに対して行うのでブロックは発生しないのでは？

そうあって欲しいと私も思いました。

期待に反する現象を確認したとき、これがSQL Serverの仕様だと思うようにしました。

バージョンが変わっているので2008でももそうだとは断定できませんが。

Fillは「すべての結果行を即座に最後まで取り出す」に該当します。

 

---

http://systemartlaboratory.com/

Answer 29

返事が遅くなってしまい申し訳ありません。

みなさんに助言いただいて、ひとまずの回避方法として SqlDataAdapter.Fill(DataSet) として DataSet を使用する方向になりました。
SqlDataAdapter や DataSet はアプリケーション内の他の箇所でも使っているので。

ただ SQL Server が期待に反する動作をしている可能性を考えると、100%安心はできていませんが・・・。

これまで調べた結果から、sqlCmd2 で発行した SELECT によって UPDATE がブロックされていることはわかりましたが、やはりある程度の件数がないと現象が発生しないことと、ある程度の件数があると必ず同じキーの UPDATE で現象が発生する、という２点がこのブロックとどう関係しているのかがわかりません・・・。
どのように考えられるでしょうか。

Answer 30

どのような状況でどのような仕組みで発生するか、私はそれ以上の追求を諦めました。SQL Serverにはブロックと連鎖更新の制限があるので、それ以来指定がなければSQL Serverを避けて、PostgreSQLを使うようになりました。SQL Serverでも読みながら更新しなければブロックは起こっていないようです。たいした情報がなくて済みません。どなたか情報をもっておられると良いですね。

---

http://systemartlaboratory.com/

Answer 31

次のどちらかで対処できたのかもしれないと思いました。
・同じトランザクション（Connection １つ）に変更して MARS を有効にする（既出な話）。
・最初のソース（複数接続）のまま、alter database にて READ_COMMITTED_SNAPSHOT を ON にする。
　参考：http://technet.microsoft.com/ja-jp/sqlserver/gg639075

あと関係ないですが、なるべく Using を使って解放するようにした方がいいと思います。