本稿はデータベースソフトウェア「SQL Anywhere」およびデータベース全般に関する英語ドキュメントを翻訳する形で提供しています。図など、部分的に英語のままになっていますが、製品のSQL Anywhere自体は完全に日本語化されていますのでご安心ください。
私は以前の記事で、スナップショットアイソレーションの使用に関するトレードオフについて解説し、マテリアライズドビューの時間空間トレードオフに関する資料を紹介しました。スナップショットアイソレーションについての記事では、私は次のように書きました。
もちろん、スナップショットアイソレーションもタダではありません。データベースシステムは、新規のスナップショットトランザクションを見越して、変更されたデータのアーカイブコピーを作成しなければならないのです。SQL Anywhereでは、スナップショットのローのコピーは自動的に管理され、必要に応じて一時ファイルに書き込まれます（一時ファイルのサイズは必要に応じて増加）。管理上の影響はほぼゼロですが、クエリのパフォーマンスには影響が出る可能性があります。スナップショットのローを、トランザクションのスナップショットセマンティクスに従って、一時ファイルに保存されているスナップショットのローから個別にフェッチしなければならないためです。パフォーマンスがどの程度低下するかは、ひとえにアプリケーションとそのワークロードに依ります。更新量が多い場合には、パフォーマンスの低下はひどくなるでしょう。
今回の記事では、特にSQL Anywhereサーバのトランザクションログとの関連で、スナップショットアイソレーションとマテリアライズドビューの関係を解説し、そのトレードオフについても取り上げようと思います。
マテリアライズドビューをリフレッシュする
遅延メンテナンス型のマテリアライズドビューの場合、マテリアライズドビューの基となるベーステーブルへの変更は、追加のロックや（即時）メンテナンスのオーバヘッドなしに進行していきます。つまり、更新トランザクションはベーステーブルの値またはローを変更し、COMMITの時点でその変更を確定します。しかしこの状態では、マテリアライズドビューの内容は古くなってしまいます。このようなマテリアライズドビューの古い内容をSQL文が利用できるかどうかは、そのクエリの接続に関するオプション設定に左右されます。ビューの内容をリフレッシュするには、そのビューに対してREFRESH MATERIALIZED VIEW文を発行します。このSQL文は、事実上、対象のビューを含んでいるベーステーブルに対してTRUNCATE TABLEを実行した後、すぐさまINSERT ... FROM SELECTを実行してマテリアライズドビューにデータを挿入します。
一方、即時メンテナンス型のマテリアライズドビューの場合には、最初にビューにデータを挿入する時にだけREFRESH MATERIALIZED VIEW文が必要です。それ以降は、基となるベーステーブルに変更を加えたときに、同じトランザクション内で、そのベーステーブルを参照している即時メンテナンス型のマテリアライズドビューに変更が適用されます。このトランザクションは、完了時にCOMMITを実行して変更を確定するか、ROLLBACKを発行して変更を取り消します。
例
ここで、簡単な例を考えてみましょう。サンプルデータベースDEMOのProductsテーブルを基にして、簡単な単一テーブルの即時メンテナンス型マテリアライズドビューを作成します。
CREATE MATERIALIZED VIEW groupo.shirt_products( prod_id, prod_name, prod_description, prod_size, prod_color, prod_quantity, prod_unit_price) AS SELECT "id", "name", "description", "size", color, quantity, unitprice FROM Products WHERE "name" LIKE '%shirt%'
ビューを即時メンテナンス型にするには、まずビューをインスタンス化したテーブルに対して一意のインデックスを作成します。
CREATE UNIQUE INDEX products ON groupo.shirt_products (prod_id ASC);
次に、このビューに即時メンテナンスが適用されるように設定します。
ALTER MATERIALIZED VIEW groupo.shirt_products IMMEDIATE REFRESH
そして最後にマテリアライズドビューを初期化します。
REFRESH MATERIALIZED VIEW groupo.shirt_products
マテリアライズドビューの作成が完了したので、新規のトランザクションを開始し、Productsベーステーブルの基となるローをいくつか変更してみます。
UPDATE products SET DESCRIPTION = 'Modified' WHERE "name" LIKE '%Tee Shirt%'; COMMIT
マテリアライズドビューの内容を見ると、基になるProductsテーブルに対して行った変更がビューに反映されていることがわかります。
マテリアライズドビューとトランザクションログ
この時点でDBTRANユーティリティを使ってDEMOデータベースのトランザクションログの内容を見ると、次のようになっています。
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
--CONNECT-1010-0000975095-DBA-2011-02-02 11:38
--BEGIN TRANSACTION-1010-0000975106
BEGIN TRANSACTION
GO
--SQL-1010-0000975109
BEGIN
SET TEMPORARY OPTION first_day_of_week = '7';
SET TEMPORARY OPTION date_order = 'YMD';
SET TEMPORARY OPTION nearest_century = '50';
SET TEMPORARY OPTION date_format = 'YYYY-MM-DD';
SET TEMPORARY OPTION timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSS';
SET TEMPORARY OPTION time_format = 'HH:NN:SS.SSS';
SET TEMPORARY OPTION default_timestamp_increment = '1';
SET TEMPORARY OPTION timestamp_with_time_zone_format = 'YYYY-MM-DD HH:NN:SS.SSS+HH:NN';
CREATE materialized VIEW groupo.shirt_products( prod_id,prod_name,prod_description,prod_size,prod_color,prod_quantity,prod_unit_price )
AS SELECT products.id,products.name,products.description,products.SIZE,products.color,products.quantity,products.unitprice
FROM GROUPO.products
WHERE products.name LIKE '%shirt%';
SET TEMPORARY OPTION first_day_of_week = ;
SET TEMPORARY OPTION date_order = ;
SET TEMPORARY OPTION nearest_century = ;
SET TEMPORARY OPTION date_format = ;
SET TEMPORARY OPTION timestamp_format = ;
SET TEMPORARY OPTION time_format = ;
SET TEMPORARY OPTION default_timestamp_increment = ;
SET TEMPORARY OPTION timestamp_with_time_zone_format = ;
END
GO
--COMMIT-1010-0000976304
COMMIT WORK
GO
--BEGIN TRANSACTION-1010-0000976307
BEGIN TRANSACTION
GO
--SQL-1010-0000976310
comment TO preserve format ON VIEW groupo.shirt_products IS
'create materialized view groupo.shirt_products( prod_id, prod_name, prod_description, prod_size, prod_color, prod_quantity, prod_unit_price)
as select "id", "name", "description", "size", color, quantity, unitprice
from products
where "name" like ''%shirt%'''
GO
--COMMIT-1010-0000976661
COMMIT WORK
GO
--BEGIN TRANSACTION-1010-0000976664
BEGIN TRANSACTION
GO
--SQL-1010-0000976667
CREATE UNIQUE INDEX products ON groupo.shirt_products(prod_id ASC)
GO
--COMMIT-1010-0000976745
COMMIT WORK
GO
--BEGIN TRANSACTION-1010-0000976748
BEGIN TRANSACTION
GO
--SQL-1010-0000976751
ALTER materialized VIEW groupo.shirt_products immediate refresh
GO
--COMMIT-1010-0000976826
COMMIT WORK
GO
--CHECKPOINT-0000-0000976829-2011-02-02 11:39
--BEGIN TRANSACTION-1010-0000976859
BEGIN TRANSACTION
GO
--SQL-1010-0000976862
refresh materialized VIEW groupo.shirt_products
GO
--COMMIT-1010-0000976921
COMMIT WORK
GO
--BEGIN TRANSACTION-1010-0000976924
BEGIN TRANSACTION
GO
--UPDATE-1010-0000977130
UPDATE GROUPO.Products
SET Description='Modified'
WHERE ID=300
GO
--UPDATE-1010-0000977152
UPDATE GROUPO.Products
SET Description='Modified'
WHERE ID=301
GO
--UPDATE-1010-0000977174
UPDATE GROUPO.Products
SET Description='Modified'
WHERE ID=302
GO
--COMMIT-1010-0000977494
COMMIT WORK
GO
|
上に示したトランザクションログの内容に関して、指摘しておきたいことがいくつかあります。
-
CREATE MATERIALIZED VIEW文が含まれているバッチ（18～40行目）に注目してください。ここでは、CREATE MATERIALIZED VIEW文の発行に必要だった接続オプションが再設定されています。 -
このトランザクションログでは、
REFRESH MATERIALIZED VIEW文が独立した文として記録されています（85行目）。デフォルトでは、REFRESH MATERIALIZED VIEW文はWITH句が指定されていなければWITH SHARE MODEロックを使用します。即時メンテナンス型のビューで使用できるロックモードは、WITH SHARE MODEとWITH EXCLUSIVE MODEとWITH ISOLATION LEVEL SERIALIZABLEだけです。 -
shirt_productsマテリアライズドビューのローの変更は、トランザクションログには現れていません。このビューは即時メンテナンスされるビューなので、トランザクションログには、
UPDATE文によるベーステーブルの変更（93～106行目）だけが記録されていれば十分です。このログがリカバリー処理で再生される場合は、トランザクションログ内の3つのUPDATE文によってマテリアライズドビューへの変更が自動的に呼び出されます。
スナップショットアイソレーションの影響
SQL Anywhereデータベースにおいてスナップショットアイソレーションを可能にするには、次のようにallow_snapshot_isolationオプションを設定します。
SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
スナップショットアイソレーションを有効にすると、REFRESH MATERIALIZED VIEW文のデフォルトの独立性レベル設定はWITH ISOLATION LEVEL SNAPSHOTになります。そのため、このREFRESH文を実行する接続は、対象マテリアライズドビューのベーステーブルを同時に変更しようとしている他の同時更新トランザクションによってブロックされることはありません。
これは利点ですが、トレードオフも存在します。トランザクションログには、どんな状態のバックアップに対してもこのログを使って正確にリカバリーできるように、データベースへの変更に関する十分に詳細な情報が含まれていなければなりません。REFRESH MATERIALIZED VIEW WITH ISOLATION LEVEL SNAPSHOT文を使用した場合、この文は、基となるベーステーブルのローをスナップショットのセマンティクスに従って「見る」ことになります。言い換えると、このREFRESH文は、REFRESH文の開始後に行われたコミット済みトランザクションによって変更されたローを「見る」ことはできないということです。リカバリー時にこのREFRESH文をトランザクションログから正しく再生するには、REFRESH文がマテリアライズドビューの内容を正しく計算できるように、サーバが基となるすべてのベーステーブルのすべてのローの状態を保持している必要があります。
同様の現象は、REFRESH MATERIALIZED VIEW文をSERIALIZABLE、SHARE MODE、EXCLUSIVE MODE以外の独立性レベルで実行したときにも起こりえます。必要なコンテキストのすべてをトランザクションログに提供しようとするよりも、REFRESH MATERIALIZED VIEW文が比較的低い独立性レベルで実行されたときは、サーバは（REFRESH文のみではなく）REFRESH文とマテリアライズドビューに挿入された個々のローの両方をログに記録します。
この場合、DBTRANユーティリティによって生成されたSQL文を見ると、DBTRANの出力にはREFRESH MATERIALIZED VIEW文しか含まれていないはずです。なぜなら、ログをSQLに変換して別のデータベースに適用する際にはREFRESH文さえあれば十分だからです。しかしリカバリーの際には、サーバはこのREFRESH MATERIALIZED VIEW文を無視して、ログ内の個々のINSERT文を利用してマテリアライズドビューをリカバリーします。
現在、我々はDBTRANの機能強化を検討しており、このようなケースでログに記録されるINSERT文をDBTRANの出力内にコメントとして出現させ、その存在を確認できるようにしようと考えています。
