Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (日本語) » SQL Anywhere サーバ - SQL の使用法 » データベースの作成 » トランザクションと独立性レベルの使用 » 独立性レベルのチュートリアル

 

チュートリアル:ダーティ・リード

このチュートリアルでは、複数のトランザクションを同時に実行するときに発生する矛盾の 1 つを再現してみます。小規模の商品販売会社で、2 人の従業員が、会社のデータベースに同時にアクセスすると仮定します。1 人は会社の Sales Manager で、もう 1 人は Accountant です。

Sales Manager は、会社で販売している T シャツの価格を 0.95 ドル上げようとしていますが、SQL 言語の構文に少し問題があります。それと同時に、Sales Manager が知らないうちに、Accountant が現在の在庫の小売り価格を計算し、次の管理ミーティングに提出するレポートに記載しようとしています。

ヒント

データベースを次の方法で変更するときには、UPDATE の代わりに SELECT を使用して変更内容をテストしてから変更した方が賢明です。

注意

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

この例では、2 人の従業員が同時に SQL Anywhere サンプル・データベースを使用するケースを示します。

  1. Interactive SQL を起動します。

  2. [接続] ウィンドウで、Sales Manager として SQL Anywhere サンプル・データベースに接続します。

    • [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。

    • [詳細] タブをクリックし、[接続名] フィールドに Sales Manager と入力します。

    • [OK] をクリックします。

  3. Interactive SQL をもう 1 つ起動します。

  4. [接続] ウィンドウで、Accountant として SQL Anywhere サンプル・データベースに接続します。

    • [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。

    • [詳細] タブをクリックし、[接続名] フィールドに Accountant と入力します。

    • [OK] をクリックします。

  5. Sales Manager として、すべての T シャツの価格を 0.95 ドル上げます。

    • [Sales Manager] ウィンドウで次のコマンドを実行します。

      UPDATE Products
         SET UnitPrice = UnitPrice + 95
         WHERE Name = 'Tee Shirt';
      SELECT ID, Name, UnitPrice
         FROM Products;

    結果は次の表のようになります。

    ID Name UnitPrice
    300 Tee Shirt 104.00
    301 Tee Shirt 109.00
    302 Tee Shirt 109.00
    400 Baseball Cap 9.00
    ... ... ...

    ここで、95 ではなく 0.95 を入力しなくてはならなかったことに気が付きます。しかし、間違いを訂正する前に、Accountant が別のオフィスからそのデータベースにアクセスしてきました。

  6. Accountant は、在庫額が多いことを懸念しています。Accountant として次のコマンドを実行し、全在庫品の小売り価格の合計を計算します。

    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;

    結果は次の表のようになります。

    Inventory
    21453.00

    残念ながら、この計算は正確ではありません。Sales Manager が誤って T シャツの価格を 95 ドル上げてしまったため、合計に誤りがあります。このような誤りは、「ダーティ・リード」と呼ばれる典型的な矛盾の例です。Accountant であるあなたは、Sales Manager が入力したデータにアクセスしますが、このデータはまだコミットされていません。

    ダーティ・リードやその他の矛盾の防止については、独立性レベルと一貫性を参照してください。

  7. Sales Manager として、最初の変更をロールバックし、正しい UPDATE コマンドを入力して間違いを訂正します。新しく入力した値が正しいかをチェックします。

    ROLLBACK;
    UPDATE Products
    SET UnitPrice = UnitPrice + 0.95
    WHERE NAME = 'Tee Shirt';
    COMMIT;
    ID Name UnitPrice
    300 Tee Shirt 9.95
    301 Tee Shirt 14.95
    302 Tee Shirt 14.95
    400 Baseball Cap 9.00
    ... ... ...
  8. Accountant は、計算した値に誤りがあったことに気づきません。Accountant のウィンドウでもう一度 SELECT 文を実行すると、正しい値が表示されます。

    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;
    Inventory
    6687.15
  9. Sales Manager のウィンドウでのトランザクションを終了します。Sales Manager は COMMIT 文を入力して変更を確定できますが、ここでは、ROLLBACK を実行して、SQL Anywhere サンプル・データベースのローカル・コピーが変更されないようにします。

    ROLLBACK;

Accountant は、データベース・サーバが Sales Manager と Accountant の作業を同時に処理しているため、知らない間に間違った情報を受け取っています。

スナップショット・アイソレーションを使用したダーティ・リードの回避

スナップショット・アイソレーションを使用すると、他のデータベース接続は、クエリの応答でコミットされたデータだけを認識します。独立性レベルを statement-snapshot または snapshot に設定すると、ダーティ・リードが発生する可能性がなくなります。Accountant は、スナップショット・アイソレーションを使用して、クエリの実行時にコミットされたデータだけが認識されるようにできます。

  1. Interactive SQL を起動します。

  2. [接続] ウィンドウで、Sales Manager として SQL Anywhere サンプル・データベースに接続します。

    • [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。

    • [詳細] タブをクリックし、[接続名] フィールドに Sales Manager と入力します。

    • [OK] をクリックして接続します。

  3. 次の文を実行し、データベースのスナップショット・アイソレーションを有効にします。

    SET OPTION PUBLIC.allow_snapshot_isolation = 'ON';
  4. Interactive SQL をもう 1 つ起動します。

  5. [接続] ウィンドウで、Accountant として SQL Anywhere サンプル・データベースに接続します。

    • [ODBC データ・ソース名] フィールドで、[SQL Anywhere 11 Demo] を選択します。

    • [詳細] タブをクリックし、[接続名] フィールドに Accountant と入力します。

    • [OK] をクリックします。

  6. Sales Manager として、すべての T シャツの価格を 0.95 ドル上げます。

    • [Sales Manager] ウィンドウで次のコマンドを実行します。

      UPDATE Products
      SET UnitPrice = UnitPrice + 0.95
      WHERE Name = 'Tee Shirt';
    • Sales Manager 用の新しい T シャツ価格を使用して、全在庫品の小売り価格の合計を計算します。

      SELECT SUM( Quantity * UnitPrice )
       AS Inventory
         FROM Products;

      結果は次の表のようになります。

      Inventory
      6687.15
  7. Accountant として次のコマンドを実行し、全在庫品の小売り価格の合計を計算します。このトランザクションは snapshot 独立性レベルを使用するため、データベースにコミットされたデータのみで結果が計算されます。

    SET OPTION isolation_level = 'Snapshot';
    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;

    結果は次の表のようになります。

    Inventory
    6538.00
  8. Sales Manager として次の文を実行し、データベースに対する変更をコミットします。

    COMMIT;
  9. Accountant として次の文を実行し、現在の在庫の更新後の小売り価格を表示します。

    COMMIT;
    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;

    結果は次の表のようになります。

    Inventory
    6687.15

    Accountant のトランザクションで使用されるスナップショットは最初の読み込み操作で開始するため、COMMIT を実行してトランザクションを終了し、スナップショット・トランザクションの開始後に加えられたデータの変更を Accountant が確認できるようにする必要があります。スナップショット・トランザクションの知識を参照してください。

  10. Sales Manager として次の文を実行し、T シャツの価格の変更を取り消し、SQL Anywhere サンプル・データベースを元の状態に復元します。

    UPDATE Products
    SET UnitPrice = UnitPrice - 0.95
    WHERE Name = 'Tee Shirt';
    COMMIT;