Phenomena that might occur with various isolation levels

With certain isolation levels, an application that is reading data might retrieve more or fewer rows than are expected.

Phantom rows that can occur with any isolation level

SQL transaction T1 reads a set of rows that satisfy some search condition on a table. SQL transaction T2 then executes SQL statements that generate one or more new rows in the table that also satisfy the search condition that is used by SQL transaction T1. If T1 then repeats the original read with the same search condition, T1 receives a different set of rows.

This phenomenon can occur with uncommitted read (UR), cursor stability (CS), read stability (RS), or repeatable read (RR) isolation.

One case in which phantom rows occur is when there is a multiple-column index on a table, and the following activities occur concurrently:

One transaction executes a query that selects rows based on the first and third indexed columns of a table. The query uses an index scan.
Another transaction updates a value in the second indexed column of the table. The corresponding row has not yet been selected by the query. The update causes the index key value for the updated row to move to a point that the index scan has already passed.

Suppose that table EMP_INFO is defined as follows:

CREATE TABLE EMP_INFO (
 WORKDEPT CHAR(3) NOT NULL, 
 STATE CHAR(2),
 JOB CHAR(8), 
 LASTNAME VARCHAR(15), 
 FIRSTNME VARCHAR(12));

Index EMP_INFO_IX is defined on the first three columns of table EMP_INFO:

CREATE INDEX EMP_INFO_IX ON EMP_INFO(WORKDEPT, STATE, JOB);

Table EMP_INFO contains rows like these. The rows are displayed in index order. This is the order in which the rows are read with an index scan.

WORKDEPT	STATE	JOB	LASTNAME	FIRSTNME
A00	CA	PRES	HAAS	CHRISTINE
A00	NY	SALESREP	HEMMINGER	DIAN
A00	OH	SALESREP	LUCCHESI	VINCENZO
A00	PA	SALESREP	O'CONNELL	SEAN

Transaction T1 executes the following query to retrieve a list of employees who are in department A00 and work as sales representatives:

SELECT FIRSTNME, LASTNAME FROM EMP_INFO WHERE WORKDEPT = 'A00' AND JOB='SALESREP';

The packages that execute transaction T1 are bound with isolation repeatable read (RR). The query uses index EMP_INFO_IX to retrieve the rows.

The following actions occur:

The select operation reads the index's second key value, ('A00', 'NY', 'SALESREP'), and retrieves data from the second row of the table. The select operation also holds a lock with RR isolation on this table row:

WORKDEPT STATE JOB FIRSTNME LASTNAME

A00 NY SALESREP DIAN HEMMINGER
At the same time, transaction T2 updates the STATE column value to AK for the row with a LASTNAME column value of O'CONNELL:
```
UPDATE EMP_INFO SET STATE ='AK' WHERE LASTNAME='O''CONNELL';
```
The update operation changes the order of the keys in index EMP_INFO_IX to this order:

WORKDEPT STATE JOB

A00 AK SALESREP

A00 CA PRES

A00 NY SALESREP

A00 OH SALESREP
The select operation continues to retrieve rows using the updated index order:

WORKDEPT STATE JOB

A00 OH SALESREP

The row with a LASTNAME column value of O'CONNELL is a phantom row. The result set does not contain that row because the index scan has already passed the new position of the row's index key value.
If the query is executed again, with no updates that affect the order of the index key values, all rows are returned.

WORKDEPT	STATE	JOB	FIRSTNME	LASTNAME
A00	NY	SALESREP	DIAN	HEMMINGER

WORKDEPT	STATE	JOB
A00	AK	SALESREP
A00	CA	PRES
A00	NY	SALESREP
A00	OH	SALESREP

WORKDEPT	STATE	JOB
A00	OH	SALESREP

In the previous example, the update operation moved the index key to a position before the first row that was locked by the select operation with RR isolation. As a result, the update operation was able to run successfully while the select operation was still being executed.

To avoid the phantom rows problem, the select operation needs to use an index that is not affected by the update operation.

Phantom rows that can occur with uncommitted read, cursor stability, or read stability isolation

This phenomenon can occur with uncommitted read (UR), cursor stability (CS), or read stability (RS) isolation.

One case in which phantom rows occur is when there is a multiple-column index on a table, and the following activities occur concurrently:

One transaction executes a query that selects rows based on the first indexed column of a table. The query uses an index scan.
Another transaction updates a value in the second indexed column of the table. The corresponding row has not yet been selected by the query. The update causes the index key value for the updated row to move to a point that the index scan has already passed.

In this situation, the query misses the updated row.

Example: Suppose that table EMP_INFO is defined as follows:

CREATE TABLE EMP_INFO (
 WORKDEPT CHAR(3) NOT NULL, 
 LASTNAME VARCHAR(15), 
 FIRSTNME VARCHAR(12), 
 JOB CHAR(8));

Index EMP_INFO_IX is defined on the first two columns of table EMP_INFO:

CREATE INDEX EMP_INFO_IX ON EMP_INFO(WORKDEPT, LASTNAME);

Table EMP_INFO contains rows like these. The rows are displayed in index order. This is the order in which the rows are read with an index scan.

