INFORMIX
Informix Guide to SQL: Tutorial
Chapter 7: Programming for a Multiuser Environment
Home Contents Index Master Index New Book

Setting the Isolation Level

 The isolation level is the degree to which your program is isolated from the concurrent actions of other programs. Universal Server offers a choice of isolation levels. It implements them by setting different rules for how a program uses locks when it is reading. (This description does not apply to reads performed on update cursors.)

To set the isolation level, use either the SET ISOLATION or SET TRANSACTION statement. The SET TRANSACTION statement also lets you set access modes in Universal Server. For more information about access modes, see "Controlling Data Modification with Access Modes".

Comparing SET TRANSACTION with SET ISOLATION

The SET TRANSACTION statement complies with ANSI SQL-92. This statement is similar to the Informix SET ISOLATION statement; however, the SET ISOLATION statement is not ANSI compliant and does not provide access modes.

The isolation levels that you can set with the SET TRANSACTION statement are comparable to the isolation levels that you can set with the SET ISOLATION statement, as the following table shows.
SET TRANSACTION Correlates to SET ISOLATION

Read Uncommitted

Dirty Read

Read Committed

Committed Read

Not Supported

Cursor Stability

(ANSI) Repeatable Read Serializable

(Informix) Repeatable Read (Informix) Repeatable Read

The major difference between the SET TRANSACTION and SET ISOLATION statements is the behavior of the isolation levels within transactions. The SET TRANSACTION statement can be issued only once for a transaction. Any cursors opened during that transaction are guaranteed to get that isolation level (or access mode if you are defining an access mode). With the SET ISOLATION statement, after a transaction is started, you can change the isolation level more than once within the transaction. The following examples show both the SET ISOLATION and SET TRANSACTION statements:

SET ISOLATION

SET TRANSACTION

ANSI Read Uncommitted and Informix Dirty Read Isolation

 The simplest isolation level, ANSI Read Uncommitted and Informix Dirty Read, amounts to virtually no isolation. When a program fetches a row, it places no locks, and it respects none; it simply copies rows from the database without regard for what other programs are doing.

A program always receives complete rows of data; even under ANSI Read Uncommitted or Informix Dirty Read isolation, a program never sees a row in which some columns have been updated and some have not. However, a program that uses ANSI Read Uncommitted or Informix Dirty Read isolation sometimes reads updated rows before the updating program ends its transaction. If the updating program later rolls back its transaction, the reading program processed data that never really existed (number 4 in the list of concurrency issues on page 7-5).

ANSI Read Uncommitted or Informix Dirty Read is the most efficient isolation level. The reading program never waits and never makes another program wait. It is the preferred level in any of the following cases:

ANSI Read Committed and Informix Committed Read Isolation

 When a program requests the ANSI Read Committed or Informix Committed Read isolation level, Universal Server guarantees that it never returns a row that is not committed to the database. This action prevents reading data that is not committed and that is subsequently rolled back.

ANSI Read Committed or Informix Committed Read is implemented very simply. Before it fetches a row, the database server tests to determine whether an updating process placed a lock on the row; if not, it returns the row. Because rows that are updated but not committed have locks on them, this test ensures that the program does not read uncommitted data.

ANSI Read Committed or Informix Committed Read does not actually place a lock on the fetched row, so it is almost as efficient as ANSI Read Uncommitted or Informix Dirty Read. It is appropriate for use when each row of data is processed as an independent unit, without reference to other rows in the same or other tables.

Informix Cursor Stability Isolation

The next level, Cursor Stability, is available only with the Informix SQL statement SET ISOLATION. When Cursor Stability is in effect, the database server places a lock on the latest row fetched. It places a shared lock for an ordinary cursor or a promotable lock for an update cursor. Only one row is locked at a time; that is, each time a row is fetched, the lock on the previous row is released (unless that row is updated, in which case the lock holds until the end of the transaction).

Cursor Stability ensures that a row does not change while the program examines it. Such row stability is important when the program updates some other table based on the data it reads from this row. Because of Cursor Stability, the program is assured that the update is based on current information. It prevents the use of stale data.

The following example illustrates the point. In terms of the demonstration database, Program A wants to insert a new stock item for manufacturer Hero (HRO). Concurrently, Program B wants to delete manufacturer HRO and all stock associated with it. The following sequence of events can occur:

    1. Program A, operating under Cursor Stability, fetches the HRO row from the manufact table to learn the manufacturer code: This action places a shared lock on the row.

    2. Program B issues a DELETE statement for that row. Because of the lock, the database server makes the program wait.

    3. Program A inserts a new row in the stock table using the manufacturer code it obtained from the manufact table.

    4. Program A closes its cursor on the manufact table or reads a different row of it, releasing its lock.

    5. Program B, released from its wait, completes the deletion of the row and goes on to delete the rows of stock that use manufacturer code HRO, including the row just inserted by Program A.

If Program A used a lesser level of isolation, the following sequence could occur:

    1. Program A reads the HRO row of the manufact table to learn the manufacturer code. No lock is placed.

    2. Program B issues a DELETE statement for that row. It succeeds.

    3. Program B deletes all rows of stock that use manufacturer code HRO.

    4. Program B ends.

    5. Program A, not aware that its copy of the HRO row is now invalid, inserts a new row of stock using the manufacturer code HRO.

    6. Program A ends.

At the end, a row occurs in stock that has no matching manufacturer code in manufact. Furthermore, Program B apparently has a bug; it did not delete the rows that it was supposed to delete. The use of the Cursor Stability isolation level prevents these effects.

The preceding scenario could be rearranged to fail even with Cursor Stability. All that is required is for Program B to operate on tables in the reverse sequence to Program A. If Program B deletes from stock before it removes the row of manufact, no degree of isolation can prevent an error. Whenever this kind of error is possible, all programs that are involved must use the same sequence of access.

Because Cursor Stability locks only one row at a time, it restricts concurrency less than a table lock or database lock does.

ANSI Serializable, ANSI Repeatable Read, and Informix Repeatable Read Isolation

The definitions for ANSI Serializable, ANSI Repeatable Read, and Informix Repeatable Read isolation levels are all the same.

The Repeatable Read isolation level asks the database server to put a lock on every row the program examines and fetches. The locks that are placed are shareable for an ordinary cursor and promotable for an update cursor. The locks are placed individually as each row is examined. They are not released until the cursor closes or a transaction ends.

Repeatable Read allows a program that uses a scroll cursor to read selected rows more than once and to be sure that they are not modified or deleted between readings. (Scroll cursors are described in Chapter 5, "Programming with SQL.") No lower isolation level guarantees that rows still exist and are unchanged the second time they are read.

Repeatable Read isolation places the largest number of locks and holds them the longest. Therefore, it is the level that reduces concurrency the most. If your program uses this level of isolation, think carefully about how many locks it places, how long they are held, and what the effect can be on other programs.

In addition to the effect on concurrency, the large number of locks can be a problem. The database server records the number of locks by each program in a lock table. If the maximum number of locks is exceeded, the lock table fills up, and the database server cannot place a lock. An error code is returned. The person who administers a database server system can monitor the lock table and tell you when it is heavily used.

The Serializable isolation level is automatically used in an ANSI-compliant database. The Serializable isolation level is required to ensure operations behave according to the ANSI standard for SQL.



Informix Guide to SQL: Tutorial, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.