Cursor Stability Isolation Level

The DB2 Cursor Stability (CS) isolation level has been enhanced in DB2 V9.7 to reduce significantly instances of lock wait and deadlock. In previous DB2 versions, CS prevented any row that was changed by other applications from being read until the change was committed.

In this enhanced implementation, CS, where possible, avoids a read operation waiting for a row to commit before returning a value. CS now returns the currently committed result, ignoring what might happen to an uncommitted operation. Some exceptions, such as updatable cursors, exist; currently committed results cannot be returned immediately when the row might be updated based upon its previous contents. CS behavior is determined through the Concurrent Access Resolution connection option.

Consider the following example, in which deadlocks are avoided under the currently committed semantics. In this scenario, two applications update two separate tables, but do not yet commit. Each application then attempts to read (with a read-only cursor) from the table that the other application has updated.

Table 5-6. Cursor Stability Examples

Step	Application A	Application B
1	UPDATE T1 SET col1 = ? WHERE col2 = ?	UPDATE T2 SET col1 = ? WHERE col2 = ?
2	SELECT col1, col3, col4 FROM T2   WHERE col2 >= ?	SELECT col1, col5, FROM T1 WHERE col5 = ?   AND col2 = ?
3	Commit	Commit

Without currently committed semantics, these applications running under the cursor stability isolation level might create a deadlock, causing one of the applications to fail. This happens when each application needs to read data that is being updated by the other application.

Under currently committed semantics, if the query in step 2 (for either application) happens to require the data currently being updated by the other application, that application does not wait for the lock to be released, making a deadlock impossible. The previously committed version of the data is located and used instead.