Re: Row locks in DB2/400 UDB

["Jim Franz" <franz400@xxxxxxxxxxxx>]

have been following this thread - are we saying SQL cannot do
such a lock and update? if so an example would be great..
jim
----- Original Message -----
From: "Booth Martin" <Booth@xxxxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Friday, August 08, 2003 11:09 AM
Subject: RE: Row locks in DB2/400 UDB


> Chain to the record. That will lock it until its updated. No need for
> special locking code.
>
>
>
> ---------------------------------------------------------
> Booth Martin http://www.MartinVT.com
> Booth@xxxxxxxxxxxx
> ---------------------------------------------------------
>
> -------Original Message-------
>
> From: Midrange Systems Technical Discussion
> Date: Friday, August 08, 2003 9:53:11 AM
> To: 'Midrange Systems Technical Discussion'
> 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".
>
> 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.
>
>