Phantom row tutorial

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

Phantom row tutorial

The following tutorial continues the same scenario. In this case, the Accountant views the department 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, Non-repeatable read tutorial, so that you have two instances of Interactive SQL.

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

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

SELECT * FROM department
ORDER BY dept_id;

dept_id	dept_name	dept_head_id
100	R & D	501
200	Sales	902
300	Finance	1293
400	Marketing	1576
...	...	...

The Sales Manager decides to set up a new department to focus on the foreign market. Philip Chin, who has emp_id 129, will head the new department.
```
INSERT INTO department
   (dept_id, dept_name, dept_head_id)
   VALUES(600, 'Foreign Sales', 129);
```
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.

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 department
ORDER BY dept_id;

dept_id	dept_name	dept_head_id
100	R & D	501
200	Sales	902
300	Finance	1293
500	Shipping	703
...	...	...

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.

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 department
ORDER BY dept_id
```
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 department
   (dept_id, dept_name, dept_head_id)
VALUES(700, 'Major Account Sales', 902)
```
The Sales Manager's window will pause during execution because the Accountant's locks block the command. Click the Interrupt the SQL Statement button on the toolbar (or choose Stop from the SQL menu) to interrupt this entry.
To avoid changing the demonstration database that comes with Adaptive Server Anywhere, you should roll back the insertion of the new departments. Execute the following command in the Sales Manager's window:
```
ROLLBACK
```

When the Accountant raised his isolation to level 3 and again selected all rows in the department table, the database server placed anti-insert locks on each row in the table, and one extra phantom lock to avoid insertion 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 the Sales Manager 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 on locking, see How locking works.