none
SQL Azureのトランザクション分離レベルと同時実行制御について RRS feed

  • 質問


  • いつもお世話になっております。

    SQL Azureのトランザクション分離レベルと同時実行制御についてご教授願います。

    SQLAzureでSystem.Transactions.TransactionScopeを利用した更新処理を複数ユーザから同時実行した場合には、どのような挙動になるのでしょうか?

    「SQL Azure 開発入門」(http://msdn.microsoft.com/ja-jp/magazine/gg309175.aspx)の以下の記述を参考にしました。

      
      >SQL Azure でホストされているデータベースに使用できるトランザクション分離レベルは、READ COMMITTED スナップショットのみです。
      >この分離レベルを使用すると、ステートメントの状態が STARTED になった時点に使用できたデータの、最新で一貫性のあるバージョンがリーダーに提供されます。
      
      >SQL Azure では、更新の競合が検出されません。
      >更新内容の損失、反復不能読み取り、およびファントムが発生することがあるため、これはオプティミスティック同時実行モデルとも呼ばれます。
      >もちろん、ダーティ リードは発生しません。
      
      【質問】下記のような挙動になる認識はあっていますでしょうか?
      
      【レコード】
      CustID     LastName     FirstName

      101          Smith             Bob


      【処理】
      
      Aさん、Bさんが、レコード を読取。
      
      Aさんは、[FirstName]を"Jon"へ変更。
      
      Bさんは、[FirstName]を"Robert"へ変更。
      
      Aさん、Bさんが同じタイミングで更新処理を実行。
      
      CさんがBさんのコミット前にレコードを読取。
      
      【予想結果】
      ・A、B、Cさんの処理は全て正常終了する。
      ・レコードは、Bさん後勝ちでデータ更新されている。([FirstName]="Robert")
      ・Cさんの取得した情報は、Aさんの更新済データとなっている。([FirstName]="Jon")
      

      [詳細]
      
       Aさんの処理                                Bさんの処理
        処理開始                                    ↓
        (何らかの機能?でAさんから処理開始になったとする)      処理開始
        ↓                                        ↓
        レコードロック(共有ロック?)                       ロック開放待ち状態 
        ↓                                        (更新の競合を検知しないため、エラーとならずに待つ?)
        SQL実行                                    ↓
        ↓                                        ↓
        SQL実行後                                  ↓
        (READ COMMITTEDのため、ここでロックが開放される?)   レコードロック(共有ロック?)
        ↓                                          ↓
        自動コミット                                 SQL実行
        ↓                                          ↓
        終了                                     SQL実行後
                                                 (READ COMMITTEDのため、ロックが開放される?)
                                                  ↓
                                                 ■Cさんのレコード読取処理
                                                 (スナップショットのため、Aさんのコミット新データが読み込まれる?)
                                                  ↓
                                                 自動コミット
                                                  ↓
                                                  終了

    SQL Azureではなく、SQL Serverの常識的なことも含まれているかも知れませんが、どうぞ宜しくお願いいたします。。。

     【参照したサイト】
     (1)SQL Azure 開発入門
      http://msdn.microsoft.com/ja-jp/magazine/gg309175.aspx

     (2)Handling Transactions in SQL Azure
      http://social.technet.microsoft.com/wiki/contents/articles/handling-transactions-in-sql-azure.aspx

    2011年1月19日 8:41

回答

  • SQL Azureの知識がないので、以下はSQL Serverの話として読んでください。分離レベルと同時実行制御の話としてはSQL Azureに違いはないと思います。


    >SQL Azure でホストされているデータベースに使用できるトランザクション分離レベルは、READ COMMITTED スナップショットのみです。

    スナップショットだけカタカナ訳されると別のものに読めますが、正しくは「READ_COMMITTED_SNAPSHOT」です。READ_COMMITTED分離レベルではありません。

    READ_COMMITTED_SNAPSHOTは、Oracleの動作とほぼ同じと考えるとわかりやすいかもしれません。

    この分離レベルでは共有ロックはかからず、他のトランザクションは更新可能です。READ_COMMITTED_SNAPSHOTで最悪なのは、トランザクション単位での読み取り一貫性がないこと、オプティミスティック同時実行制御にも関わらず更新の競合を検出できないことですので、その点はロックヒントが必要になります。

    要するに、トランザクション中に同じデータを二度以上選択(select)すると双方で値が違う、他のトランザクションの更新を上書きしてしまう可能性があると言うことです。


    以下に例を挙げます。

    例)
    FistNameの値は"Mike"

    1)Aさんがトランザクション開始
    2)Bさんがトランザクション開始
    3)Cさんがトランザクション開始
    4)Bさんが[FirstName]を読み取る→"Mike"(共有ロックはかからない)
    5)Aさんが[FirstName]を"Jon"へ変更→(4で共有ロックがかかっていないので更新可)
    6)Cさんが[FirstName]を読み取り→"Mike"(ダーティリードは防げる)
    7)Aさんがコミット→[FistName]が"Jon"に確定。
    8)Cさんが[FistName]を読み取り→"Jon"(読み取り一貫性がない)
    9)Bさんが[FirstName]を"Robert"へ変更し、コミット→(同時実行違反。だが、競合を検出できない)。
    おまけ)Cさんが[FistName]を読み取り→"Robert"(読み取り一貫性がない)

    2011年1月19日 11:18

