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.

Prerequisites

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

As the Sales Manager, execute the following statement to enable snapshot isolation for the database:
SET OPTION PUBLIC.allow_snapshot_isolation = 'ON';

As the Sales Manager, raise the price of all the tee shirts by $0.95:

Execute the following statement to update the price:

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

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

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

As the Sales Manager, commit your changes to the database by executing the following statement:
COMMIT;

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.

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;

Results

The Accountant successfully avoided dirty reads by enabling snapshot isolation.

Next

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

Discuss this page in DocCommentXchange.