都市伝説 (Urban Legends) を読み解く - データが読み込めないのは「文レベル読取り一貫性」によるものなのか!?

  • Article

マイクロソフトの北川です。

都市伝説には「ニュース性」「真実味」「オチ」という特徴があり、起源や根拠が全く不明なものが多いとされています。また、何かしらの根拠があるものに関しても、なんでもない事実に尾ひれがついて伝説化したとされています。この都市伝説の問題はそれが「真実」であるかのように語られる点にあります。特定の都市伝説が広く普及した原因は、広く信頼されているソースにより紹介されたことにあるとされています。

閑話休題

日本オラクル社のウェブサイトにおける「オラクル都市伝説」で揶揄されている「あのデータベース」ですが、今回は「PowerPivot と相性がいい」とされているようです。この PowerPivot は、提示されている UI を見るとマイクロソフトが5月1日より提供開始した SQL Server 2008 R2 PowerPivot for Excel (以降 PowerPivot) のようです。

「データの活用」という観点でエンジンでは競合する両社がともに「セルフサービス BI」の重要性を訴求するといういいお話では残念ながらないようです。では、今回はこの「都市伝説」を読み解いてみましょう。

 

本文中から読み取れる前提とゴール

[ゴール] 　売上分析レポートを作成する

[前提]  

  1. 事業部 X (Oracle Database), Y (あのデータベース) のシステムは別
  2. 「売上分析レポート」という提携で準備されていそうなレポートであるにもかかわらず、定型レポートには含まれていないため、アドホックにレポートを作成する必要がある
  3. レポートで使用する対象のテーブルは「仕入先」「社員」「受注」「受注明細」「商品」「商品区分」「都道府県」「得意先」
  4. 月末のため、事業部のほぼ全員がデータベースにアクセスして「顧客毎の営業ステータスを更新する」ために更新処理を行っている
  5. 上記で更新されているテーブルはレポートで使用する対象のテーブルに含まれている (レポート作成のために読み取る必要があるとされている)
  6. 「あのデータベース」では「文レベル読取り一貫性」が有効になっておらず、デフォルトの分離レベルで稼働している
  7. レポートで使用する対象のテーブルはサイズが大きい (朝6時から9時の3時間で取得が終わらないほどに) 

データが読み込めないのは「文レベル読取り一貫性」によるものなのか!?

まず最初に「あのデータベース」では「一個目のテーブル(仕入先)から一向にデータが取得でいない」とされています。それに対して Oracle Database ではデータ取得が短時間で終わっているとされています。では、「あのデータベース」が SQL Server と見なして上記前提と照らし合わせて考えてみましょう。なお、この都市伝説のコンテンツは Oracle Database と「あのデータベース」との比較を行っていることから、上記前提は等しく両者のデータベースを使用したシステムに適用されるものと仮定します。(でなければ公平な比較はできませんよね。)

システムは別、とされていますが、読み込みを行おうとしている SQL Server 2008 R2 PowerPivot for Excel の画面からすると、テーブル構造は同じのようです。また、今回「あのデータベース」に対しては「ダーティーリード」を実行していないようです。つまり、SQL Server 2008 R2 PowerPivot for Excel で SQL 文を NOLOCK ヒント付きで実行するようなことは「あえて」していないようです。

前述の「前提」と合わせて考慮すると「あのデータベース (SQL Server)」は Read Committed で稼働しているようです。SQL Server ではレコードのデータが更新された場合、当該レコードの更新がコミットされるまで排他ロックを持ちます。そのため、データの取得のための共有ロックを取得することができず、ロックの競合が発生し、データの取得がブロックされます。これが SQL Server のデフォルトの動作です。

一向に読み込めないということは、(a) ロックの競合が解決しない、もしくは (b) テーブルのすべてのレコード (n) に対して少なくとも1回の更新が連続的に発生しているため、ロックの競合に連続してあたってしまい、読取りが完了できない、という状況が想定されます。

もし (a) の場合、問題は「あのデータベース (SQL Server)」ではなく、アプリケーションの作り方にあります。きちんと更新のためのトランザクションが終了していれば、ロックの競合が解決されデータの取得が行われます。Oracle Database では「文レベル読取り一貫性」が適用されていますので、データの取得を正常に行うことが可能です。

