Dirty read tutorial

ASA SQL User's Guide
Using Transactions and Isolation Levels
Isolation level tutorials

Dirty read tutorial

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 demonstration database concurrently.

Start Interactive SQL.
Connect to the sample database as the Sales Manager:
- In the Connect dialog, choose the ODBC data source ASA 9.0 Sample.
- On the Advanced tab, type the following string to make the window easier to identify:
```
   ConnectionName=Sales Manager
```
Click OK to connect.
Start a second instance of Interactive SQL.
Connect to the sample database as the Accountant:
- In the Connect dialog, choose the ODBC data source ASA 9.0 Sample.
- On the Advanced tab, type the following string to make the window easier to identify:
```
   ConnectionName=Accountant
```
- Click OK to 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 commands:

   SELECT id, name, unit_price
   FROM product;
   UPDATE PRODUCT
   SET unit_price = unit_price + 95
   WHERE NAME = 'Tee Shirt'

The result is:

id	name	unit_price
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 * unit_price )
  AS inventory
FROM product
```
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.

inventory
21453.00

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 product
SET unit_price = unit_price + 0.95
WHERE NAME = 'Tee Shirt';

id	name	unit_price
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 his SELECT statement again in his window.
```
SELECT SUM( quantity * unit_price )
  AS inventory
FROM product;
```
inventory
6687.15
Finish the transaction in the Sales Manager's window. She would enter a COMMIT statement to make his changes permanent, but you may wish to enter a ROLLBACK, instead, to avoid changing the copy of the demonstration database on your machine.
```
ROLLBACK;
```

inventory
6687.15

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.