Tutorial: Phantom rows

The following tutorial continues the same scenario. In this case, the Accountant views the Departments table while the Sales Manager creates a new department. You will observe the appearance of a phantom row.

If you have not done so, do steps 1 through 4 of the previous tutorial, Tutorial: Non-repeatable reads, so that you have two instances of Interactive SQL.

  1. Set the isolation level to 2 in the Sales Manager window by executing the following command. 
    SET TEMPORARY OPTION isolation_level = 2;
  2. Set the isolation level to 2 for the Accountant window by executing the following command. 
    SET TEMPORARY OPTION isolation_level = 2;
  3. In the Accountant window, enter the following command to list all the departments. 
    SELECT * FROM Departments
ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  4. 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. 
    INSERT INTO Departments
   (DepartmentID, DepartmentName, DepartmentHeadID)
   VALUES(600, 'Foreign Sales', 129);
    		 
    COMMIT;

    The final command creates the new entry for the new department. It appears as a new row at the bottom of the table in the Sales Manager's window.

    In the Sales Manager window, enter the following command to list all the departments.

    SELECT *
FROM 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, however, is not aware of the new department. At isolation level 2, the database server places locks to ensure that no row changes, but places no locks that stop other transactions from inserting new rows.

    The Accountant will only discover the new row if he executes his SELECT command again. In the Accountant's window, execute the SELECT statement again. You will see the new row appended to the table. 

    SELECT *
FROM 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

    The new row that appears is called a phantom row because, from the Accountant's point of view, it appears like an apparition, seemingly from nowhere. The Accountant is connected at isolation level 2. At that level, the database server acquires locks only on the rows that he is using. Other rows are left untouched and hence there is nothing to prevent the Sales Manager from inserting a new row.

  6. The Accountant would prefer to avoid such surprises in future, so he raises the isolation level of his current transaction to level 3. Enter the following commands for the Accountant. 
    SET TEMPORARY OPTION isolation_level = 3;
SELECT *
FROM Departments
ORDER BY DepartmentID;
  7. The Sales Manager would like to add a second department to handle a sales initiative aimed at large corporate partners. Execute the following command in the Sales Manager's window. 
    INSERT INTO Departments
 (DepartmentID, DepartmentName, DepartmentHeadID)
   VALUES(700, 'Major Account Sales', 902);

    The Sales Manager's window pauses during execution because the Accountant's locks block the command. From the toolbar, click Interrupt The SQL Statement (or choose SQL » Stop) to interrupt this entry.

  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. Execute the following command in the Sales Manager's window to rollback the last, incomplete transaction: 
      ROLLBACK;
    2. Also in the Sales Manager's window, execute the following two statements to delete the row that you inserted earlier and commit this operation. 
      DELETE FROM Departments
WHERE DepartmentID = 600;
      		 
      COMMIT;
Explanation

When the Accountant raised his isolation to level 3 and again selected all rows in the Departments table, the database server placed anti-insert locks on each row in the table, and added one extra phantom lock to block inserts at the end of the table. When the Sales Manager attempted to insert a new row at the end of the table, it was this final lock that blocked her command.

Notice that the Sales Manager's command was blocked even though she is still connected at isolation level 2. The database server places anti-insert locks, like read locks, as demanded by the isolation level and statements of each transactions. Once placed, these locks must be respected by all other concurrent transactions.

For more information about locking, see How locking works.

Using snapshot isolation to avoid phantom rows

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 command is not blocked, and the Accountant does not see a phantom row.

If you have not done so, follow steps 1 through 4 of the Tutorial: Phantom rows which describe how to start two instances of Interactive SQL.

  1. Enable snapshot isolation for the Accountant by executing the following command. 
    SET OPTION PUBLIC. allow_snapshot_isolation = 'On';
SET TEMPORARY OPTION isolation_level = snapshot;
  2. In the Accountant window, enter the following command to list all the departments. 
    SELECT * FROM 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. 
    INSERT INTO Departments
   (DepartmentID, DepartmentName, DepartmentHeadID)
   VALUES(600, 'Foreign Sales', 129);
COMMIT;

    The final command creates the new entry for the new department. It appears as a new row at the bottom of the table in the Sales Manager's window. 

    SELECT * FROM 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
  4. The Accountant can execute his query again, and does not see the new row because the transaction has not ended. 
    SELECT *
FROM Departments
ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  5. The Sales Manager would like to add a second department to handle sales initiative aimed at large corporate partners. Execute the following command in the Sales Manager's window. 
    INSERT INTO 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.

  6. The Accountant must end his snapshot transaction to see the changes the Sales Manager committed to the database. 
    COMMIT;
   SELECT * FROM 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
  7. 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. Execute the following command in the Sales Manager's window to rollback the last, incomplete transaction: 
      ROLLBACK;
    2. Also in the Sales Manager's window, execute the following two statements to delete the row that you inserted earlier and commit this operation. 
      DELETE FROM Departments
WHERE DepartmentID = 600;
      		 
      COMMIT;
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0