Check isolation levels

The isolation level affects the concurrency between sessions that access the same set of data.

The following tables show the types of phenomena that can occur without appropriate isolation-level controls.
  • A Dirty Read occurs because transaction 2 sees the uncommitted results of transaction 1. 
    Transaction 1    Write(a)                    Roll Back
    Transaction 2                 Read(a)
  • A Nonrepeatable Read occurs if transaction 1 retrieves a different result from the each read. 
    Transaction 1     Read(a)                              Read(a)
    Transaction 2             Write/Delete(a)   Commit
  • A Phantom Read occurs if transaction 1 obtains a different result from each Select for the same criteria. 
    Transaction 1     Select(criteria)                   Select(criteria)
    Transaction 2               Update/Create   Commit
To determine which of the following isolation levels the user or application specifies, the access method can call either the mi_tab_isolevel() or mi_scan_isolevel() function.
Isolation level Type of read prevented
Serializable Dirty Read, Nonrepeatable Read, Phantom Read
Repeatable Read or Cursor Stability Dirty Read, Nonrepeatable Read
Read Committed Dirty Read
Read Uncommitted None

A virtual-index interface cannot use the COMMITTED READ LAST COMMITTED isolation level feature.

For more information about how applications use isolation levels, consult the Informix® Guide to SQL: Reference, Informix Guide to SQL: Syntax, and Informix Guide to SQL: Tutorial.

The database server automatically enforces repeatable read isolation under the following conditions:
  • The virtual index and all the table data that it accesses in sbspaces.
  • User-data logging is turned on for the smart large objects that contain the data.

    To find out how to turn on user-data logging with the access method, see Activate automatic controls in sbspaces. To find out how to provide for logging with ONCONFIG parameters, see your Informix Administrator's Guide.

The access method must provide the code to enforce isolation levels if users require Serializable isolation. The database server does not provide support for full Serializable isolation.

The access method must provide the code to enforce isolation levels under the following circumstances:
  • Users require Serializable isolation.

    The database server does not provide support for full Serializable isolation.

  • Some or all of the data are in extspaces.
Important: You must document the isolation level that the access method supports in a user guide. For an example of how to word the isolation-level notice, see Sample language to describe isolation level.