My favorite performance items in DB2 LUW v9.7 - #3: Currently Committed

DB2 v9.7 introduced the CURRENTLY COMMITTED (CC) feature, which can provide a nice performance boost to applications suffering from lock contention.   The most common scenario where CC comes into play is where one connection running in the default cursor stability (CS) isolation attempts to read a row which is locked for update by another connection.  Normally, the reader would be blocked until either the maximum lock wait time had elapsed (resulting in a rollback), or the writer had committed and released the lock.  

Now, of course, there are cases where instead of CS isolation, variants of 'dirty read' are acceptable, and for those there is the UR isolation level, plus a handful of registry variables that have been introduced to help improve concurrency (e.g. DB2_EVAL_UNCOMMITTED, DB2_SKIP_DELETED, DB2_SKIP_INSERTED, etc.)  However, with CC, the reader receives (you guessed it) the currently committed value of the row, extracted from the transaction log.  This has the obvious benefits of improving concurrency, while still maintaining use of committed, consistent data.

CC is controlled with the CUR_COMMIT database configuration parameter, and defaults ON for new databases, and OFF for databases migrated from earlier versions (this difference is required to maintain the locking behavior as experienced prior to migration.)  It can also be turned on for individual applications with a new BIND option.  Systems which will benefit most from enabling CC are those which display significant lock contention - indicated by a high & increasing lock wait time (e.g. LOCK_WAIT_TIME from the MON_GET_SERVICE_SUBCLASS table function), and/or a large portion of applications in 'lock wait' state (many rows returned from the MON_GET_APPL_LOCKWAIT table function.)

If CC is enabled and the engine needs to look at the logs for committed values, it's more efficient for it to find them in the log buffer, rather than having to go to the log disk.  So, a word to the wise - for best CC performance, make sure the log buffer is a "reasonable" size - say, around 1024 pages.   Also note that CC consumes some additional log space on disk - again, because of the extra information being stored - so enabling CC may require some increase in log configuration.

A good tip - once CC is enabled, you can tell how frequently it's pulling back committed values from the log, and whether it's finding them in the log buffer or on disk, via the db2pd -log command.

Cur Commit Disk Log Reads     1771
Cur Commit Total Log Reads   8211

See the Information Center for more information about Currently Committed.

Recent Stories
Tips for getting the best INSERT performance

Statistics Event Monitors - terrible name, terrific feature

Measuring OVERHEAD and TRANSFERRATE on DB2 LUW - the why & how