Tutorial: Non-repeatable reads

The example in Tutorial: Dirty reads 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 assume the role of the same two employees, both using the SQL Anywhere sample database concurrently. The Sales Manager wants to offer a new sales price on plastic visors. The Accountant wants 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 SQL Anywhere sample database supplied with SQL Anywhere. By setting the isolation level to 1, you eliminate the type of inconsistency which the previous tutorial demonstrated, namely the dirty read.

Note

For this tutorial to work properly, the Automatically Release Database Locks option must not be selected in Interactive SQL (Tools » Options » SQL Anywhere).

Start Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:
1. From the Action dropdown list, choose Connect With An ODBC Data Source.
2. In the ODBC Data Source Name field, choose SQL Anywhere 12 Demo.
3. If necessary, click Advanced, to reveal the Advanced Options tab.
4. Click the Advanced Options tab and type Sales Manager in the ConnectionName field.
5. Click Connect.
Start a second instance of Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Accountant:
1. From the Action dropdown list, choose Connect With An ODBC Data Source.
2. In the ODBC Data Source Name field, choose SQL Anywhere 12 Demo.
3. If necessary, click Advanced, to reveal the Advanced Options tab.
4. Click the Advanced Options tab and type Accountant in the ConnectionName field.
5. Click 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, UnitPrice FROM Products;

ID	Name	UnitPrice
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
401	Baseball Cap	10.00
500	Visor	7.00
501	Visor	7.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, UnitPrice FROM Products
WHERE Name = 'Visor';
UPDATE Products
SET UnitPrice = 5.95 WHERE ID = 501;
COMMIT;
SELECT ID, Name, UnitPrice FROM Products
WHERE Name = 'Visor';

ID	Name	UnitPrice
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 executes the SELECT statement again, and sees the Sales Manager's new sale price.

SELECT ID, Name, UnitPrice
FROM Products;

ID	Name	UnitPrice
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
401	Baseball Cap	10.00
500	Visor	7.00
501	Visor	5.95
...	...	...

This inconsistency is called a non-repeatable read because when the Accountant executes the same SELECT a second time in the same transaction, and did not get the same results.

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. Non-repeatable reads are eliminated at isolation level 2. As the Accountant, execute the following statements:

SET TEMPORARY OPTION isolation_level = 2;
SELECT ID, Name, UnitPrice
FROM Products;

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 starts to execute, and then her window appears to freeze.
UPDATE Products SET UnitPrice = 7.00 WHERE ID = 501;
The database server must guarantee repeatable reads at isolation level 2. Because the Accountant is using isolation level 2, the database server places a read lock on each row of the Products 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 Products 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;

When the database server executes this statement, the Sales Manager's transaction completes.

ID	Name	UnitPrice
500	Visor	7.00
501	Visor	7.00

The Sales Manager can finish now. She wants 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 does not 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 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's 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 the blocking is set to On the second transaction waits, as in the above tutorial.

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

Using snapshot isolation to avoid non-repeatable reads

You can also use snapshot isolation to help avoid blocking. Because transactions that use snapshot isolation only see committed data, the Accountant's transaction does not block the Sales Manager's transaction.

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

Execute the following statements to enable snapshot isolation for the database and specify that the snapshot isolation level is used:

SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
SET TEMPORARY OPTION isolation_level = 'snapshot';

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

SELECT ID, Name, UnitPrice
FROM Products
ORDER BY ID;

ID	Name	UnitPrice
300	Tee Shirt	9.00
301	Tee Shirt	14.00
302	Tee Shirt	14.00
400	Baseball Cap	9.00
401	Baseball Cap	10.00
500	Visor	7.00
501	Visor	7.00
...	...	...

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

UPDATE Products
SET UnitPrice = 5.95 WHERE ID = 501;
COMMIT;
SELECT ID, Name, UnitPrice FROM Products
WHERE Name = 'Visor';

The Accountant executes his query again, and does not see the change in price because the data that was committed at the time of the first read is used for the transaction.
SELECT ID, Name, UnitPrice FROM Products;

As the Sales Manager, change the plastic visor back to its original price.

UPDATE Products
SET UnitPrice = 7.00
WHERE ID = 501;
COMMIT;

The database server does not place a read lock on the rows in the Products table that the Accountant is reading because the Accountant is viewing a snapshot of committed data that was taken before the Sales Manager made any changes to the Products table.

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;

Discuss this page in DocCommentXchange.