Want in depth description on CS and RR (DB2)

sasidhar001 · New User Joined: 14 May 2006 Posts: 3 Location: Bangalore

Hi Can anybody post in depth explanation of RR and CS in practical situation. It would be helpful because this is the must question for every interview.

guptae · Posted: Thu Nov 09, 2006 11:33 am

Hi there,

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.

chettiyar · Posted: Tue Nov 21, 2006 2:42 pm

Isolation level refers to the extent by which the data
accessed by a transaction is isolated from all other
transactions.

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.