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

If an access method does not support Serializable isolation for data in an extspace, an update by another transaction can change data on disk after the access method sends the same row to the database server. The disk data no longer matches the data that the database server placed in shared memory.

A virtual-table 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 table resides 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 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 resides 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 .