Practical locking implications tutorial

ASA SQL User's Guide
Using Transactions and Isolation Levels
Isolation level tutorials

Practical locking implications tutorial

The following continues the same scenario. In this tutorial, the Accountant and the Sales Manager both have tasks that involve the sales order and sales order items 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 placed on an indexed scan position to prevent phantom rows. When a transaction at isolation level 3 selects rows which match a given criterion, the database server places anti-insert locks to stop other transactions from inserting rows which would also match. The number of locks placed on your behalf depends both on the search criterion and on the design of your database.

If you have not done so, do steps 1 through 3 of the previous tutorial which describe how to start two instances of Interactive SQL.

Set the isolation level to 2 in both the Sales Manager window and the Accountant window by executing the following command.
```
SET TEMPORARY OPTION ISOLATION_LEVEL = 2
```

Each month, the sales representatives are paid a commission, which 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 command 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 emp_id, emp_fname, emp_lname,
   SUM(sales_order_items.quantity * unit_price)
      AS "April sales"
FROM employee
   KEY JOIN sales_order
   KEY JOIN sales_order_items
   KEY JOIN product
WHERE '2001-04-01' <= order_date
   AND order_date < '2001-05-01'
GROUP BY  emp_id, emp_fname, emp_lname

emp_id	emp_fname	emp_lname	April sales
129	Philip	Chin	2160.00
195	Marc	Dill	2568.00
299	Rollin	Overbey	5760.00
467	James	Klobucher	3228.00
...	...	...	...

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 commands. 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 sales_order
VALUES ( 2653, 174, '2001-04-22', 'r1',
      'Central', 129);
INSERT into sales_order_items
VALUES ( 2653, 1, 601, 100, '2001-04-25' );
COMMIT;
```

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 command, and observe that Philip Chin's April sales changes to $4560.00.

emp_id	emp_fname	emp_lname	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!

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

First, roll back the insertion of Philip's missing order: Execute the following statement in the Sales Manager's window.
```
ROLLBACK
```
In the Accountant's window, execute the following two statements.
```
ROLLBACK;
SET TEMPORARY OPTION ISOLATION_LEVEL = 3;
```

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

DELETE
FROM sales_order_items
WHERE id = 2653;
DELETE
FROM sales_order
WHERE id = 2653;
COMMIT;

In the Accountant's window, execute same query as before.

SELECT emp_id, emp_fname, emp_lname,
   SUM(sales_order_items.quantity * unit_price)
      AS "April sales"
FROM employee
   KEY JOIN sales_order
   KEY JOIN sales_order_items
   KEY JOIN product
WHERE '2001-04-01' <= order_date
   AND order_date < '2001-05-01'
GROUP BY  emp_id, emp_fname, emp_lname

Because you set the isolation to level 3, the database server will automatically place anti-insert locks to ensure that the Sales Manager can't insert April order items until the Accountant finishes his transaction.

Return to the Sales Manager's window. Again attempt to enter Philip Chin's missing order.
```
INSERT INTO sales_order
VALUES ( 2653, 174, '2001-04-22',
         'r1','Central', 129)
```
The Sales Manager's window will hang; the operation will not complete. Click the Interrupt the SQL Statement button on the toolbar (or choose Stop from the SQL menu) to interrupt this entry.
The Sales Manager can't enter the order in April, but you might think that she could still enter it in May.

Change the date of the command to May 05 and try again.
```
INSERT INTO sales_order
VALUES ( 2653, 174, '2001-05-05', 'r1',
      'Central', 129)
```
The Sales Manager's window will hang again. Click the Interrupt the SQL Statement button on the toolbar (or choose Stop from the SQL menu) 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 a large number of other transactions.

The database server places locks in table indices. 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.
The Sales Manager wishes to add a second item to order 2651. Use the following command.
```
INSERT INTO sales_order_items
VALUES ( 2651, 2, 302, 4, '2001-05-22' )
```
All goes well, so the Sales Manager decides to add the following item to order 2652 as well.
```
INSERT INTO sales_order_items
VALUES ( 2652, 2, 600, 12, '2001-05-25' )
```
The Sales Manager's window will hang. Click the Interrupt the SQL Statement button on the toolbar (or choose Stop from the SQL menu) to interrupt this entry.
Conclude this tutorial by undoing any changes to avoid changing the demonstration database. Enter the following command in the Sales Manager's window.
```
ROLLBACK
```
Enter the same command in the Accountant's window.
```
ROLLBACK
```

You may now close both windows.