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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Transactions and isolation levels » Isolation level tutorials » Tutorial: Understanding dirty reads

 

Lesson 2: Avoiding dirty reads using snapshot isolation

Snapshot isolation prevents dirty reads from occurring by allowing other database connections to only view committed data in response to queries. The Accountant can use snapshot isolation to ensure that uncommitted data does not affect his queries.

Prérequis

This lesson assumes that you have completed all preceding lessons. See Lesson 1: Creating a dirty read.

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Understanding dirty reads.

 Task
  1. As the Sales Manager, execute the following statement to enable snapshot isolation for the database:

    SET OPTION PUBLIC.allow_snapshot_isolation = 'ON';
  2. As the Sales Manager, raise the price of all the tee shirts by $0.95:

    1. Execute the following statement to update the price:

      UPDATE GROUPO.Products
      SET UnitPrice = UnitPrice + 0.95
      WHERE Name = 'Tee Shirt';
    2. Calculate the total retail value of all merchandise in stock using the new tee shirt price for the Sales Manager:

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

      The following result is returned:

      Inventory
      6687.15
  3. As the Accountant, execute the following statements to calculate the total retail value of all the merchandise in stock. Because this transaction uses the snapshot isolation level, the result is calculated only for data that has been committed to the database.

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

    The following result is returned:

    Inventory
    6538.00
  4. As the Sales Manager, commit your changes to the database by executing the following statement:

    COMMIT;
  5. As the Accountant, execute the following statements to view the updated retail value of the current inventory:

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

    The following result is returned:

    Inventory
    6687.15

    Because the snapshot used for the Accountant's transaction began with the first read operation, you must execute a COMMIT to end the transaction and allow the Accountant to see changes made to the data after the snapshot transaction began.

  6. As the Sales Manager, execute the following statement to undo the tee shirt price changes and restore the SQL Anywhere sample database to its original state:

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

Résultat

The Accountant successfully avoided dirty reads by enabling snapshot isolation.

Suivant

(optional) Restore the sample database (demo.db) to its original state. See Recreate the sample database (demo.db).