WORKDEPT	LASTNAME	FIRSTNME	JOB
A00	HAAS	CHRISTINE	PRES
A00	HEMMINGER	DIAN	SALESREP
A00	LUCCHESI	VINCENZO	SALESREP
A00	O'CONNELL	SEAN	CLERK
A00	ORLANDO	GREG	CLERK
B01	THOMPSON	MICHAEL	MANAGER
C01	KWAN	SALLY	MANAGER
C01	NATZ	KIM	ANALYST
C01	NICHOLLS	HEATHER	ANALYST
C01	QUINTANA	DOLORES	ANALYST
…

The packages that execute transactions T1 and T2 are bound with RS isolation.

Transaction T1 retrieves a list of employees who are in department A00 by executing the following query:

SELECT FIRSTNME, LASTNAME FROM EMP_INFO WHERE WORKDEPT = 'A00';

The query uses index EMP_INFO_IX to retrieve the rows.

The following actions occur:

The select operation reads the index first key value, ('A00','HAAS'), and retrieves the first row:

FIRSTNME LASTNAME

CHRISTINE HAAS
At the same time, transaction T2 updates the last name of employee O'Connell to Connelly:
```
UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';
```
The update operation also uses index EMP_INFO_IX.

FIRSTNME	LASTNAME
CHRISTINE	HAAS

The update operation changes the order of the keys in index EMP_INFO_IX to this order:

('A00','CONNELLY')
('A00','HAAS')
('A00','HEMMINGER')
('A00','LUCCHESI')
('A00','ORLANDO')
…

The select operation continues to retrieve rows using the updated index order:

FIRSTNME LASTNAME

DIAN HEMMINGER

VINCENZO LUCCHESI

GREG ORLANDO

The row for Sean Connelly is a phantom row. The result set does not contain that row because the index scan has already passed the new position of the row's index key value.
If the query is executed again, with no updates that affect the order of the index key values, all rows are returned:

FIRSTNME LASTNAME

SEAN CONNELLY

CHRISTINE HAAS

DIAN HEMMINGER

VINCENZO LUCCHESI

GREG ORLANDO

FIRSTNME	LASTNAME
DIAN	HEMMINGER
VINCENZO	LUCCHESI
GREG	ORLANDO

FIRSTNME	LASTNAME
SEAN	CONNELLY
CHRISTINE	HAAS
DIAN	HEMMINGER
VINCENZO	LUCCHESI
GREG	ORLANDO

Dirty read with uncommitted read isolation

SQL transaction T1 modifies a row. SQL transaction T2 reads that row before T1 executes a commit operation. If T1 then executes a rollback operation, T2 will have read a row that was never committed, and therefore can be considered never to have existed.

This phenomenon can occur with uncommitted read (UR) isolation.

Example: Suppose that table EMP_INFO is defined as in the previous example, and contain the same data.

The packages that execute transactions T1 and T2 are bound with UR isolation.

The following actions occur:

Transaction T1 updates the last name of employee O'Connell to Connelly:
```
UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';
```
Transaction T2 executes the following query:
```
SELECT FIRSTNME, LASTNAME FROM EMP_INFO WHERE WORKDEPT = 'A00';
```
The following rows are returned:

FIRSTNME LASTNAME

SEAN CONNELLY

CHRISTINE HAAS

DIAN HEMMINGER

VINCENZO LUCCHESI

GREG ORLANDO
Transaction T1 executes a rollback operation, which reverts this update statement:
```
UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';
```
The result set in the previous step is no longer valid, because there is no longer a row with a LASTNAME value of 'CONNELLY'.

FIRSTNME	LASTNAME
SEAN	CONNELLY
CHRISTINE	HAAS
DIAN	HEMMINGER
VINCENZO	LUCCHESI
GREG	ORLANDO

Rows cannot be reread with uncommitted read or cursor stability isolation

SQL transaction T1 reads a row. SQL transaction T2 then modifies or deletes that row and executes a commit operation. If T1 then attempts to reread that row, T1 might receive the modified value or discover that the row has been deleted.

This phenomenon can occur with uncommitted read (UR) or cursor stability (CS) isolation.

Example: Suppose that table EMP_INFO is defined as in the first example, and contain the same data.

The packages that execute transactions T1 and T2 are bound with CS isolation.

The following actions occur:

Transaction T1 executes the following query:
```
SELECT FIRSTNME, LASTNAME, JOB FROM EMP_INFO WHERE LASTNAME = 'HAAS';
```
The following row is returned:

FIRSTNME LASTNAME JOB

CHRISTINE HAAS PRES
Transaction T2 updates the Christine Haas' job from PRES to CEO, and commits the update:
```
UPDATE EMP_INFO SET JOB='CEO' WHERE LASTNAME='HAAS' AND FIRSTNME='CHRISTINE';
COMMIT;
```
Transaction T1 executes the following query again:
```
SELECT FIRSTNME, LASTNAME, JOB FROM EMP_INFO WHERE LASTNAME = 'HAAS';
```
The following row is returned, which is different from the row that was previously returned by the same query:

FIRSTNME LASTNAME JOB

CHRISTINE HAAS CEO