Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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.

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

1. Start Interactive SQL.

2. Connect to the SQL Anywhere sample database as the Sales Manager:

   • In the Connect dialog, choose the SQL Anywhere 10 Demo ODBC data source.

   • To make the window easier to identify, click the Advanced tab, and then type Sales Manager in the Connection Name field.

   • Click OK to connect.

3. Start a second instance of Interactive SQL.

4. Connect to the SQL Anywhere sample database as the Accountant:

   • In the Connect dialog, choose the SQL Anywhere 10 Demo ODBC data source.

   • To make the window easier to identify, click the Advanced tab, and then type Accountant in the Connection Name field.

   • Click OK to connect.

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

   • In the window labeled Sales Manager, 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.

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. Connect to the SQL Anywhere sample database as the Sales Manager:

   • In the Connect dialog, choose the SQL Anywhere 10 Demo ODBC data source.

   • On the Advanced tab, type Sales Manager in the Connection Name field to make the window easier to identify.

   • 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. Connect to the SQL Anywhere sample database as the Accountant:

   • In the Connect dialog, choose the SQL Anywhere 10 Demo ODBC data source.

   • On the Advanced tab, type Accountant in the Connection Name field to make the window easier to identify.

   • Click OK to 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 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;