Tutorial: Understanding 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.

Note

For this tutorial to work properly, the Automatically Release Database Locks option must not be selected in Interactive SQL (Tools » Options » SQL Anywhere).

In this example, you assume the role of two employees, both using the SQL Anywhere sample database concurrently.

 Create a dirty read

  1. Start Interactive SQL.

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

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

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

    • Click Advanced to reveal Advanced Options tab.

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

    • 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:

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

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

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

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

    • Click Connect.

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

    • In the Sales Manager window, execute the following statements: 

      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 statement 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 tee shirt by $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.

  7. As the Sales Manager, fix the error by rolling back your first changes and entering the correct UPDATE statement. Check that your new values are correct. 

    ROLLBACK;
UPDATE Products
SET UnitPrice = UnitPrice + 0.95
WHERE NAME = 'Tee Shirt';
    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.

  10. (optional) Restore the sample database (demo.db) to its original state by following the steps found here: Recreate the sample database (demo.db).

 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 Action dropdown list, click Connect With An ODBC Data Source.

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

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

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

    • Click 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 Action dropdown list, click Connect With An ODBC Data Source.

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

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

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

    • Click Connect.

  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 statement:

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

  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;

  11. (optional) Restore the sample database (demo.db) to its original state by following the steps found here: Recreate the sample database (demo.db).

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1