チュートリアル：繰り返し不可能読み出し

チュートリアル：ダーティ・リードの例では、矛盾のケースとして、まずダーティ・リードを取り上げました。その中では、Sales Manager が価格を更新している間に、Accountant が計算を実行してしまいました。Accountant は、Sales Manager が入力後に訂正を加えている過程の間違った情報を使用して計算しました。

次の例では、別のタイプの矛盾を説明します。これは、「繰り返し不可能読み出し」というものです。この例では、前の例と同じ 2 人の従業員が同時に SQL Anywhere サンプル・データベースを使用するケースを示します。Sales Manager はプラスチック・バイザーに新しい価格を設定すると仮定します。Accountant は最近注文があったいくつかの品目の価格を調べるとします。

この例では、独立性レベル 0 ではなく 1 を使って、両方の接続を開始します。独立性レベル 0 は SQL Anywhere に付属の SQL Anywhere サンプル・データベースのデフォルト値です。独立性レベルを 1 に設定して、前のチュートリアルで示した一貫性が失われるケース (ダーティ・リード) を防止します。

注意

このチュートリアルが正常に動作するためには、Interactive SQL ([ツール] - [オプション] - [SQL Anywhere]) の[データベース・ロックの自動解放] オプションをオフにする必要があります。

Interactive SQL を起動します。
[接続] ウィンドウで、Sales Manager として SQL Anywhere サンプル・データベースに接続します。
- [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。
- [詳細] タブをクリックし、[接続名] フィールドに Sales Manager と入力します。
- [OK] をクリックします。
Interactive SQL をもう 1 つ起動します。
[接続] ウィンドウで、Accountant として SQL Anywhere サンプル・データベースに接続します。
- [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。
- [詳細] タブをクリックし、[接続名] フィールドに Accountant と入力します。
- [OK] をクリックします。
次のコマンドを実行して、Accountant の接続の独立性レベルを 1 に設定します。
SET TEMPORARY OPTION isolation_level = 1;
次のコマンドを実行して、Sales Manager のウィンドウに独立性レベル 1 を設定します。
SET TEMPORARY OPTION isolation_level = 1;

Accountant は、バイザーの価格をリストすることにします。Accountant として、次のコマンドを実行します。

SELECT ID, Name, UnitPrice FROM Products;

ID	Name	UnitPrice
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
401	Baseball Cap	10.00
500	Visor	7.00
501	Visor	7.00
...	...	...

Sales Manager は、プラスチック・バイザーに新価格を導入することにします。Sales Manager として、次のコマンドを実行します。

SELECT ID, Name, UnitPrice FROM Products
WHERE Name = 'Visor';
UPDATE Products
SET UnitPrice = 5.95 WHERE ID = 501;
COMMIT;
SELECT ID, Name, UnitPrice FROM Products
WHERE Name = 'Visor';

ID	Name	UnitPrice
500	Visor	7.00
501	Visor	5.95

Sales Manager ウィンドウでのバイザーの価格と Accountant ウィンドウでの価格を比較してみてください。Accountant が SELECT 文をもう一度実行すると、Sales Manager の新価格が表示されます。

SELECT ID, Name, UnitPrice
FROM Products;

ID	Name	UnitPrice
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
401	Baseball Cap	10.00
500	Visor	7.00
501	Visor	5.95
...	...	...

この矛盾を「繰り返し不可能読み出し」と呼んでいます。Accountant が 2 回目も同じトランザクションで同じ SELECT 文を実行しても、同じ結果が得られないためです。

もちろん、Accountant が SELECT コマンドを再度使用する前に、COMMIT や ROLLBACK コマンドなどを発行してトランザクションを終了した場合には、状況は違ってきます。データベースは複数のユーザが同時に使用でき、Accountant のトランザクションの前後に他の人が値を変更できます。他の人が行った変更によって矛盾が生じるのは、Accountant のトランザクションの途中で変更が行われるためです。そうしたイベントにより、スケジュールは直列化不可能となります。

Accountant はこれに気づき、以降は価格参照中にほかからの変更を防ぐことにします。繰り返し不可読み出しは、独立性レベル 2 で削除されます。Accountant として、次の文を実行します。

SET TEMPORARY OPTION isolation_level = 2;
SELECT ID, Name, UnitPrice
FROM Products;

Sales Manager は、明日受注するはずの大口注文に値下げを適用しなくて済むように、プラスチック・バイザーの安売りを来週に延期することを決定します。Sales Manager のウィンドウで、次の文を実行します。コマンドの実行が始まりますが、ウィンドウがフリーズします。
UPDATE Products SET UnitPrice = 7.00 WHERE ID = 501;
データベース・サーバは独立性レベル 2 では繰り返し可能読み出しを保証する必要があります。Accountant は独立性レベル 2 を使用するため、データベース・サーバは Accountant が読み込む Products テーブルの各ローに読み込みロックをかけます。Sales Manager が価格を元に戻そうとすると、そのトランザクションは、Products テーブルのプラスチック・バイザーのローに書き込みロックをかける必要があります。書き込みロックは排他ロックであるため、Accountant のトランザクションが読み込みロックを解放するまで待機しなければなりません。

Accountant が価格の閲覧を終了しました。データベースを誤って変更するのを防ぐために、ROLLBACK 文でトランザクションを完了します。

ROLLBACK;

データベース・サーバがこの文を実行すると、Sales Manager のトランザクションが完了します。

ID	Name	UnitPrice
500	Visor	7.00
501	Visor	7.00

Sales Manager も処理を終了できるようになりました。Sales Manager は、変更をコミットして、元の価格をリストアします。
COMMIT;

ロックの種類と各種の独立性レベル

Accountant の独立性レベルを 1 から 2 に更新したときに、データベース・サーバは、前に誰もロックをかけたことのない場所で読み込みロックを使用しました。一般的に、各独立性レベルは、必要なロックの種類や、他のトランザクションが保持するロックをどのように扱うかによって特徴づけられます。

独立性レベルが 0 の場合、データベース・サーバは書き込みロックだけを必要とします。データベース・サーバは、これらのロックを使用して、2 つのトランザクションが競合する修正を行わないようにします。たとえば、レベル 0 のトランザクションは、ローの更新や削除をする前に書き込みロックをかけ、すでに書き込みロックがかかっている新しいローを挿入します。

レベル 0 のトランザクションは、読み込み中のローはチェックしません。たとえば、レベル 0 のトランザクションがローを読み込むときは、他のトランザクションがそのローにどのようなロックをかけているかをチェックしません。チェックが不要のため、レベル 0 のトランザクション処理は速くなります。この速度は一貫性を犠牲にして得られたものです。別のトランザクションが書き込みロックをかけているローを読むと、ダーティ・データを返す危険性があります。

レベル 1 では、トランザクションはローを読む前に書き込みロックがかかっていないかをチェックします。操作は 1 つ増えますが、このトランザクションでは読み込むデータはすべてコミット済みであることが保証されます。独立性レベルを 0 ではなく 1 に設定し、最初のチュートリアルを繰り返してみます。Sales Manager が T シャツの価格を更新するトランザクションの最中は、Accountant の計算は処理を進めることができず、不完全なままになることがわかります。

Accountant が独立性レベルを 2 に上げたとき、データベース・サーバは読み込みロックの使用を開始しました。それ以降、選択内容に適合するローごとに読み込みロックをかけました。

トランザクションのブロック

前述のチュートリアルでは、UPDATE コマンドの実行中に Sales Manager のウィンドウがフリーズしました。データベース・サーバは UPDATE コマンドの実行を開始し、Sales Manager が変更を必要としているローに Accountant のトランザクションが読み込みロックをかけていることを発見しました。この時点で、データベース・サーバは UPDATE の実行を一時停止します。Accountant が ROLLBACK でトランザクションを終了すると、データベース・サーバは自動的にロックを解放します。妨げがなくなると、Sales Manager の UPDATE が最後まで処理されます。

ロックの競合が発生するのは、一般に、あるトランザクションが、別のトランザクションがロックをかけているローに排他ロックをかけようとした場合や、別のトランザクションが排他ロックをかけているローに共有ロックをかけようとした場合です。このような場合、その「別のトランザクション」の完了を待たなければなりません。待機しなければならないトランザクションは、もう一方のトランザクションに「ブロック」されたといいます。

データベース・サーバが、トランザクションの即時処理を禁止するロックの競合を認識すると、トランザクションの実行を一時停止するか、またはトランザクションを終了し、変更をロールバックし、エラーを返すことができます。ブロック・オプションを設定して、その手段を制御します。ブロック・オプションが ON に設定されていると、前述のチュートリアルで説明したように 2 番目のトランザクションは待機します。

ブロック・オプションの詳細については、ブロック・オプションを参照してください。

スナップショット・アイソレーションを使用した繰り返し不可能読み出しの回避

スナップショット・アイソレーションを使用してブロックを回避することもできます。スナップショット・アイソレーションを使用するトランザクションはコミットされたデータだけを認識するため、Accountant のトランザクションは、Sales Manager のトランザクションをブロックしません。

Interactive SQL を起動します。
[接続] ウィンドウで、Sales Manager として SQL Anywhere サンプル・データベースに接続します。
- [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。
- [詳細] タブをクリックし、[接続名] フィールドに Sales Manager と入力します。
- [OK] をクリックします。
Interactive SQL をもう 1 つ起動します。
[接続] ウィンドウで、Accountant として SQL Anywhere サンプル・データベースに接続します。
- [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。
- [詳細] タブをクリックし、[接続名] フィールドに Accountant と入力します。
- [OK] をクリックします。

次の文を実行して、データベースのスナップショット・アイソレーションを有効にし、snapshot 独立性レベルを使用することを指定します。

SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
SET TEMPORARY OPTION isolation_level = snapshot;

Accountant は、バイザーの価格をリストすることにします。Accountant として、次のコマンドを実行します。

SELECT ID, Name, UnitPrice
FROM Products
ORDER BY ID;

ID	Name	UnitPrice
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
401	Baseball Cap	10.00
500	Visor	7.00
501	Visor	7.00
...	...	...

Sales Manager は、プラスチック・バイザーに新価格を導入することにします。Sales Manager として、次のコマンドを実行します。

UPDATE Products
SET UnitPrice = 5.95 WHERE ID = 501;
COMMIT;
SELECT ID, Name, UnitPrice FROM Products
WHERE Name = 'Visor';

Accountant はクエリをもう一度実行しますが、最初の読み込み時にコミットされたデータがトランザクションで使用されるため、価格の変更を認識しません。
SELECT ID, Name, UnitPrice FROM Products;
Sales Manager として、プラスチック・バイザーを元の価格に戻します。
UPDATE Products SET UnitPrice = 7.00 WHERE ID = 501; COMMIT;
データベース・サーバは、Accountant が読み込み中の Products テーブルのローに読み込みロックをかけません。これは Sales Manager が Products テーブルに変更を加える前に作成された、コミットされたデータのスナップショットを Accountant が閲覧しているためです。
Accountant が価格の閲覧を終了しました。データベースを誤って変更するのを防ぐために、ROLLBACK 文でトランザクションを完了します。
ROLLBACK;