IBM Support

New DB2 Isolation level - Currently Committed for users of IBM Tivoli service management products

Technical Blog Post


Abstract

New DB2 Isolation level - Currently Committed for users of IBM Tivoli service management products

Body

 The default isolation level for DB2 is Cursor Stability (CS), which implements the READ_COMMITTED ANSI SQL Isolation Level. For cursor stability, the reader will acquire the read lock before accessing the data. However, if the writer has acquired write lock, the reader has to wait. The following table illustrates the DB2 Cursor Stability locking behavior:

However, unlike DB2, the default locking for Oracle does not lock the reader. As a result, applications originated from Oracle (like IBM Tivoli service management products) will likely run into long lock waits and much higher deadlocks while moving to DB2.

Starting from version 9.7, DB2 introduces a new isolation level semantic called "Currently Committed” that allows DB2 locking to generally work as Oracle does.  This means that applications can work with both DB2 and Oracle databases without having to code different logic. Under this "Currently Committed" semantics, only committed data is returned, as was in Cursor Stability, but now readers do not wait for writers to release the locks. Instead readers return data that is based on the currently committed version. See the following table for the Currently Committed locking behavior:

 
For new databases created under DB2 9.7, Currently Committed will be the default. If you migrate your database from older DB2 versions, you would need to turn on Currently Committed by setting the "CUR_COMMIT" database configuration parameter to "ON".
 

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

UID

ibm11134705

Page Feedback