Re: Row locks in DB2/400 UDB

["Booth Martin" <Booth@xxxxxxxxxxxx>]

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