RE: Row locks in DB2/400 UDB

["Walden H. Leverich III" <WaldenL@xxxxxxxxxxxxxxx>]

>How much time is there between the access and the update? the three lines
of
>code should be together. "get the number", "add 1 to the number", "update
>the record".

Three lines ontop of eachother is still too much space. I used to think I
could run that way, retrieve (no lock) , add, update and all was good. Then
I ran two of the same process in batch on a B70 (it was fast at the time)
and BAM! The two processes walked all over eachother. The initial read
_must_ lock the row or you'r looking for trouble.

Now, since this is SQL, does DB2/400 support the fetch and update concept? I
know in SQLServer[1] I could write a line that said:

Update lastused set LastUsed = LastUsed + 1, @Number = LastUsed

And @Number would be a variable in my code that had the value from the
database. The result would be that the row was locked, fetched, updated and
release in that order so I couldn't have two people get the same value. I
don't know if DB2/400 has a similar concept.

As for Commitment Control, you DO NOT want to use this to lock the record.
Remember, the "next number" row would remain locked until you issued a
commit/rollback. So all other processes that wanted a next number would have
to wait for the first process to issue the commit/rollback. That would
create a huge bottleneck. In cases where I'm using commitment control for
the "real" updates, I purposely avoid commitment control for the next number
file. 

One idea would be to create a user defined function in SQL that called a RPG
program that did the read(with lock), increment and update. You could then
use the UDF in your SQL statement. Something like:

Insert into file (ID, Name, City) values(GetNextNumber(), 'Walden', 'New
York')

Or even:

Select GetNextNumber() from dummy

-Walden

PS. I agree with you Booth, I've never understood the requirement to avoid
wasted numbers. It just makes life harder.



------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 

-----Original Message-----
From: Booth Martin [mailto:Booth@xxxxxxxxxxxx] 
Sent: Friday, August 08, 2003 10:11 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Row locks in DB2/400 UDB


How much time is there between the access and the update? the three lines of
code should be together. "get the number", "add 1 to the number", "update
the record".
 
Why not write one program that does that, and let the other programs call
that program for the next number? 
 
Sometimes people like to get the next number but not do the actual update
till the user has finished the process just in case the user bails out. The
idea being so that there's no wasted numbers. That always struck me as an
odd decision because numbers are free.
 
 
 
---------------------------------------------------------
Booth Martin http://www.MartinVT.com
Booth@xxxxxxxxxxxx
---------------------------------------------------------
 
-------Original Message-------
 
From: Midrange Systems Technical Discussion
Date: Friday, August 08, 2003 9:02:07 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Row locks in DB2/400 UDB
 
All
 
Is there a way to lock a row in DB2/400 UDB SQL without using commitment
control?
 
We have a table that holds the 'next sequence number'. Whenever several
processes access the table, we end up with a scenario like:
 
program A accesses the table and grabs
the next counter number (counter number = 50)
 
program B accesses the table and grabs
the next counter number (counter number = 50)
 
program B adds one to the counter
number, and updates the table (counter number = 51)
 
program A adds one to the counter
number, and updates the table (counter number = 51)
 
Now, there are two transactions with an identical sequence number.
 
We're doing a simple select to fetch the sequence number, and we are not
using commitment control.
 
Any suggestions?
 
Thanks
 
-Doc

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.