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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Transactions and isolation levels » Isolation level tutorials » Tutorial: Understanding dirty reads

 

Lesson 1: Creating a dirty read

Create a dirty read, in which the Accountant makes a calculation while the Sales Manager is in the process of updating a price. The Accountant's calculation uses erroneous information which the Sales Manager enters and is in the process of fixing.

Prérequis

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
  1. As the Sales Manager, execute the following statements to raise the price of all tee shirts by $0.95:

    UPDATE GROUPO.Products
       SET UnitPrice = UnitPrice + 95
       WHERE Name = 'Tee Shirt';
    SELECT ID, Name, UnitPrice
       FROM GROUPO.Products;

    The following result set is returned:

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

    The Sales Manager observes immediately that 0.95 should have been entered instead of 95, but before the error can be fixed, the Accountant accesses the database from another office.

  2. 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 GROUPO.Products;

    The following result is returned:

    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. As the Accountant, you accessed data that the Sales Manager has entered, but has not yet committed.

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

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

    The following result set is returned:

    ID Name UnitPrice
    300 Tee Shirt 9.95
    301 Tee Shirt 14.95
    302 Tee Shirt 14.95
    400 Baseball Cap 9.00
    ... ... ...
  4. 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 GROUPO.Products;
    Inventory
    6687.15
  5. 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 statement instead, to avoid changing the local copy of the SQL Anywhere sample database.

    ROLLBACK;

Résultat

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.