Checking Isolation Levels

Home | Previous Page | Next Page Design Decisions > Supporting Data Retrieval, Manipulation, and Return >

Checking 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

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.

For more information about how applications use isolation levels, consult the IBM Informix: Guide to SQL Reference, IBM Informix: Guide to SQL Syntax, and IBM Informix: Guide to SQL Tutorial. For information about determining isolation level, refer to mi_scan_isolevel() or mi_tab_isolevel() in Descriptor Function Reference.

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, refer to Activating Automatic Controls in Sbspaces. To find out how to provide for logging with ONCONFIG parameters, refer to your IBM 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, refer to Figure 14.