Lesson 2: Avoiding non-repeatable reads

 Using snapshot isolation to avoid non-repeatable reads

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.

  1. Start Interactive SQL.

  2. In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:

    1. From the Action dropdown list, click Connect to an ODBC Data Source.

    2. Click ODBC Data Source Name, and then type SQL Anywhere 12 Demo in the field below.

    3. If necessary, click Advanced, to reveal the Advanced Options tab.

    4. Click the Advanced Options tab and type Sales Manager in the ConnectionName field.

    5. Click Connect.

  3. Start a second instance of Interactive SQL.

  4. In the Connect window, connect to the SQL Anywhere sample database as the Accountant:

    1. In the Action dropdown list, click Connect With An ODBC Data Source.

    2. Click ODBC Data Source Name, and then type SQL Anywhere 12 Demo in the field below.

    3. If necessary, click Advanced button, to reveal the Advanced Options tab.

    4. Click the Advanced Options tab and type Accountant in the ConnectionName field.

    5. Click Connect.

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

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

  6. The Accountant decides to list the prices of the visors. As the Accountant, execute the following statement:

    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
    ... ... ...

  7. The Sales Manager decides to introduce a new sale price for the plastic visor. As the Sales Manager, execute the following statements: 

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

  8. 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 Products;

  9. As the Sales Manager, change the plastic visor back to its original price.

    UPDATE 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.

  10. 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;
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1