すべての返信

  • SQL Azureの知識がないので、以下はSQL Serverの話として読んでください。分離レベルと同時実行制御の話としてはSQL Azureに違いはないと思います。


    >SQL Azure でホストされているデータベースに使用できるトランザクション分離レベルは、READ COMMITTED スナップショットのみです。

    スナップショットだけカタカナ訳されると別のものに読めますが、正しくは「READ_COMMITTED_SNAPSHOT」です。READ_COMMITTED分離レベルではありません。

    READ_COMMITTED_SNAPSHOTは、Oracleの動作とほぼ同じと考えるとわかりやすいかもしれません。

    この分離レベルでは共有ロックはかからず、他のトランザクションは更新可能です。READ_COMMITTED_SNAPSHOTで最悪なのは、トランザクション単位での読み取り一貫性がないこと、オプティミスティック同時実行制御にも関わらず更新の競合を検出できないことですので、その点はロックヒントが必要になります。

    要するに、トランザクション中に同じデータを二度以上選択(select)すると双方で値が違う、他のトランザクションの更新を上書きしてしまう可能性があると言うことです。


    以下に例を挙げます。

    例)
    FistNameの値は"Mike"

    1)Aさんがトランザクション開始
    2)Bさんがトランザクション開始
    3)Cさんがトランザクション開始
    4)Bさんが[FirstName]を読み取る→"Mike"(共有ロックはかからない)
    5)Aさんが[FirstName]を"Jon"へ変更→(4で共有ロックがかかっていないので更新可)
    6)Cさんが[FirstName]を読み取り→"Mike"(ダーティリードは防げる)
    7)Aさんがコミット→[FistName]が"Jon"に確定。
    8)Cさんが[FistName]を読み取り→"Jon"(読み取り一貫性がない)
    9)Bさんが[FirstName]を"Robert"へ変更し、コミット→(同時実行違反。だが、競合を検出できない)。
    おまけ)Cさんが[FistName]を読み取り→"Robert"(読み取り一貫性がない)

    2011年1月19日 11:18
  • T-Yokoo様返信ありがとうございます。

    頂いた回答から思うに、この質問はSQL Azureの質問ではなく、SQL Serverの質問ですね・・・申し訳ありません。
    (一時テーブルのデータ(tempdb データベース内の「Version Store」)はSQL Azure使用容量に含まれるのか等は気になりますが。。。)

    >READ_COMMITTED_SNAPSHOTは、Oracleの動作とほぼ同じと考えるとわかりやすいかもしれません。

      ⇒この一文で、ある程度のイメージができました!
        SQL Server をあまり利用したことがないので、SQL Serverの知識を深めた方が良さそうです。。。
        読取でロックがかかるだとか、分離レベルの種類で動作が違うなどの部分が、う~んとなっていました。

    >この分離レベルでは共有ロックはかからず、他のトランザクションは更新可能です。READ_COMMITTED_SNAPSHOTで最悪なのは、トランザクション単位で>の読み取り一貫性がないこと、オプティミスティック同時実行制御にも関わらず更新の競合を検出できないことですので、その点はロックヒントが必要になります。

      ⇒ULOCK(更新ロック)を使用して更新処理をすることにしようと思います。
       (select for updateを使うイメージで)


    >要するに、トランザクション中に同じデータを二度以上選択(select)すると双方で値が違う、他のトランザクションの更新を上書きしてしまう可能性があると言うことです。

      ⇒これは、二相ロック方式等のアプリケーションでの対応を行いたいと思います。
        (扱うデータを全てロック付き検索→処理→全て更新→ロック開放)
           同時実行性が弱くなってしまうかも知れませんが・・・・・。

    まだまだ足りてない知識が多いで、まずSQL Serverについて勉強したいと思います。
    分かりやすい回答ありがとうございました!!

    【参考サイト】
    (1)ロックヒント
     http://msdn.microsoft.com/ja-jp/library/ms172398(v=sql.90).aspx

    (2)二相ロック

     http://www.techscore.com/tech/sql/11_02.html

     

     

    2011年1月20日 6:26