Joined: 14 Oct 2005 Posts: 1191 Location: Bangalore,India
RR: Repeatable read holds all locks untile the transaction completes or a syncpoint is issued.
CS: Cursor stablity means that DB2 takes a lock on the page the cursor is accessing and releases the lock when the cursor moves to another page.
Isolation level refers to the extent by which the data
accessed by a transaction is isolated from all other
DB2 supports 2 Isolation Levels for every transaction
Cursor Stability (CS).
Repeatable Read (RR) (Default).
Cursor Stability (CS)
DB2 takes a lock on the page the cursor is accessing and releases the lock on that page when the cursor moves onto a different page. This is not done when we use FOR UPDATE OF statement in cursor.
The lock on the last page is released at commit time or at thread deallocation time.
Repeatable Read (RR)
In CS, while your transaction reads data, other transaction could change the data you have already read.
In RR, DB2 holds all the page locks while the cursor is moving on till the transaction commits or the thread is deallocated.
CS provides higher concurrency.
RR provides higher consistency.
Read Stability (RS)
Is similar to RR, but without allowing qualifying data to be updated or deleted by another process. It offers greater concurrency than repeatable read, because although other applications cannot change rows that are returned to the original application, they can insert
new rows, or update rows that did not satisfy the original application's search condition.