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.

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.

Start Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:
- From the Action dropdown list, choose Connect With An ODBC Data Source.
- In the ODBC Data Source Name field, choose SQL Anywhere 12 Demo.
- Click Advanced, to reveal Advanced Options tab.
- Click the Advanced Options tab, and type Sales Manager in the ConnectionName field.
- Click Connect.
Start a second instance of Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Accountant:
- From the Action dropdown list, choose Connect With An ODBC Data Source.
- In the ODBC Data Source Name field, choose SQL Anywhere 12 Demo.
- If necessary, click Advanced, to reveal the Advanced Options tab.
- Click the Advanced Options tab and type Accountant in the ConnectionName field.
- Click Connect.

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.

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

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

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';

ID	name	UnitPrice
300	Tee Shirt	9.95
301	Tee Shirt	14.95
302	Tee Shirt	14.95
400	Baseball Cap	9.00
...	...	...

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

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

Start Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:
- From the Action dropdown list, choose Connect With An ODBC Data Source.
- In the ODBC Data Source Name field, choose SQL Anywhere 12 Demo.
- 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.
Execute the following statement to enable snapshot isolation for the database:
SET OPTION PUBLIC.allow_snapshot_isolation = 'ON';
Start a second instance of Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Accountant:
- From the Action dropdown list, choose Connect With An ODBC Data Source.
- In the ODBC Data Source Name field, choose SQL Anywhere 12 Demo.
- If necessary, click Advanced, to reveal the Advanced Options tab.
- Click the Advanced Options tab and type Accountant in the ConnectionName field.
- Click Connect.

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

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

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

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;

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

Discuss this page in DocCommentXchange.