Duplicate Results from a SELECT Statement

Using read-committed isolation level can lead to duplicates in a result set. A SELECT statement selects more or fewer rows than the total number of rows in the table if some rows are added or removed and committed in the range in which the SELECT scan is occurring. This may happen when an UPDATE, INSERT or DELETE statement adds or deletes a value from an index and the SELECT scan is using this index. This can also happen when an INSERT or DELETE adds or deletes rows from the table and the SELECT operation is using an all-table scan.

Index values are ordered. An UPDATE of an index value may delete the old value and insert the new value into a different place. In other words it moves a row from one position in the index to another position. If an index scan sees the same row in both positions, it returns the row twice. This does not happen with a serial scan because table pages are unordered and rows do not need to be moved around for an UPDATE. Hence once a scan passes a row, it will not see that same row again.

The only general way to avoid this problem is for the SELECT statement to use Serializable isolation. This prevents a concurrent INSERT, DELETE or UPDATE operation. There is no reliable way to avoid this problem with INSERT or DELETE by forcing the use of an index because these operations affect all indexes. With UPDATE, this problem can be avoided by forcing the SELECT statement to use an index that is not being updated.

For more information about serializable isolation, see Concurrency Control Through Isolation and Locking in the Oracle TimesTen In-Memory Database Operations Guide.