WHAT IS ISOLATION

[solanki123]

Can anybody explain me what is isolation level.
We mention this parameter as CS(cursor stability) or
RR(repeatability read) in BIND pgm. What is difference
between CS and RR.

 

[MarcLodge]

This link should do....

http://www.starquest.com/Supportdocs/Starsqldocs/starsql/Isolation_Levels.htm

 

[solanki123]

It is not clear in this link.
I want to know the exact difference between RR and CS.
I mean what will happen if i put isolation=RR and what
will happen if i put isolation=CS

 

[gregsimpson]

Solanki123,

Isolation(CS) acquires and releases page locks as pages are read and processed. CS provides the greatest level of concurrency at the expense of potentially different data being retruned by the same cursor, if it is processed twice during the same unit of work.

Isolation(RR) holds page and row locks until a COMMIT point; no other program can modify the data. If the data is accessed twice during the unit of owrk the exact same data will be returned.

In summary, with CS your result set can change to pick up other transactions updates if you use CS. If you use RR you are gauranteed your CURSOR will not change in a UOW no matter how many times you process the CURSOR.

Cheers
Greg

 

[Arfi]

I have one major confussion regarding locking, basically i am not able to relate ISOLATION LEVEL and AQUIRE & RELEASE parameter (Bind Parameter) in context to locking.
It is said when you specify CS as Isolation Level then the page lock will be released as soon as the cursor moves to the next page. Now if RELEASE is Deallocate then the tablespace lock will be removed after the plan is deallocated.
Now my question is, what good can releasing a page lock can do, when the table space lock is still there (because you have mentioned RELEASE as deallocate, or even if you have mentioned RELEASE as commit), and my understing is if tablespace/table is locked then all the pages in that Table or Tablespace is also locked.

Can someone help me in clearing my concepts.

Thanks in Advance

 

[solanki123]

Excellent Greg,
You gave me exact difference.
Thank you very much

 

[solanki123]

Greg,I have come to know one more isolation parameter that is UR(uncommitted read),
Could you please explain me what is UR, and what are the different situations where we use all these parameters(CS,RR and UR).

 

[sathyarams]

There is one more islation level - RS

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[gregsimpson]

It's also known as a dirty read. It can be used to access uncommitted data changes of other apps. An application using the UR isolation level will return all matching rows, including those in the process of being modified by another transaction which it may not yet have committed.

If you're interested in RS then, it basically locks those rows that are part of a result set. If you decide to use this you will always get the same rows if a query is executed once in a UOW, although you may also get phantom rows.

Cheers
Greg

 

[sathyarams]

Greg .. Why not copy-paste these four points and create a FAQ ?

Thanks

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8