レッスン 1：繰り返し不可能読み出しの作成

♦ 繰り返し不可能読み出しの作成

Interactive SQL を起動します。
[接続] ウィンドウで、Sales Manager として SQL Anywhere サンプルデータベースに接続します。
1. [アクション] ドロップダウンリストで、[ODBC データソースを使用した接続] をクリックします。
2. [ODBC データソース名] をクリックし、その下のフィールドに SQL Anywhere 12 Demo と入力します。
3. 必要に応じて、[詳細] をクリックし、[詳細オプション] タブを表示します。
4. [詳細オプション] タブをクリックし、[ConnectionName] フィールドに Sales Manager と入力します。
5. [接続] をクリックします。
Interactive SQL をもう 1 つ起動します。
[接続] ウィンドウで、Accountant として SQL Anywhere サンプルデータベースに接続します。
1. [アクション] ドロップダウンリストで、[ODBC データソースを使用した接続] をクリックします。
2. [ODBC データソース名] をクリックし、その下のフィールドに SQL Anywhere 12 Demo と入力します。
3. 必要に応じて、[詳細] をクリックし、[詳細オプション] タブを表示します。
4. [詳細オプション] タブをクリックし、[ConnectionName] フィールドに Accountant と入力します。
5. [接続] をクリックします。
次の文を実行して、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 番目のトランザクションは待機します。

参照

DocCommentXchange で意見交換できます