Various Locking mechanism for cursors and other SQL?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
Chandra Shekar G R
Member
Posts: 14
Joined: Fri Apr 06, 2007 11:22 pm

Various Locking mechanism for cursors and other SQL?

Post by Chandra Shekar G R » Wed Apr 18, 2007 11:58 pm

Can somebody help me understand various locking mechanism that DB2 implements for cursors and other SQLs?

On what basis DB2 decides which lock to use?

Veera
Moderator
Posts: 111
Joined: Wed Feb 22, 2006 2:59 pm

Post by Veera » Thu Apr 19, 2007 2:52 am

Chandra Shekar,

Its very vast and interesting topic , can't be summarized in a note there will be lots of documents in GOOGLE OR DB2 MATERIAL IN OUR IBMMAINFRAMEGURU SITE you can go thru those for more detailed understanding.

Find below some info @ high level to just give the basic understanding.....

what basis DB2 decides which lock to use?

Code: Select all


 BASED ON THE ISOLATION LEVEL PROVIDED IN COMPILATION  ROCESS.

 ISOLATION LEVEL IS ONE OF THE PARAMETER WE PROVIDE DURING THE

 BIND PROCESS.

Locking mechanism in DB2 is enforced thru ISOLATION LEVELS.

There are 4 isolation levels, based on each level DB2 performs

DB locking differently..

1---> CURSOR STABILITY (CS) -> Is the Default Isolation Level.
2---> READ STABILITY (RS)
3---> UNCOMMITTED READ (UR)
4---> REPEATABLE READ (RR)


V.I.M.P NOTE : Isolation Levels act differently for read-only and updatable cursors.
below info just gives generic understanding ....you can refer other manuals
or doc for complete understanding.

CURSOR STABILITY
++++++++++++++++

If we use CS the row currently under fetch will be locked , particular
row that is current cursor position.When the next row is selected from
the result set the lock on the previous row will be released.

During the duration of the lock held by the CS,no other applications
can do any DB operations apart from JUST SELECT.

The lock is held until changes are DB COMMITTED if any.


READ STABILITY
++++++++++++++

This isolation level locks only those rows which satisfies the
QUERY under execution.

We can assume on set of rows which qualifies the query condition ,
i.e. the entire result set.

Accordingly no DB operations are possible apart from read...
But under few scenarios some DB operations are possible
if the LOCKS are shared ...not if its EXCLUSIVE...

UNCOMMITTED READ
+++++++++++++++

Here no rows are locked , its just a read ..DB operations
are possible by other Applications/pgms.

IF some updates are done by other pgms then the UR read rows also
reflects that even before the DB is COMMITTED.

REPEATABLE READ
+++++++++++++++

We can assume this Isolation level to be LOCK @ TABLE'S accessed in QUERY.

if we execute the same QUERY N times during the lock we will get the same
results for each execution.

Similarly at this Isolation level no DB operations except for read are possible
by other Application /pgms.

Unlike UR level in RR level uncommitted data will not be fetch.

Code: Select all


Better way to study is to look into comparison aspects of each level
and prepare a MATRIX with diff attributes , this info can be found in 
any doc in various sites . 

Thanks,
Veera.

Chandra Shekar G R
Member
Posts: 14
Joined: Fri Apr 06, 2007 11:22 pm

Post by Chandra Shekar G R » Thu Apr 19, 2007 2:00 pm

I understand the locking mechanism with isolation levels. I know there are various other ways of achieving locks through Tablespace or through overrides (LOCK statement) but what is not clear to me is among these locking mechanisms (isolation level, tablespace, override,...) which one takes precedence?

Also you mentioned Cursor Stability as default but I thought Repeatable Read was default. Which one is correct??

Veera
Moderator
Posts: 111
Joined: Wed Feb 22, 2006 2:59 pm

Post by Veera » Fri Apr 20, 2007 9:48 pm

LOCK TABLE statement locks all tables in the table space. Highest Priority

This command should be used with utmost caution depending on ur Batch Vs Online trails, Because when the command is used it will also prevent the online users from any Updations.

The Lock is held until COMMIT or DEAL LOCATION is done. And locking the TABLE reduces concurrent access to all tables in the table space.

LOCK TABLE IS @ TABLE SPACE WHICH MEANS ALL THE TABLES IN THAT PARTICULAR TABLE SPACE ARE LOCKED.

LOCKING USING ISOLATION LEVELS DOES LOCKS @ THE PAGE LEVEL.

But yes LOCK TABLE will override the locks at the page level( ISOLATION LEVEL) and entire TABLES IN TABLE SPACES are locked.

IT IS NOT JUST A STATEMENT THAT ONE CAN MAKE WHAT WUD BE THE HIERARCHY, AND AS SUCH THERE IS NO COMPATIBILITY , BUT YES THERE IS

INTEROPERABILITY OR DEPENDENCY BETWEEN LOCKS WHEN YOU USE WITH COMBINATION OF ISOLATION LEVEL AND LOCK TABLE SPACES.

MOST IMPORTANTLY IT ALL DEPENDS ON HOW YOU WOULD LIKE TO Parameterize LockS.

LIKE TABLE or NORMAL PARAMETER DECIDES THE LOCKING GRANULARITY , I MEAN THE EXTENT TO WHICH THE LOCKS SHOULD BE HELD.

THERE ARE VARIOUS OTHER ATTRIBUTES WHICH ALSO DETERMINES THE LOCKS PERFORMACNE/HIERACHY/PRECEDNCE

A--> HOW EFFICIENTLY THE USER IS COMMITTING
B--> THE VOLUME OF UNIT OF WORK HE IS PERFORMING THRU A PROGRAM OR APPLICATION
C--> THE RESTART LOGICS

Code: Select all


Well lets deal with the same example

Take an example where you have a table in a table space with excluseive 

and you have multiple programs accessing the same table with different 

Isolation levels. 

WHEN THERE ARE MULTIPLE PROGRAMS WITH DIFF ISOLATION LEVELS 

THEN THEY COMPETE WITH  EACH OTHER BASED ON EACH ISOLATION 

LEVEL PRECEDENCE AS WE HAVE LEARNT IN THE PREVIOUS POST. BUT 

IF U  HAVE A HIGHER PRECEDENT LOCKS LIKE ASSUME TABLE LOCK 

THAT  PRECEDES ALL THE OTHER PROGRAMS ISOLATING LEVEL LOCKS.

PLEASE NOTE :

SINCE WE CANT POST LINKS TO OTHER SITES INFO I AM NOT SUPPOSED

TO GIVE LINKS , THERE IS WORLD OF INFO REG THIS TOPIC. AS I SAID

EARLIER ALL THESE TOPICS CANT BE BRIEFED OVER THE POSTS ONLY

ONE CAN TRY TO GIVE THE BASIC UNDERSTANDING TOWARDS IT. IT

DEPENDS ON EACH INDIVIDUAL WITH WHAT EXTENT OF INFO OR

EXPLANATION ONE WILL BE SATISFIED OR UNDERSTANDS THE BASIC

INFO.

CS IS THE DEFAULT ISOLATION LEVEL. NORMALLY IF WE SPECIFICALLY

MENTION THE LEVEL FOR SQL THEN ITS OBVIOUS IT EXECUTES WITH

THAT LEVEL.

Thanks,
Veera.
Last edited by Veera on Fri Apr 20, 2007 10:02 pm, edited 1 time in total.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others