DB2 Version 9.7 for Linux, UNIX, and Windows

Currently committed semantics improve concurrency

Lock timeouts and deadlocks can occur under the CS isolation level with row-level locking, especially with applications that are not designed to prevent such problems. Some high throughput database applications cannot tolerate waiting on locks that are issued during transaction processing, and some applications cannot tolerate processing uncommitted data, but still require non-blocking behavior for read transactions.

Under the new currently committed semantics, only committed data is returned, as was the case previously, but now readers do not wait for writers to release row locks. Instead, readers return data that is based on the currently committed version; that is, data prior to the start of the write operation.

Currently committed semantics are turned on by default for new databases. This allows any application to take advantage of the new behavior, and no changes to the application itself are required. The new database configuration parameter cur_commit can be used to override this behavior. This might be useful, for example, in the case of applications that require blocking on writers to synchronize internal logic.

Similarly, upgraded databases have cur_commit disabled by default in case applications require blocking writers to synchronize their internal logic, and this parameter can be turned on later, if so desired.

Currently committed semantics apply only to read-only scans that do not involve catalog tables or the internal scans that are used to evaluate or enforce 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 increased log space for writers. Additional space is required for logging the first update of a data row during a transaction. This data is required for retrieving the currently committed image of the row. Depending on the workload, this 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.

Restrictions

The following restrictions apply to currently committed semantics:

The target table object in a section that is to be used for data update or deletion operations does not use currently committed semantics. Rows that are to be modified must be lock protected to ensure that they do not change after they have satisfied any query predicates that are part of the update operation.
A transaction that has made an uncommitted modification to a row forces the currently committed reader to access appropriate log records to determine the currently committed version of the row. Although log records that are no longer in the log buffer can be physically read, currently committed semantics do not support the retrieval of log files from the log archive. This only affects databases that are configured to use infinite logging.
The following scans do not use currently committed semantics:
- Catalog table scans
- Scans that are used to enforce referential integrity constraints
- Scans that reference LONG VARCHAR or LONG VARGRAPHIC columns
- Range-clustered table (RCT) scans
- Scans that use spatial or extended indexes

Example

Consider the following scenario, 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.

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.