Phenomena that might occur with isolation levels other than repeatable read

Phantom rows

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), 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.

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

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

The following actions occur:

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

   FIRSTNME	LASTNAME
   CHRISTINE	HAAS

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

3. 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')
   …

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

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

Dirty read

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:

1. Transaction T1 updates the last name of employee O'Connell to Connelly:

   UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';

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

3. 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'.

Non-repeatable read

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:

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

2. 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;

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