Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 
Downloads > Code Samples & Utilities > Snapshot Isolation Sample Code  
RSS Feed
 
 
 

Snapshot Isolation Sample

This sample demonstrates how snapshot isolation eliminates blocking and facilitates concurrency by comparing it with other isolation levels.

For more information about snapshot isolation, refer to the online documentation: SQL Anywhere User's Guide | Using Transactions and Isolation Levels | Isolation levels and consistency | Snapshot isolation.

Requirements

  • SQL Anywhere 10.0

You can run this sample under Windows or Linux.

Walkthrough

Comparing SNAPSHOT and ISOLATION LEVEL 3

  1. Shut down any SQL Anywhere databases that are running on your computer.
     
  2. Start Interactive SQL by executing the following command at a Command Prompt:
     
    dbisql
     
  3. Connect to the SQL Anywhere 10.0 Demo database by choosing the SQL Anywhere 10 Demo ODBC data source.
     

     
  4. On the Advanced tab, type DBISQL1 in the Connection Name field.
     

     
  5. Click OK to start and connect to the database.
     
  6. Repeat Steps 2-5 to start a second instance of Interactive SQL, this time naming the connection DBISQL2.
     
  7. In DBISQL1, execute the following command (by pressing F5) to allow snapshot isolation:
     
    SET OPTION public.allow_snapshot_isolation = 'ON';
     
  8. In DBISQL1, execute the following command to set the isolation level to 3, the highest level possible (serializable). This prevents dirty reads, non-repeated reads and phantom rows.
     
    SET OPTION isolation_level = 3;
     
  9. In DBISQL1, execute the following command:
     
    SELECT * FROM products WHERE ID >= 200 AND ID <= 500;
     
  10. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    401

    Baseball Cap

    Wool cap

    One size fits all

    White

    12

    500

    Visor

    Cloth visor

    One size fits all

    White

    36

  11. In DBISQL2, execute the following command:
     
    INSERT INTO products VALUES(450, 'Jacket', 'Nice Jacket', 'Large', 'Green', 50, 100.0, NULL);
     
  12. Notice that this execution is blocked because the stop execution button is not disables, meaning that the SQL statement is currently running.
     

     
    At isolation level 3, the database prevents such an action because the insert in DBISQL2 will cause a different result set from the same SELECT statement in DBISQL1. DBISQL1 has produced a range lock on all the possible rows between ID >= 200 and ID <= 500, preventing the inconsistency that a phantom row will be inserted. DBISQL1 must commit before the INSERT statement in DBISQL2 can proceed.
     
  13. In DBISQL1, execute the following command:
     
    COMMIT;
     
    The statement in DBISQL2 will now execute because the transaction in DBISQL1 has finished.
     
  14. In DBISQL2, execute the following command:
     
    ROLLBACK;
     
    This will undo the changes.
     
    Setting the isolation level at 3 hinders concurrency, as all the values selected within a range are locked. For example, a SELECT statement with the WHERE clause for ID between 200 and 500 locks all the Ids within this range from other users for INSERT, UPDATE and DELETE. This was demonstrated as the INSERT statement was blocked and could not proceed. The advantage of SNAPSHOT isolation is that it has the characteristics of isolation level 3, without the hindrance in concurrency.
     
  15. Now you will use snapshot isolation to see the difference in behaviour.
     
    In DBISQL1, reset the isolation level to SNAPSHOT. Execute the following command:
     
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
     
  16. Repeat Steps 9-10 and notice the difference. DBISQL2 is not blocked, and the INSERT statement executes successfully without a COMMIT and ROLLBACK.
     
  17. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    401

    Baseball Cap

    Wool cap

    One size fits all

    White

    12

    500

    Visor

    Cloth visor

    One size fits all

    White

    36

  18. In DBISQL1, execute the following statement:
     
    SELECT * FROM products WHERE ID >= 200 AND ID <= 500;
     
  19. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    401

    Baseball Cap

    Wool cap

    One size fits all

    White

    12

    500

    Visor

    Cloth visor

    One size fits all

    White

    36

  20. Notice that the results are the same as in Step 15. SNAPSHOT has maintained a copy of the original result, therefore enforcing data consistency within a transaction without the hindrance in concurrency as in isolation level 3.
     
  21. In DBISQL2, execute the following command:
     
    COMMIT;
     
    The INSERT statement in DBISQL2 has now been completed.
     
  22. To verify that the row has been inserted, execute the following command in DBISQL2:
     
    SELECT * FROM products WHERE ID >= 200 AND ID <= 500;
     
  23. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    401

    Baseball Cap

    Wool cap

    One size fits all

    White

    12

    450

    Jacket

    Nice Jacket

    Large

    Green

    50

    500

    Visor

    Cloth visor

    One size fits all

    White

    36

  24. You will see the changes, with row ID 450 added.
     
  25. In DBISQL1, execute the same statement:
     
    SELECT * FROM products WHERE ID >= 200 AND ID <= 500;
     
  26. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    401

    Baseball Cap

    Wool cap

    One size fits all

    White

    12

    500

    Visor

    Cloth visor

    One size fits all

    White

    36


  27. You will not see the changes, and therefore row ID 450 is not displayed.
     
  28. In SNAPSHOT isolation, the dataset requires that a COMMIT or ROLLBACK be issued within a transaction before changes can be seen. In DBISQL1, execute the following command:
     
    COMMIT;
     
  29. Now run the SELECT statement again in DBISQL1 and notice the changes:
     
    SELECT * FROM products WHERE ID >= 200 AND ID <= 500;
     
  30. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    401

    Baseball Cap

    Wool cap

    One size fits all

    White

    12

    450

    Jacket

    Nice Jacket

    Large

    Green

    50

    500

    Visor

    Cloth visor

    One size fits all

    White

    36

  31. This time you will see the changes because the previous transaction has finished.
     
  32. Close both instances of Interactive SQL.
  33. Setting the isolation level to SNAPSHOT maintains consistency at the same level as to setting the isolation level at 3, without any sort of blocking.

    Comparing STATEMENT and ISOLATION LEVEL 1

  34. Shut down any SQL Anywhere databases that are running on your computer.
     
  35. Start Interactive SQL by executing the following command at a Command Prompt:
     
    dbisql
     
  36. Connect to the SQL Anywhere 10.0 Demo database by choosing the SQL Anywhere 10 Demo ODBC data source.
     
  37. On the Advanced tab, type DBISQL1 in the Connection Name field.
     
  38. Click OK to start and connect to the database.
     
  39. Repeat Steps 2-5 to start a second instance of Interactive SQL, this time naming the connection DBISQL2.
     
  40. In DBISQL1, execute the following command to allow snapshot isolation:
     
    SET OPTION public.allow_snapshot_isolation = 'ON';
     
  41. In DBISQL1, 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;
     
  42. In DBISQL1, execute the following command:
     
    SELECT * FROM products;
     
  43. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    601

    Sweatshirt

    Zipped Sweatshirt

    Large

    Blue

    32

    700

    Shorts

    Cotton Shorts

    Medium

    Black

    80

  44. In DBISQL2, execute the following command:
     
    UPDATE products SET Name = 'New Sweatshirt' WHERE id = 601;
     
  45. In DBISQL1, execute the same SELECT statement:
     
    SELECT * FROM products;
     
    Notice the SELECT statement is blocked and cannot proceed; this is because the UPDATE statement in DBISQL2 has not been committed or rolled back, therefore the transaction in DBISQL2 is not complete. The SELECT statement must wait until the transaction in DBISQL2 is complete before it can proceed to ensure the SELECT statement does not read uncommitted data into its result.
     
  46. In DBISQL2, execute the following command:
     
    ROLLBACK;
     
    The UPDATE statement in DBISQL2 has now been completed and the SELECT statement would also be able to proceed.
     
  47. Setting isolation level 1 still introduces the same blocking, but setting the isolation level to STATEMENT SNAPSHOT would achieve the effect of isolation 1 without blocking.
     
    Now, in DBISQL1, reset the isolation level to STATEMENT SNAPSHOT by executing the following command:
     
    SET TRANSACTION ISOLATION LEVEL STATEMENT SNAPSHOT;
  48. In DBISQL1, execute the following statement:
     
    SELECT * FROM products;
     
  49. In DBISQL2, execute the following statement:
     
    UPDATE products SET Name = 'New Sweatshirt' WHERE id = 601;
     
  50. In DBISQL1, issue the SELECT statement again:
     
    SELECT * FROM products;
     
    Notice that this time the SELECT statement executed without any blocking, but the update operation was not reflected. This is the advantage of STATEMENT SNAPSHOT.
     
  51. In DBISQL2, finish the transaction by executing the following command:
     
    COMMIT;
     
  52. In DBISQL1, look at the data again:
     
    SELECT * FROM products;
     
  53. ID

    Name

    Description

    Size

    Color

    Quantity

    ....

    ......

    ......

    ......

    ......

    ......

    601

    New Sweatshirt

    Zipped Sweatshirt

    Large

    Blue

    32

    700

    Shorts

    Cotton Shorts

    Medium

    Black

    80

  54. Notice that changes that occurred and that the name has changed from Sweatshirt to New Sweatshirt even though the transaction in DBISQL1 has not been committed or rolled back. This is different from setting the isolation level to SNAPSHOT, which requires the transaction to be finished before seeing new committed data. In STATEMENT SNAPSHOT, it sees the most recently committed data regardless of whether its own transaction is committed/rolled back or not.
     
  55. Close both instances of Interactive SQL.

In Setting the isolation level to STATEMENT SNAPSHOT, anomalies such as non-repeatable read and phantom rows can still occur, but it only reads committed data, so it prevents dirty reads. STATEMENT SNAPSHOT is similar to isolation 1 without the blocking and locks in concurrency.


 

SQL Anywhere

The industry-leading mobile and embedded database that provides data management and synchronization technologies for applications outside the data center.

 
 
 

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics