IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Doubt in CS and RR Isolation levels..


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sanjay_mf

New User


Joined: 30 May 2008
Posts: 10
Location: Bangalore

PostPosted: Mon Jun 23, 2008 3:07 pm
Reply with quote

1) Program A defined with CS isloation level retrieves a row from a
table. Another Program B wants to access the same table. Can program B read the row which Program A is updating? Can program B read or update or delete other rows?


2) Program A defined with RR isloation level retrieves a row from a
table. Another Program B wants to access the same table. Can program B read the row which Program A is updating? Can program B read or update or delete other rows?


3) Program A defined with CS/RR isloation level is accessing 10 tables. Are all the 10 tables locked (in case of CS/RR) ?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 23, 2008 3:51 pm
Reply with quote

Suppose the table has 100 rows and as per the query DB2 has to scan say 50 rows which may or may not satisfy the where condition ....

Now when you are using CS it will lock only the row which it is currently accessing while RR will lock all the scanned rows ie 50 rows even if the result set has only 10 rows ....

So no other programs can update or delete the locked rows ... for CS the single row on which the cursor is positioned cannot be updated or deleted and for RR none of the 50 rows can be updated or deleted by other transactions ...

Now for reading the rows using CS / RR depends on the lock which is being held on the table ...if it is an exclusive lock all other transactions can neither read nor modify the held rows ....

isolation levels are a way to control how data is accessed by concurrent transactions through the use of locks ....

So for your question 1 and 2 ... no update or delete can occur and the ability to read depends on the lock being held ...

for 3 it depends on the type of lock defined on the table ..if it is a table level lock then the table will be locked if its row level only the rows satisfying the condition will be locked ....ROW level lock is the default ...
Back to top
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 771
Location: Germany

PostPosted: Mon Jun 23, 2008 3:57 pm
Reply with quote

Pleasant morning Sir !

Some news about DB2 / RR-CS

In DB2 9, the CURRENTDATA default is changed from YES to NO. The CURRENTDATA parameter is used for a couple of different reasons, but its primary purpose today is to help reduce network traffic for distributed applications. More and more companies have moved their data from servers running DB2, SQL Sever and Oracle to a centralized database serving DB2 for z/OS. CURRENTDATA determines whether to require data currency for read-only and ambiguous cursors when the isolation level of cursor stability (CS) is in effect. In other words, the data row where the cursor is positioned cannot change while the cursor is there. This parameter also determines whether block fetching can be used for distributed, ambiguous cursors. Block fetching is important to reduce the amount of back and forth traffic "messages" your application must make with DB2.


The NO default specifies that currency isn't required for read-only and ambiguous cursors. Block fetching for a distributed, ambiguous cursor is allowed. An ambiguous cursor is one in which FOR FETCH ONLY or FOR READ ONLY isn't specified, and DB2 is unsure if the data will be changed. So this is a good thing because in most cases you're just reading data and you want DB2 to block fetch the data to reduce network traffic.

The YES default specifies that currency is required for read-only and ambiguous cursors. DB2 acquires page or row locks to ensure data currency. Block fetching for a distributed, ambiguous cursor is inhibited.

The ISOLATION parameter tells DB2 what type of locking your application needs. The old defaults of repeatable read (RR) ensure that your application doesn't read a row that another application has changed until that application releases the row. It also ensures that another application doesn't change a row your application read until your application releases that row. Using ISOLATION(RR) can greatly reduce your concurrency and increase CPU usage.

The most commonly used ISOLATION level, CS, ensures that, like RR, your application doesn't read a row that another application has changed until that application releases the row. Unlike RR, CS doesn't prevent other applications from changing rows that your application reads before your application commits or terminates.

Interestingly, to take advantage of DB2 lock avoidance, you must bind with CURRENTDATA(NO) and ISOLATION(CS). So with the new BIND defaults you not only get improved network traffic and improved performance, but improved concurrency.

More Informations: z/OS Performance Monitoring and Tuning Guide
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Mon Jun 23, 2008 8:46 pm
Reply with quote

Quote:
ROW level lock is the default ...


I would have to disagree, the LOCKSIZE on the table would decide this. The default Locksize is page.

regards,
asci(i)
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 23, 2008 9:00 pm
Reply with quote

LOCKSIZE PAGE is for TABLESPACE and not for TABLE ..
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Mon Jun 23, 2008 9:09 pm
Reply with quote

oops!... yes ur right... So the Locks held in the table within this Tablespace is governed by the LOCKSIZE...!

regards,
asci(i)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Doubt about pl/1 (job offer) General Talk & Fun Stuff 5
No new posts doubt when executing a file when logg... TSO/ISPF 2
No new posts Doubt in MFS IMS DB/DC 2
No new posts Doubt on GETMAIN, FREEMAIN behaviour CICS 3
No new posts Doubt in using command code O IMS DB/DC 4
Search our Forums:

Back to Top