Lesson 1: Creating non-repeatable reads

Create a non-repeatable read

Start Interactive SQL.
In the Connect window, connect to the SQL Anywhere sample database as the Sales Manager:
1. In the Action dropdown list, click Connect With An ODBC Data Source.
2. Click ODBC Data Source Name, and then type SQL Anywhere 12 Demo in the field below.
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. In the Action dropdown list, click Connect With An ODBC Data Source.
2. Click ODBC Data Source Name, and then type SQL Anywhere 12 Demo in the field below.
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 statement.
SET TEMPORARY OPTION isolation_level = 1;
Set the isolation level to 1 in the Sales Manager's window by executing the following statement:
SET TEMPORARY OPTION isolation_level = 1;

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

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 statements:

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 statement 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 statement 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 statement. The database server began to execute her statement, 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.

Lesson 1: Creating non-repeatable reads

Create a non-repeatable read

Types of locks and different isolation levels

Transaction blocking

See also