Set the isolation level to 2 in the Sales Manager and Accountant windows by executing the following statement in each:

SET TEMPORARY OPTION isolation_level = 2;

As the Accountant, execute the following statement to list all the departments:

SELECT * FROM GROUPO.Departments
ORDER BY DepartmentID;

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;

As the Sales Manager, execute the following statement to list all the departments:

SELECT * FROM GROUPO.Departments
ORDER BY DepartmentID;

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 only discovers the new row if he executes his SELECT statement again. As the Accountant, execute the SELECT statement again to see the new row appended to the table.

SELECT * FROM GROUPO.Departments
ORDER BY DepartmentID;

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, so 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. As the Accountant, execute the following statements:

SET TEMPORARY OPTION isolation_level = 3;
SELECT * FROM GROUPO.Departments
ORDER BY DepartmentID;

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 window pauses during execution because the Accountant's locks block the statement. From the toolbar, click Stop to interrupt this entry.

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 statement.

Notice that the Sales Manager's statement 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 transaction. Once placed, these locks must be respected by all other concurrent transactions.

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.