Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Doubt in CS and RR Isolation levels..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Doubt in CS and RR Isolation levels..
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    Post subject:
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: 755
Location: Germany

PostPosted: Mon Jun 23, 2008 3:57 pm    Post subject:
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    Post subject: Reply to: Doubt in CS and RR Isolation levels..
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    Post subject:
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    Post subject: Reply to: Doubt in CS and RR Isolation levels..
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Doubt in MFS abdulrafi IMS DB/DC 2 Fri Oct 21, 2016 3:09 pm
No new posts Doubt on GETMAIN, FREEMAIN behaviour Arunkumar Chandrasekaran CICS 3 Sun Aug 30, 2015 9:22 pm
No new posts Doubt in using command code O Appu IMS DB/DC 4 Tue Jan 13, 2015 3:14 pm
No new posts Doubt in using command code D and N t... Appu IMS DB/DC 7 Mon Dec 29, 2014 5:52 pm
This topic is locked: you cannot edit posts or make replies. ISREDIT macro command doubt sakrat TSO/ISPF 60 Wed Feb 05, 2014 6:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us