Cursor stability (CS) isolation level enhancements provide more concurrency

In Version 9.7, you can use the CS isolation level with currently committed semantics to significantly reduce lock wait and deadlock scenarios. It is the default for new databases.

In previous versions, CS prevented an application from reading any row that was changed by other applications until the change was committed. In Version 9.7, under CS, a read operation does not necessarily wait for a change to a row to be committed before returning a value. Where possible, a read operation now returns the currently committed result, ignoring what might happen to an uncommitted operation. An example of an exception is for updatable cursors; in that case, currently committed results cannot be returned immediately if the row might be updated based on its previous contents.

The new CS behavior is beneficial in high-throughput transaction-processing database environments. In such environments, waiting on locks cannot be tolerated. This new behavior is particularly beneficial if your applications run against databases from multiple vendors. You can use CS instead of writing and maintaining code pertaining to locking semantics specifically for DB2® databases.

This new CS behavior is disabled for existing databases that you upgrade from a previous release. You can enable or disable the behavior by using the new database configuration parameter cur_commit. Also, you can override the database-level setting for individual applications using the CONCURRENTACCESSRESOLUTION option of the BIND and PRECOMPILE commands. You can override the database-level setting for stored procedures using the DB2_SQLROUTINE_PREPOPTS registry variable and the SET_ROUTINE_OPTS procedure.

Currently committed semantics apply only to read-only scans that do not involve catalog tables or the internal scans that are used to evaluate constraints. Note that, because currently committed is decided at the scan level, a writer's access plan might include currently committed scans. For example, the scan for a read-only subquery can involve currently committed semantics. Because currently committed semantics obey isolation level semantics, applications running under currently committed semantics continue to respect isolation levels.

Currently committed semantics require more log space because additional space is required for logging the first update of a data row during a transaction. This log data is required for retrieving the currently committed image of the row. Depending on the workload, the additional log data can have an insignificant or measurable impact on the total log space used. The requirement for additional log space does not apply when cur_commit is disabled.

You can use the AIRLINE.war sample program to learn how to use this feature.