Snapshot isolation example

The following example uses two connections to the SQL Anywhere sample database to illustrate how snapshot isolation can be used to maintain consistency without blocking.

To use snapshot isolation

  1. Execute the following command to create an Interactive SQL connection (Connection1), to the SQL Anywhere sample database:

    dbisql -c "DSN=SQL Anywhere 11 Demo;UID=DBA;PWD=sql;ConnectionName=Connection1"

  2. Execute the following command to create an Interactive SQL connection (Connection2) to the SQL Anywhere sample database:

    dbisql -c "DSN=SQL Anywhere 11 Demo;UID=DBA;PWD=sql;ConnectionName=Connection2"

  3. In Connection1, execute the following command to set the isolation level to 1 (read committed), which acquires and holds a read lock on the current row. 

    SET OPTION isolation_level = 1;

  4. In Connection1, execute the following command:

    SELECT * FROM Products;
    ID Name Description Size Color Quantity ...
    300 Tee Shirt Tank Top Small White 28 ...
    301 Tee Shirt V-neck Medium Orange 54 ...
    302 Tee Shirt Crew Neck One size fits all Black 75 ...
    400 Baseball Cap Cotton Cap One size fits all Black 112 ...
    ... ... ... ... ... ... ...

  5. In Connection2, execute the following command:

    UPDATE Products
SET Name = 'New Tee Shirt'
WHERE ID = 302;

  6. In Connection1, execute the SELECT statement again:

    SELECT * FROM Products;

    The SELECT statement is blocked and cannot proceed because the UPDATE statement in Connection2 has not been committed or rolled back. The SELECT statement must wait until the transaction in Connection2 is complete before it can proceed. This ensures that the SELECT statement does not read uncommitted data into its result.

  7. In Connection2, execute the following command:

    ROLLBACK;

    The transaction in Connection2 completes, and the SELECT statement in Connection1 proceeds.

  8. Using the statement snapshot isolation level achieves the same concurrency as isolation level 1, but without blocking.

    In Connection1, execute the following command to allow snapshot isolation:

    SET OPTION PUBLIC.allow_snapshot_isolation = 'On';

  9. In Connection 1, execute the following command to change the isolation level to statement snapshot:

    SET TEMPORARY OPTION isolation_level = 'statement-snapshot';

  10. In Connection1, execute the following statement:

    SELECT * FROM Products;

  11. In Connection2, execute the following statement:

    UPDATE Products
SET Name = 'New Tee Shirt'
WHERE ID = 302;

  12. In Connection1, issue the SELECT statement again:

    SELECT * FROM Products;

    The SELECT statement executes without being blocked, but does not include the data from the UPDATE statement executed by Connection2.

  13. In Connection2, finish the transaction by executing the following command:

    COMMIT;

  14. In Connection1, finish the transaction (the query against the Products table), and then execute the SELECT statement again to view the updated data: 

    COMMIT;
SELECT * FROM Products;
    ID Name Description Size Color Quantity ...
    300 Tee Shirt Tank Top Small White 28 ...
    301 Tee Shirt V-neck Medium Orange 54 ...
    302 New Tee Shirt Crew Neck One size fits all Black 75 ...
    400 Baseball Cap Cotton Cap One size fits all Black 112 ...
    ... ... ... ... ... ... ...

  15. Undo the changes to the SQL Anywhere sample database by executing the following statement:

    UPDATE Products
SET Name = 'Tee Shirt'
WHERE id = 302;
COMMIT;

For more examples about using snapshot isolation, see:

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1