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?
Various Locking mechanism for cursors and other SQL?
Moderators: Kalicharan, Moderator Group
-
- Member
- Posts: 14
- Joined: Fri Apr 06, 2007 11:22 pm
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?
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.
Thanks,
Veera.
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.
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 .
Veera.
-
- Member
- Posts: 14
- Joined: Fri Apr 06, 2007 11:22 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??
Also you mentioned Cursor Stability as default but I thought Repeatable Read was default. Which one is correct??
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
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.
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.
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.
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page