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:
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.
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".