Tutorial: Understanding phantom locks

In this tutorial the Accountant and the Sales Manager both have tasks that involve the SalesOrder and SalesOrderItems tables. The Accountant needs to verify the amounts of the commission checks paid to the sales employees for the sales they made during the month of April 2001. The Sales Manager notices that a few orders have not been added to the database and wants to add them.

Their work demonstrates phantom locking. A phantom lock is a shared lock that is placed on an indexed scan position to prevent phantom rows. When a transaction at isolation level 3 selects rows that match the specified criteria, the database server places anti-insert locks to stop other transactions from inserting rows that would also match. The number of locks placed on your behalf depends both on the search criteria and on the design of your database.

Note

For this tutorial to work properly, the Automatically Release Database Locks option must not be selected in Interactive SQL. You can check the setting of this option by clicking Tools » Options, and then clicking SQL Anywhere in the left pane.

 Create a phantom lock
  1. Start two instances of Interactive SQL. See steps 1 through 4 of the non-repeatable reads tutorial. See Tutorial: Understanding non-repeatable reads.

  2. Set the isolation level to 2 in both the Sales Manager window and the Accountant window by executing the following statement.

    SET TEMPORARY OPTION isolation_level = 2;
  3. Each month, the sales representatives are paid a commission that is calculated as a percentage of their sales for that month. The Accountant is preparing the commission checks for the month of April 2001. His first task is to calculate the total sales of each representative during this month.

    Enter the following statement in the Accountant's window. Prices, sales order information, and employee data are stored in separate tables. Join these tables using the foreign key relationships to combine the necessary pieces of information.



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM Employees
       KEY JOIN SalesOrders
       KEY JOIN SalesOrderItems
       KEY JOIN Products
    WHERE '2001-04-01' <= OrderDate
       AND OrderDate < '2001-05-01'
    GROUP BY  EmployeeID, GivenName, Surname
    ORDER BY EmployeeID;
    EmployeeID GivenName Surname April sales
    129 Philip Chin 2160.00
    195 Marc Dill 2568.00
    299 Rollin Overbey 5760.00
    467 James Klobucher 3228.00
    ... ... ... ...
  4. The Sales Manager notices that a big order sold by Philip Chin was not entered into the database. Philip likes to be paid his commission promptly, so the Sales Manager enters the missing order, which was placed on April 25.

    In the Sales Manager's window, enter the following statements. The sales order and the items are entered in separate tables because one order can contain many items. You should create the entry for the sales order before you add items to it. To maintain referential integrity, the database server allows a transaction to add items to an order only if that order already exists.

    INSERT into SalesOrders
    VALUES ( 2653, 174, '2001-04-22', 'r1',
          'Central', 129 );
    INSERT into SalesOrderItems
    VALUES ( 2653, 1, 601, 100, '2001-04-25' );
    COMMIT;
  5. The Accountant has no way of knowing that the Sales Manager has just added a new order. Had the new order been entered earlier, it would have been included in the calculation of Philip Chin's April sales.

    In the Accountant's window, calculate the April sales totals again. Use the same statement, and observe that Philip Chin's April sales changes to $4560.00.

    EmployeeID GivenName Surname April sales
    129 Philip Chin 4560.00
    195 Marc Dill 2568.00
    299 Rollin Overbey 5760.00
    467 James Klobucher 3228.00
    ... ... ... ...

    Imagine that the Accountant now marks all orders placed in April to indicate that commission has been paid. The order that the Sales Manager just entered might be found in the second search and marked as paid, even though it was not included in Philip's total April sales.

  6. At isolation level 3, the database server places anti-insert locks to ensure that no other transactions can add a row that matches the criteria of a search or select.

    In the Sales Manager's window, execute the following statements to remove the new order.

    DELETE
    FROM SalesOrderItems
    WHERE ID = 2653;
    DELETE
    FROM SalesOrders
    WHERE ID = 2653;
    COMMIT;
  7. In the Accountant's window, execute the following two statements.

    ROLLBACK;
    SET TEMPORARY OPTION isolation_level = 3;
  8. In the Accountant's window, execute same query as before.



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM Employees
       KEY JOIN SalesOrders
       KEY JOIN SalesOrderItems
       KEY JOIN Products
    WHERE '2001-04-01' <= OrderDate
       AND OrderDate < '2001-05-01'
    GROUP BY  EmployeeID, GivenName, Surname;

    Because you set the isolation to level 3, the database server automatically places anti-insert locks to ensure that the Sales Manager cannot insert April order items until the Accountant finishes their transaction.

  9. Return to the Sales Manager's window. Attempt to enter Philip Chin's missing order by executing the following statement.

    INSERT INTO SalesOrders
    VALUES ( 2653, 174, '2001-04-22',
             'r1','Central', 129 );

    The Sales Manager's window stops responding, and the operation does not complete. On the toolbar, click Stop (or click SQL » Stop) to interrupt this entry.

  10. The Sales Manager cannot enter the order in April, but you might think that they could still enter it in May.

    Change the date in the statement to May 05 and try again.

    INSERT INTO SalesOrders
    VALUES ( 2653, 174, '2001-05-05', 'r1',
          'Central', 129 );

    The Sales Manager's window stops responding again. On the toolbar, click Stop (or click SQL » Stop) to interrupt this entry. Although the database server places no more locks than necessary to prevent insertions, these locks have the potential to interfere with many transactions.

    The database server places locks in table indexes. For example, it places a phantom lock in an index so a new row cannot be inserted immediately before it. However, when no suitable index is present, it must lock every row in the table.

    In some situations, anti-insert locks may block some insertions into a table, yet allow others.

  11. Conclude this tutorial by undoing any changes to avoid changing the SQL Anywhere sample database. Execute the following statement in the Sales Manager's window.

    ROLLBACK;

    Execute the following statement in the Accountant's window.

    ROLLBACK;
  12. Shut down both instances of Interactive SQL.