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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Transactions and isolation levels » Isolation level tutorials » Tutorial: Understanding phantom rows

 

Lesson 2: Avoiding phantom rows using snapshot isolation

You can use the snapshot isolation level to maintain consistency at the same level as isolation level at 3 without any sort of blocking. The Sales Manager's statement is not blocked and the Accountant does not see a phantom row.

Prerequisites

This lesson assumes that you have completed all preceding lessons. See Lesson 1: Creating a phantom row.

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Understanding phantom rows.

 Task
  1. As the Accountant, execute the following statements to enable snapshot isolation:

    SET OPTION PUBLIC. allow_snapshot_isolation = 'On';
    SET TEMPORARY OPTION isolation_level = 'snapshot';
  2. Execute the following statement to list all the departments:

    SELECT * FROM GROUPO.Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  3. The Sales Manager decides to set up a new department to focus on the foreign market. Philip Chin, who has EmployeeID 129, heads the new department. As the Sales Manager, execute the following statement to create a new entry for the new department, which appears as a new row at the bottom of the table in the Sales Manager's window:

    INSERT INTO GROUPO.Departments
       ( DepartmentID, DepartmentName, DepartmentHeadID )
       VALUES( 600, 'Foreign Sales', 129 );
    COMMIT;
  4. As the Sales Manager, execute the following statement to list all the departments:

    SELECT * FROM GROUPO.Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
    600 Foreign Sales 129
  5. The Accountant can execute his query again and does not see the new row because the transaction has not been committed.

    SELECT * FROM GROUPO.Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  6. The Sales Manager would like to add a second department to handle a sales initiative aimed at large corporate partners. As the Sales Manager, execute the following statement:

    INSERT INTO GROUPO.Departments
     ( DepartmentID, DepartmentName, DepartmentHeadID )
       VALUES( 700, 'Major Account Sales', 902 );

    The Sales Manager's change is not blocked because the Accountant is using snapshot isolation.

  7. The Accountant must end his snapshot transaction to see the changes that the Sales Manager committed to the database.

    COMMIT;
       SELECT * FROM GROUPO.Departments
       ORDER BY DepartmentID;

    Now the Accountant sees the Foreign Sales department, but not the Major Account Sales department.

    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
    600 Foreign Sales 129
  8. To avoid changing the SQL Anywhere sample database, you should roll back the incomplete transaction that inserts the Major Account Sales department row and use a second transaction to delete the Foreign Sales department.

    1. As the Sales Manager, execute the following statement to roll back the current transaction, delete the row inserted earlier, and commit this operation:

      ROLLBACK;
      DELETE FROM GROUPO.Departments
      WHERE DepartmentID = 600;
      COMMIT;

Results

You have successfully avoided phantom rows by using snapshot isolation.