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 non-repeatable reads

 

Lesson 2: Avoiding non-repeatable reads using snapshot isolation

You can also use snapshot isolation to help avoid blocking. Because transactions that use snapshot isolation only see committed data, the Accountant's transaction does not block the Sales Manager's transaction.

Prerequisites

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

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

 Task
  1. As the Accountant, execute the following statements to enable snapshot isolation for the database and to specify the snapshot isolation level that is used:

    SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
    SET TEMPORARY OPTION isolation_level = 'snapshot';
  2. As the Accountant, execute the following statement to list the prices of the visors:

    SELECT ID, Name, UnitPrice
    FROM GROUPO.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
    ... ... ...
  3. As the Sales Manager, execute the following statements to introduce a new sale price for the plastic visor:

    UPDATE GROUPO.Products
    SET UnitPrice = 5.95 WHERE ID = 501;
    COMMIT;
    SELECT ID, Name, UnitPrice FROM GROUPO.Products
    WHERE Name = 'Visor';
  4. The Accountant executes his query again and does not see the change in price because the data that was committed at the time of the first read is used for the transaction.

    SELECT ID, Name, UnitPrice
    FROM GROUPO.Products;
  5. As the Sales Manager, change the plastic visor back to its original price:

    UPDATE GROUPO.Products
    SET UnitPrice = 7.00
    WHERE ID = 501;
    COMMIT;

    The database server does not place a read lock on the rows in the Products table that the Accountant is reading because the Accountant is viewing a snapshot of committed data that was taken before the Sales Manager made any changes to the Products table.

  6. The Accountant is finished looking at the prices. He doesn't want to risk accidentally changing the database, so he completes his transaction with a ROLLBACK statement.

    ROLLBACK;

Results

You have successfully avoided non-repeatable reads by using snapshot isolation.