|
|
| Author |
Message |
Chandra Shekar G R
Member
Joined: 06 Apr 2007
Posts: 14
|
 Posted: Wed Apr 18, 2007 11:58 pm Post subject: Various Locking mechanism for cursors and other SQL? |
 |
|
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? |
|
| Back to top |
|
|
|
Veera
Moderator
Joined: 22 Feb 2006
Posts: 111
|
| Posted: Thu Apr 19, 2007 2:52 am Post subject: |
|
|
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: |
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: |
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. |
|
| Back to top |
|
|
|
Chandra Shekar G R
Member
Joined: 06 Apr 2007
Posts: 14
|
| Posted: Thu Apr 19, 2007 2:00 pm Post subject: |
|
|
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?? |
|
| Back to top |
|
|
|
Veera
Moderator
Joined: 22 Feb 2006
Posts: 111
|
| Posted: Fri Apr 20, 2007 9:48 pm Post subject: |
|
|
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: |
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 |
|
| Back to top |
|
|
|
|
|
|
FAQ 
Search 
Register
Profile 
Log in to check your private messages 
Log in 