もし (b) の場合、「あのデータベース (SQL Server)」では、更新処理に1秒かかるとすると、少なくとも n 秒後にはデータの取得が行われます。Oracle Database では「文レベル読取り一貫性」が適用されていますので、データの取得は行えていますが、レポートとして利用するべきものにもかかわらずそのデータは「SELECT 文発行時点」のものであり 「最新ではない」データに基づいたレポートになります。

もし (a) & (b) の場合、お手上げです。「あのデータベース (SQL Server)」ではロックの競合が解消されず、データの取得が行えません。Oracle Database では、n の数にも依存しますが、 ORA-01555 のエラーが発生する危険性がかなり高くなります。

前提では「事業部のほぼ全員があのデータベースにアクセスし更新処理を行っている」かつ「Oracle ではデータ取得がサクサクと短時間で終わっている」かつ「あのデータベースではデータ取得が進まない」とされていますので、条件は (a) & (b) といえるでしょう。つまり

  • 「あのデータベース (SQL Server)」
    未コミットのデータが存在するため、ロックの競合が解消されずデータが取得できない。ただし、これはアプリケーション側でデータの更新後にコミットを行わないことが原因である。コミットが行われていれば n 秒後にはデータ取得が完了する。
  • Oracle Database
    「文レベル読取り一貫性」により、SELECT 文発行時点のデータが取得される。ただし、多数実行されている更新の結果は反映されていないレポートになる。

ということができます。もし Oracle Database から作成されたアドホック レポートの結果が許容されるのであれば、PowerPivot でのデータ取得時に SELECT 文に NOLOCK ヒントを付加することで同レベルの処理を実行することができるでしょう。

[結論] データが読み込めないのは「文レベル読取り一貫性」のためではない。「アプリケーションの作り方」が原因である。データ更新後にはコミットもしくはロールバックを発行する。もちろん、SQL Server では、デフォルトでトランザクション終了時には暗黙のコミットが行われるため、そもそもトランザクションを CLOSE しない、という稀有なアプリケーションでない限りこのような事態に陥ることはない。 なお「非常に高い更新頻度」が原因である場合には、レコード数や頻度に依存して時間がかかるが「読み込めない」という事象には至らない。時間短縮のためには「NOLOCK ヒント」を利用することができる。

 

[本項目における突っ込みどころ]

  1. 「前提」に書かれているテーブル群からすると、当該システムは「受注管理システム」のようです。これから「売上分析レポート」を作成するというのは理にかなっています。しかし、「月末のため、事業部のほぼ全員がデータベースにアクセスして顧客毎の営業ステータスを更新するために更新処理を行っている」というワークロードは「受注管理システム」に対してのワークロードとしては不適切なのではないでしょうか。どちらかといえば営業管理システムや顧客管理システムの範疇ではないかと。そもそも営業ステータスを格納するようなテーブルは対象のテーブルの中には見当たりません。仮に「月末に受注処理が集中する」としても、発生するワークロードは新しい受注レコード及び明細レコードの挿入であり、この場合、コミット済みのデータに関しては正しく読み取ることが可能です。
  2. 都市伝説本文の図には「データに一貫性がない = ダーティリード」という記載がありますが、「読取り一貫性」で提供される一貫性とは通常「文レベル読取り一貫性」であり、コミット済みデータを読み取る際に、SELECT 文が発行された時点の一貫性が保たれていることを意味しています。一方「ダーティリード」とは、ロールバックすると存在しないレコードを読み込むことを意味しています。読取り一貫性とはそもそも異なる概念です。もちろん、読取り一貫性は同時実行性を高めることは可能ですが、アドホック レポートを複数作成した場合、その結果は共通のものとはならない可能性が高くなります。
  3. 売上分析レポートが標準の定型レポートに含まれていない、ということがそもそもあるのかどうか…。
  4. アドホックな売上分析レポートが必要なのであれば、SQL Server 2008 R2 PowerPivot for Excel より Report Builder 3.0 のほうが適切ですね。こちらも SQL Server だけではなく、Oracle Database 他のデータベースに接続することが可能です。