HCL 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, Informix 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). Because Cursor Stability locks only one row at a time, it restricts concurrency less than a table lock or database lock.

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 the 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 effective use of Cursor Stability isolation. 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 that Program A just inserted.
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. 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.