MVSFORUMS.com

sathishgay · Posted: Thu Feb 10, 2005 11:59 pm Post subject: isolation levels

Hi Can anyone help to know what is isolation level which is commonly used in DB2 BLIND PROCESS Repeatable read or Cusorstability.Plz explain why so?

Thanks and Regards
sathish

hari_uss · Posted: Fri Feb 11, 2005 6:03 am Post subject:

I would say that depends on what your application needs (even though most of the times we have it as CS in our installation). We use RR or UR very rarely.

Btw, your user id looks very odd.... Smile

hari_uss · Posted: Fri Feb 11, 2005 6:08 am Post subject:

Oh..I didn't notice this portion

"Plz explain why so?"

Have a look...

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/4.2.5.1.4?SHELF=&DT=20010710165542&CASE=

sathishgay · Posted: Fri Feb 11, 2005 6:46 am Post subject:

THANKS FOR INFORMATION.

THANKS AND REGARDS
SATHISH
_________________
OK

Catherine · Posted: Mon Mar 19, 2007 1:02 am Post subject: Isolation levels

Hi all,

Can you help me demarcate between Isolation level and a lock?
Are Isolation levels Row locks?

Thanks

kolusu · Posted: Mon Mar 19, 2007 5:15 am Post subject:

Catherine,

Did you look up the manual which hari_uss listed in this topic? 3rd post from the top?

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Catherine · Posted: Mon Mar 19, 2007 7:00 am Post subject: Isolation levels

Kolusu,

If i aquire a Page lock or a row lock, My table as well as the table space gets locked. I doubt is like Why should i lock the entire table space when i access only a row or a page. Is that necessary?

Thanks
Catherine

kolusu · Posted: Mon Mar 19, 2007 8:04 am Post subject:

Catherine · Posted: Wed Mar 21, 2007 4:50 am Post subject:

Kolusu,

Can you plz refer this link and explain me?

http://www.dbazine.com/db2/db2-mfarticles/mullins4

In the above article under table locks and tablespace locks i find that
even if i give LOCKSIZE = PAGE/ROW my table or table space gets locked

Thanks,
Catherine[/url][/list][/list]

kolusu · Posted: Wed Mar 21, 2007 7:11 am Post subject:

Catherine,

I cannot get to the site as it is under construction

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Yes, you get tablespace locks, even if you specify LOCKSIZE=PAGE, but the flavour of the lock is different:
In this case the Page gets an S-Lock, but the Tablespace an IS-Lock (i.e. Intend Share). This construction shall simplify the search of another user of a tablespace by the system. IS tablespace locks are compatibel to X locks on pages of this tablespace by another user, but they are NOT compatibel to X locks on the tablespace level by other users.

To get this picture with all aspects, you should read an old DB2 redbook where locking is explained in detail. It is a pain to read all this detail stuff, but questions like yours are answered.

redards
Christian

Catherine · Posted: Fri Mar 23, 2007 3:17 am Post subject: Isolation levels

Christian,

Suppose i have defined LOCKSIZE=PAGE, I query the table with a SELECT statement.

When I try to retrieve a row
1) The page containing the Row is put in "Share" Lock mode
2) The table space is put in "Intent share" mode. This means all the pages excluding the page mentioned in point 1 is put in "Intent Share" mode
Im i right?

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Hello Catherine,

no, the tablespace locks are associated with the tablespace itself. I see it as an hirarchy:
First the system looks, whether it can access the tablespace, and then, if it can access the page.

Since every lock consumes core memory of one the DB2 address spaces, the number of locks shure shouldn't be that large.

regards
Christian

vini_srcna · Beginner Joined: 17 Jul 2005 Posts: 9 Topics: 0

and if no of locks exceeds the limit specified in the Tablespace definition, then an escalation happens and this is called as LOCK escalation.

say you have defined a tablespace with LOCKSIZE ROW & LOCMAX 7000.
when the no of locks exceed 7000 on the table space and escalation happens to a table level lock from row level lock. As Christian told, each and every lock requires a specifiec amount of memory irrespective of the LOCKSIZE.

This would be time where you get the timeouts.

Isolation level says how long to hold the lock.
Type of lock says what to hold..(ROW,TABLE,TABLESPACE...)

Dbazine has a very good info on this.
Say you have a Partitioned table and all the programs access it. Also say you have some peculiar program running in a window during which no one is supposed to access the data meanwhile. But the Tablesize is defined with LOCKSIZE ANY (ROW).
In that case you can use the LOCK statement to get the lock explicitly.
In the program at the begining you can say
LOCK TABLE tablename IN x MODE; (X= SHARE or EXCLUSIVE)
SHARE allows others to just read(UR) while you do something.
EXCLUSIVE drains the table from other users.
Locking is a huge concept again.
_________________
Thanks,
Vinay Kumar,
IBM Certified DB2 UDB Database Administrator for Z/OS.
IBM Certified Application Developer for DB2 V8 Family.