Re: High level record lock

You should be using a UDF to retrieve the "next number"..

Your UDF could ensure that commitment control is use to lock the record.

Honestly, I'd probably go with an external UDF in RPG as the "read w/lock,
then update" is more straight forward.

If you want to do it in SQL, you'd need to declare a cursor, fetch from it
and UPDATE WHERE CURRENT OF CURSOR...

Charles


On Wed, Sep 26, 2018 at 10:20 AM T. Adair <tadair@xxxxxxxxxxxxxxxx> wrote:

Is there a way to 'lock' a record so that nothing can even read it until
I release it?

We have a control record for next-available-group-number. We read the
record to get the next number, and then increment the number and update
the control record. This works fine until we hit the situation where
two jobs read the control record virtually simultaneously, and the
faster job burns through the next 6 available numbers before the slower
job does its first update, even though this happens in a tiny fraction
of a second. It's literally a million-to-one chance, but it just
happened last week.

Once a job reads the control record, I need to prevent any further
access of the record (including reads) until the first job releases it.

I've thought about trying to use a single SQL update statement but there
are two problems with that:
1. The 'number' is in an alpha field that I have to substring to get,
and to update.
2. The 'number' is 5 digits and will have to eventually wrap around
from 99999 back to 1.

I can't lock the file.

We're on 7.2.

Any thoughts?

Thanks.

~TA~

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD