MVSFORUMS.com

Harish-Ramakrishnan · Beginner Joined: 04 Apr 2006 Posts: 3 Topics: 2

WHATEVER I HAVE READ THE ONLY DIFFERNCE BETWEEN 'U' LOCK AND 'S'
LOCK IS THAT 'U' LOCK CAN BE PROMOTED TO AN 'X' LOCK IF THE ROW IS GOING TO BE UPDATED.

MY QUESTION IS THAT WHY NOT KEEP THE 'S' LOCK AS IT IS AND PROMOTE IT TO 'X' LOCK WHEN THE ROW IS TO BE UPDATED ,WHY DID ONE INTRODUCE THE CONCEPT OF 'U' LOCK IF BOTH 'S' LOCK AND 'U'
LOCK ARE THE SAME?

AND 'U'LOCK IS GIVEN TO A ROW IF THERE ARE CHANCES OF THAT ROW BEING UPDATED.HOW DOES ONE KNOW THE POSSIBILITY OF UPDATION OF A ROW ? AND IF THERE ARE SUCH POSSIBILITIES AT ALL THEN THE ROW SHLD BE LOCKED WITH 'X' LOCK RATHER THAN 'U' LOCK?

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Hi Harish-Ramakrishnan,
U lock & S lock are having following differences also.
All these locking mechanisms are to ensure data integrity and also concorrency.

The main difference which you have not listed as the difference between S & U lock is the lock owner of 'S' lock can read the data as well as other concurrent activities can also read the data.
Whereas the lock owner of 'U' lock can not read the data but other concurrent activities can read the data.

Next question: How does one know the possibilty of UPDATION of a row?
If you think of the options like SELECT FOR UPDATE, UPDATE,INSERT & DELETE then it clearly conveys the DBMS that the program is going to modify the database.

Why not 'X' lock instead of 'U' lock in the beginning itself?
Well. the problem with 'X' lock is if a program locked a particular table then it will not release the lock unless the program is finished or committed. Only the current program can read & update table. Meanwhile if any other program needs just to read the data then they have to wait. Which means if one program locked a table with 'X' lock then other programs can not read the data whereas if the first program obtained 'U' lock instead of 'X' lock, the other programs can read the data from the locked table until 'U' lock is promoted to 'X' lock. This means an improved concurrency.

So if you think in terms of concurreny then you will get answer to all your questions.

kolusu · Posted: Mon May 01, 2006 7:21 am Post subject:

Harish-Ramakrishnan,

Please don't Shout. Posting in all CAPS is like shouting.

Thanks

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

Harish-Ramakrishnan · Beginner Joined: 04 Apr 2006 Posts: 3 Topics: 2

Hi Kolusu,

Sorry for putting across all the sentences in CAPS.

Thanks for the immediate response,friends.

Regards,
Harish.

srini_igsi · Posted: Fri May 12, 2006 3:13 am Post subject:

Astro,

If u dont mind, can you pl eloborate 'Whereas the lock owner of 'U' lock can not read the data but other concurrent activities can read the data'.
_________________
Thanks,
-Srini

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Hi Srini,
Suppose you issue a "SELECT ... From TABLEA... FOR UPDATE OF ..." in a program then the program will lock the table TABLEA with 'S' lock. In this case the program which issues this statement can read the other rows in the same table(locked by this program itself).

But if you issue statements like "UPDATE/INESRT/DELETE" then the program will lock the table TABLEA with 'U' lock. Here your intention is not to read the data & just to modify the table. That is the reason it was written like the program can not read the data.

Hope this helps you.

srini_igsi · Posted: Fri May 12, 2006 5:12 am Post subject:

Hi Astro,

Once the DB2 places the 'U' lock on a Table, can the other run units access the records of that table in Read mode as long as they will not issue Update?
_________________
Thanks,
-Srini

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Hi srini_igsi,
Just read my answer for "Why not 'X' lock instead of 'U' lock in the beginning itself?" in the previous post.

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

Well Astro,

your post about U-locks is wrong!!!

If you read via a "SELECT ... FOR UPDATE OF ...", you will get the U-lock on the page where the cursor currently points to. With this the page is protected against other Readers-for-update, but readers using a simple SELECT can read the content of the page.

If the reader-for-update issues an "UPDATE WHERE CURRENT OF cursorname", then the U-lock will be promoted to an X-lock and will stay until your next synchpoint (COMMIT or ROLLBACK). After this promotion of the lock the page will not be available for ANY request of other processes.

regards
Christian

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Hello Christian,
I have asked you to re-read the answer for "Why not 'X' lock instead of 'U' lock in the beginning itself?" Have you really read it before posting the reply?

The last line of my post says