Re: High level record lock

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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