Tutorial: Dirty reads

The following tutorial demonstrates one type of inconsistency that can occur when multiple transactions are executed concurrently. Two employees at a small merchandising company access the corporate database at the same time. The first person is the company's Sales Manager. The second is the Accountant.

The Sales Manager wants to increase the price of tee shirts sold by their firm by $0.95, but is having a little trouble with the syntax of the SQL language. At the same time, unknown to the Sales Manager, the Accountant is trying to calculate the retail value of the current inventory to include in a report he volunteered to bring to the next management meeting.

Tip

Before altering your database in the following way, it is prudent to test the change by using SELECT in place of UPDATE.

In this example, you will play the role of two people, both using the SQL Anywhere sample database concurrently.

  1. Start Interactive SQL.
  2. In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:
    • In the ODBC Data Source Name field, choose SQL Anywhere 11 Demo.
    • Click the Advanced tab, and type Sales Manager in the ConnectionName field.
    • Click OK.
  3. Start a second instance of Interactive SQL.
  4. In the Connect window, connect to the SQL Anywhere sample database as the Accountant:
    • In the ODBC Data Source Name field, choose SQL Anywhere 11 Demo.
    • Click the Advanced tab and type Accountant in the ConnectionName field.
    • Click OK.
  5. As the Sales Manager, raise the price of all tee shirts by $0.95:
    • In the Sales Manager window, execute the following commands: 
      UPDATE Products
   SET UnitPrice = UnitPrice + 95
   WHERE Name = 'Tee Shirt';
SELECT ID, Name, UnitPrice
   FROM Products;

    The result is:

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

    You observe immediately that you should have entered 0.95 instead of 95, but before you can fix your error, the Accountant accesses the database from another office.

  6. The company's Accountant is worried that too much money is tied up in inventory. As the Accountant, execute the following commands to calculate the total retail value of all the merchandise in stock: 
    SELECT SUM( Quantity * UnitPrice )
 AS Inventory
   FROM Products;

    The result is:

    Inventory
    21453.00

    Unfortunately, this calculation is not accurate. The Sales Manager accidentally raised the price of the visor $95, and the result reflects this erroneous price. This mistake demonstrates one typical type of inconsistency known as a dirty read. You, as the Accountant, accessed data which the Sales Manager has entered, but has not yet committed.

    You can eliminate dirty reads and other inconsistencies explained in Isolation levels and consistency.

  7. As the Sales Manager, fix the error by rolling back your first changes and entering the correct UPDATE command. Check that your new values are correct. 
    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. The Accountant does not know that the amount he calculated was in error. You can see the correct value by executing the SELECT statement again in the Accountant's window. 
    SELECT SUM( Quantity * UnitPrice )
 AS Inventory
   FROM Products;
    Inventory
    6687.15
  9. Finish the transaction in the Sales Manager's window. The Sales Manager would enter a COMMIT statement to make the changes permanent, but you should execute a ROLLBACK, instead, to avoid changing the local copy of the SQL Anywhere sample database. 
    ROLLBACK;

The Accountant unknowingly receives erroneous information from the database because the database server is processing the work of both the Sales Manager and the Accountant concurrently.

Using snapshot isolation to avoid dirty reads

When you use snapshot isolation, other database connections see only committed data in response to their queries. Setting the isolation level to statement-snapshot or snapshot prevents the possibility of dirty reads occurring. The Accountant can use snapshot isolation to ensure that they only see committed data when executing their queries.

  1. Start Interactive SQL.
  2. In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:
    • In the ODBC Data Source Name field, choose SQL Anywhere 11 Demo.
    • Click the Advanced tab and type Sales Manager in the ConnectionName field.
    • Click OK to connect.
  3. Execute the following statement to enable snapshot isolation for the database: 
    SET OPTION PUBLIC.allow_snapshot_isolation = 'ON';
  4. Start a second instance of Interactive SQL.
  5. In the Connect window, connect to the SQL Anywhere sample database as the Accountant:
    • In the ODBC Data Source Name field, choose SQL Anywhere 11 Demo.
    • Click the Advanced tab and type Accountant in the ConnectionName field.
    • Click OK.
  6. As the Sales Manager, raise the price of all the tee shirts by $0.95:
    • In the window labeled Sales Manager, execute the following command to: 
      UPDATE 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 Products;

      The result is:

      Inventory
      6687.15
  7. As the Accountant, execute the following command 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 Products;

    The result is:

    Inventory
    6538.00
  8. As the Sales Manager, commit your changes to the database by executing the following statement: 
    COMMIT;
  9. 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 Products;

    The result is:

    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. See Understanding snapshot transactions.

  10. 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 Products
SET UnitPrice = UnitPrice - 0.95
WHERE Name = 'Tee Shirt';
COMMIT;
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0