マイクロソフトの北川です。
都市伝説には「ニュース性」「真実味」「オチ」という特徴があり、起源や根拠が全く不明なものが多いとされています。また、何かしらの根拠があるものに関しても、なんでもない事実に尾ひれがついて伝説化したとされています。この都市伝説の問題はそれが「真実」であるかのように語られる点にあります。特定の都市伝説が広く普及した原因は、広く信頼されているソースにより紹介されたことにあるとされています。
閑話休題
日本オラクル社のウェブサイトにおける「オラクル都市伝説」で揶揄されている「あのデータベース」ですが、今回は「PowerPivot と相性がいい」とされているようです。この PowerPivot は、提示されている UI を見るとマイクロソフトが5月1日より提供開始した SQL Server 2008 R2 PowerPivot for Excel (以降 PowerPivot) のようです。
「データの活用」という観点でエンジンでは競合する両社がともに「セルフサービス BI」の重要性を訴求するといういいお話では残念ながらないようです。では、今回はこの「都市伝説」を読み解いてみましょう。
本文中から読み取れる前提とゴール
[ゴール] 売上分析レポートを作成する
[前提]
データが読み込めないのは「文レベル読取り一貫性」によるものなのか!?
まず最初に「あのデータベース」では「一個目のテーブル(仕入先)から一向にデータが取得でいない」とされています。それに対して 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) といえるでしょう。つまり
ということができます。もし Oracle Database から作成されたアドホック レポートの結果が許容されるのであれば、PowerPivot でのデータ取得時に SELECT 文に NOLOCK ヒントを付加することで同レベルの処理を実行することができるでしょう。
[結論] データが読み込めないのは「文レベル読取り一貫性」のためではない。「アプリケーションの作り方」が原因である。データ更新後にはコミットもしくはロールバックを発行する。もちろん、SQL Server では、デフォルトでトランザクション終了時には暗黙のコミットが行われるため、そもそもトランザクションを CLOSE しない、という稀有なアプリケーションでない限りこのような事態に陥ることはない。なお「非常に高い更新頻度」が原因である場合には、レコード数や頻度に依存して時間がかかるが「読み込めない」という事象には至らない。時間短縮のためには「NOLOCK ヒント」を利用することができる。
[本項目における突っ込みどころ]