The ISOLATION (CS) option

The ISOLATION (CS) or cursor stability option allows maximum concurrency with data integrity. Under the ISOLATION (CS) option, a transaction holds locks only on its uncommitted changes and on the current row of each of its cursors.

Begin general-use programming interface information.
However, after the process leaves a row or page, another process can change the data. With CURRENTDATA(NO), the process does not have to leave a row or page to allow another process to change the data. If the first process returns to read the same row or page, the data is not necessarily the same. Consider the following consequences of that possibility:

  • For table spaces created with LOCKSIZE ROW, PAGE, or ANY, a change might occur even while executing a single SQL statement that reads the same row multiple times. In the following statement, data read by the inner SELECT might be changed by another transaction before it is read by the outer SELECT.
    SELECT * FROM T1
  WHERE C1 = (SELECT MAX(C1) FROM T1);
    Therefore, the information returned by this query might be from a row that is no longer the one with the maximum value for C1.
  • Another case is a process that reads a row and returns later to update it. That row might no longer exist or might not exist in the state that it did when your application process originally read the row. That is, another application might have deleted or updated the row. If your application is doing non-cursor operations on a row under the cursor, make sure that the application can tolerate "not found" conditions.

    Similarly, assume that another application updates a row after it is read by your application. If your application returns later to update the row based on the read-in value, your application erases the update from the second application.

    Therefore, if you use ISOLATION(CS) with update, your process might need to lock out concurrent updates. One method is to declare a cursor with the FOR UPDATE clause.

For packages and plans that contain updatable static scrollable cursors, ISOLATION(CS) enables DB2® to use optimistic concurrency control. DB2 can use optimistic concurrency control to shorten the amount of time that locks are held in the following situations:

  • Between consecutive fetch operations
  • Between fetch operations and subsequent positioned update or delete operations

DB2 cannot use optimistic concurrency control for dynamic scrollable cursors. With dynamic scrollable cursors, the most recently fetched row or page from the base table remains locked to maintain position for a positioned update or delete.

The two following figures show processing of positioned update and delete operations without optimistic concurrency control and with optimistic concurrency control.

Figure 1. Positioned updates and deletes with a static non-scrollable cursor and without optimistic concurrency control
Begin figure description. A timeline that shows operations in an application and for updates and deletes without optimistic concurrency control. End figure description.
Figure 2. Positioned updates and deletes with a static sensitive scrollable cursor and with optimistic concurrency control
Begin figure description. A timeline that shows operations in an application and for updates and deletes with optimistic concurrency control. End figure description.

Optimistic concurrency control consists of the following steps:

  1. When the application requests a fetch operation to position the cursor on a row, DB2 locks that row, executes the fetch operation, and releases the lock.
  2. When the application requests a positioned update or delete operation on the row, DB2 performs the following steps:
    1. Locks the row.
    2. Re-evaluates the predicate to ensure that the row still qualifies for the result table.
      End general-use programming interface information.