SET ISOLATION statement

Use the SET ISOLATION statement to define the degree of concurrency among processes that attempt to access the same rows simultaneously.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

SET ISOLATION [TO] { REPEATABLE READ | { COMMITTED READ [LAST COMMITTED] | CURSOR STABILITY | DIRTY READ [ WITH WARNING ] } [ RETAIN UPDATE LOCKS ] }

Usage

The SET ISOLATION statement is the Informix® extension to the ANSI SQL-92 standard. The SET ISOLATION statement can change the enduring isolation level for the session. If you want to set isolation levels through an ANSI-compliant statement, use the SET TRANSACTION statement instead. For a comparison of these two statements, see SET TRANSACTION statement.

The TO keyword is optional, and has no effect.

SET ISOLATION provides the same functionality as the ISO/ANSI-compliant SET TRANSACTION statement for isolation levels of DIRTY READ (called UNCOMMITTED in SET TRANSACTION), COMMITTED READ, and REPEATABLE READ (called SERIALIZABLE in SET TRANSACTION).

The database isolation_level affects read concurrency when rows are retrieved from the database. The isolation level specifies the phenomena that can occur during execution of concurrent SQL transactions. The following phenomena are possible:
  • Dirty Read. SQL transaction T1 modifies a row. SQL transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed, and therefore can be considered never to have existed.
  • Non-Repeatable Read. SQL transaction T1 reads a row. SQL transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread that row, T1 might receive the modified value or discover that the row has been deleted.
  • Phantom Row. SQL transaction T1 reads the set of rows N that satisfy some search condition. SQL transaction T2 then executes SQL statements that generate one or more new rows that satisfy the search condition used by SQL transaction T1. If T1 then repeats the original read with the same search condition, T1 receives a different set of rows.

The database server uses shared locks to support different levels of isolation among processes attempting to access data.

The update or delete process always acquires an exclusive lock on the row that is being modified. The level of isolation does not interfere with rows that you are updating or deleting. If another process attempts to update or delete rows that you are reading with an isolation level of Repeatable Read, that process is denied access to those rows.

In Informix® ESQL/C, cursors that are open when SET ISOLATION executes might or might not use the new isolation level when rows are retrieved. Any isolation level that was set from the time the cursor was opened until the application fetches a row might be in effect. The database server might have read rows into internal buffers and internal temporary tables using the isolation level that was in effect at that time. To ensure consistency and reproducible results, close any open cursors before you execute the SET ISOLATION statement.

You can issue the SET ISOLATION statement from a client computer only after a database is opened.