DB2 UDB isolation level

Description

Cursor Stability

Addresses 

dirty read

issue

Sees only committed data from other 

transaction

Lock is only held on cursor position 

unless update occurs

Update lock is held until transaction 

completed = commit

Read Stability

Addresses 

non repeatable read

issue

Sees only committed data from other 

transaction

Locks are held on every row fetched 

(Inserts permitted)

Locks are held for duration of 

transaction (commit/rollback)

Repeatable Read

Addresses 

phantom read

 issue

All record locks held for duration of 

transaction

A repeated query within the same 

transaction will get the same result set 

(Inserts are prevented)

As one can see, the isolation levels listed in Table 7 10 are ordered descendent 

according to the number and duration of locks held during the transaction, and 

therefore the degree of concurrency or locking is required to ensure the desired 

level of data integrity. However, as we can see too much locking drastically 

reduces concurrency. Poor application design and coding may cause locking 

problems such as:

Deadlocks

Lock waits

Lock escalation

Lock time outs

By default DB2 operates with the isolation level 

cursor stability

. 

Transaction 

isolation can be specified at many different levels as discussed in 7.3.5, 

 Specifying the isolation level in DB2  on page 230. For good performance, verify 

the lowest isolation level required for your migrated application.

For additional information on the DB2 UDB concurrency implementation, please 

refer to the DB2 UDB manual 

Administration Guide: Performance,

 SC09 4821.

228 

MySQL to DB2 UDB Conversion Guide