Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 (Français) » SQL Anywhere Server - SQL Usage » Using transactions and isolation levels » Isolation level tutorials

 

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

  1. Start Interactive SQL.

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

  3. Start a second instance of Interactive SQL.

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

  5. Set the isolation level to 1 for the Accountant's connection by executing the following command.

    SET TEMPORARY OPTION isolation_level = 1;
  6. Set the isolation level to 1 in the Sales Manager's window by executing the following command:

    SET TEMPORARY OPTION isolation_level = 1;
  7. 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
    ... ... ...
  8. 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
  9. 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.

  10. 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;
  11. 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.

  12. 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
  13. 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
 Transaction blocking
 Using snapshot isolation to avoid non-repeatable reads