Database isolation levels and R-tree indexes

Database isolation levels affect the degree of concurrency among processes that attempt to access the same rows at the same time. There are four levels of isolation:
  • DIRTY READ
  • REPEATABLE READ
  • COMMITTED READ
  • CURSOR STABILITY

Use the SQL statement SET ISOLATION to set the isolation level for your session.

If you specify the COMMITTED READ isolation level and use an R-tree index to select rows in a table, all the rows returned by the search are guaranteed to be committed. The same query, however, might not read some of the rows that have been deleted, but not yet committed, by another concurrent transaction.

While R-tree indexes can use the COMMITTED READ isolation level as described in the previous paragraph, R-tree indexes cannot use the COMMITTED READ LAST COMMITTED isolation level feature.

This behavior differs slightly from that of HCL Informix® B-tree indexes. Informix Guide to SQL: Syntax provides detailed information about the type of concurrency that each isolation level enforces for B-tree indexes.