Non-repeatable read tutorial

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

Non-repeatable read tutorial

The example in Dirty read tutorial demonstrated the first type of inconsistency, namely the dirty read. In that example, an Accountant made a calculation while the Sales Manager was in the process of updating a price. The Accountant's calculation used erroneous information which the Sales Manager had entered and was in the process of fixing.

The following example demonstrates another type of inconsistency: non-repeatable reads. In this example, you will play the role of the same two people, both using the demonstration database concurrently. The Sales Manager wishes to offer a new sales price on plastic visors. The Accountant wishes to verify the prices of some items that appear on a recent order.

This example begins with both connections at isolation level 1, rather than at isolation level 0, which is the default for the demonstration database supplied with Adaptive Server Anywhere. By setting the isolation level to 1, you eliminate the type of inconsistency which the previous tutorial demonstrated, namely the dirty read.

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, enter 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, enter the following string to make the window easier to identify:
```
   ConnectionName=Accountant
```
- Click OK to connect.
Set the isolation level to 1 for the Accountant's connection by executing the following command.
```
SET TEMPORARY OPTION ISOLATION_LEVEL = 1
```
Set the isolation level to 1 in the Sales Manager's window by executing the following command:
```
SET TEMPORARY OPTION ISOLATION_LEVEL = 1
```

The Accountant decides to list the prices of the visors. As the Accountant, execute the following command:

SELECT id, name, unit_price FROM product

id	name	unit_price
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
...	...	...

The Sales Manager decides to introduce a new sale price for the plastic visor. As the Sales Manager, execute the following command:

SELECT id, name, unit_price FROM product
WHERE name = 'Visor';
UPDATE product
SET unit_price = 5.95 WHERE id = 501;
COMMIT;

id	name	unit_price
500	Visor	7.00
501	Visor	5.95

Compare the price of the visor in the Sales Manager window with the price for the same visor in the Accountant window. The Accountant window still displays the old price, even though the Sales Manager has entered the new price and committed the change.

This inconsistency is called a non-repeatable read, because if the Accountant did the same SELECT a second time in the same transaction , he wouldn't get the same results. Try it for yourself. As the Accountant, execute the select command again. Observe that the Sales Manager's sale price now displays.

SELECT id, name, price
FROM product

id	name	unit_price
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
...	...	...

Of course if the Accountant had finished his transaction, for example by issuing a COMMIT or ROLLBACK command before using SELECT again, it would be a different matter. The database is available for simultaneous use by multiple users and it is completely permissible for someone to change values either before or after the Accountant's transaction. The change in results is only inconsistent because it happens in the middle of his transaction. Such an event makes the schedule unserializable.

The Accountant notices this behavior and decides that from now on he doesn't want the prices changing while he looks at them. Repeatable reads are eliminated at isolation level 2. Play the role of the Accountant:
```
SET TEMPORARY OPTION ISOLATION_LEVEL = 2;
SELECT id, name, unit_price
FROM product
```
The Sales Manager decides that it would be better to delay the sale on the plastic visor until next week so that she won't have to give the lower price on a big order that she's expecting will arrive tomorrow. In her window, try to execute the following statements. The command will start to execute, and then his window will appear to freeze.
```
UPDATE product
SET unit_price = 7.00
WHERE id = 501
```
The database server must guarantee repeatable reads at isolation level 2. To do so, it places a read lock on each row of the product table that the Accountant reads. When the Sales Manager tries to change the price back, her transaction must acquire a write lock on the plastic visor row of the product table. Since write locks are exclusive, her transaction must wait until the Accountant's transaction releases its read lock.

The Accountant is finished looking at the prices. He doesn't want to risk accidentally changing the database, so he completes his transaction with a ROLLBACK statement.

ROLLBACK

Observe that as soon as the database server executes this statement, the Sales Manager's transaction completes.

id	name	unit_price
500	Visor	7.00
501	Visor	7.00

The Sales Manager can finish now. She wishes to commit her change to restore the original price.
```
COMMIT
```

Types of Locks and different isolation levels

When you upgraded the Accountant's isolation from level 1 to level 2, the database server used read locks where none had previously been acquired. In general, each isolation level is characterized by the types of locks needed and by how locks held by other transactions are treated.

At isolation level 0, the database server needs only write locks. It makes use of these locks to ensure that no two transactions make modifications that conflict. For example, a level 0 transaction acquires a write lock on a row before it updates or deletes it, and inserts any new rows with a write lock already in place.

Level 0 transactions perform no checks on the rows they are reading. For example, when a level 0 transaction reads a row, it doesn't bother to check what locks may or may not have been acquired on that row by other transactions. Since no checks are needed, level 0 transactions are particularly fast. This speed comes at the expense of consistency. Whenever they read a row which is write locked by another transaction, they risk returning dirty data.

At level 1, transactions check for write locks before they read a row. Although one more operation is required, these transactions are assured that all the data they read is committed. Try repeating the first tutorial with the isolation level set to 1 instead of 0. You will find that the Accountant's computation cannot proceed while the Sales Manager's transaction, which updates the price of the tee shirts, remains incomplete.

When the Accountant raised his isolation to level 2, the database server began using read locks. From then on, it acquired a read lock for his transaction on each row that matched his selection.

Transaction blocking

In the above tutorial, the Sales Manager window froze during the execution of her UPDATE command. The database server began to execute her command, then found that the Accountant's transaction had acquired a read lock on the row that the Sales Manager needed to change. At this point, the database server simply paused the execution of the UPDATE. Once the Accountant finished his transaction with the ROLLBACK, the database server automatically released his locks. Finding no further obstructions, it then proceeded to complete execution of the Sales Manager's UPDATE.

In general, a locking conflict occurs when one transaction attempts to acquire an exclusive lock on a row on which another transaction holds a lock, or attempts to acquire a shared lock on a row on which another transaction holds an exclusive lock. One transaction must wait for another transaction to complete. The transaction that must wait is said to be blocked by another transaction.

When the database server identifies a locking conflict which prohibits a transaction from proceeding immediately, it can either pause execution of the transaction, or it can terminate the transaction, roll back any changes, and return an error. You control the route by setting the BLOCKING option. When BLOCKING is ON the second transaction waits, as in the above tutorial.

For more information about the blocking option, see The BLOCKING option.