Using HOLD(XXX,1) to Lock a Record

[Johan de Klerk]

Hi,

Sorry for the long post.

I am having a problem in my app and wonder how everyone else is handling it.

This is what i have now:
In my stock program branches can request stock and a field (StkRequested) in the stock record is updated with the quantity.
The main store can also book in stock and the Quantity (InStock) is updated.
Stock can also be booked out to the branches and the "InStock" is updated as well as the "StkRequested".

What I have seen is that when multiple branches are requesting stock and the store is booking stock in and out at the same time most of the time the "InStock" & "StkRequested" quantities goes completely wrong.

When the stock is booked in/requested I think it still uses the current value in memory and the addition and deduction is done on the currently loaded values.

My thinking is the record is loaded when the form opens and with multiple forms open by all the users I will have to use some sort of Record Locking (HOLD)

At this stage my code looks like this when I update the record:
StoreStock:Guid = BranchStoItmReq:StoreStockGuid
IF ACCESS:StoreStock.FETCH(StoreStock:Guid_Key) = LEVEL:BENIGN
    StoreStock:QNTYForBookout -= p_web.GSV('OrgQNTYRequested')
    StoreStock:QNTYForBookout += BranchStoItmReq:QNTYRequested
    ACCESS:StoreStock.UPDATE()
END

How would I implement Record Locking in my code above.

Regards

Johan de Klerk

[Bruce]

>> How would I implement Record Locking in my code above.

It's ABC, so you can lock the record exactly as you would in any ABC program.

Cheers
Bruce

[Johan de Klerk]

Hi Bruce,

Thanks for the reply.

I have never had the need to use HOLD so this is new to me and I am trying to get my head around it.
I have read section in the manual on HOLD.

This is how I think I need to do it but any advice/correction would be appreciated.
LOOP                !Loop to avoid "deadly embrace"
    HOLD(StoreStock,1)  !Arm Hold on view, primary record only,try for 1 second
    StoreStock:Guid = BranchStoItmReq:StoreStockGuid
    ACCESS:StoreStock.FETCH(StoreStock:Guid_Key) !Get and hold the record
    IF ERRORCODE() = 43 !If someone else has it
        CYCLE            ! try again
    ELSIF ACCESS:StoreStock.FETCH(StoreStock:Guid_Key) = LEVEL:BENIGN
        StoreStock:QNTYForBookout -= p_web.GSV('OrgQNTYRequested')
        StoreStock:QNTYForBookout += BranchStoItmReq:QNTYRequested
        ACCESS:StoreStock.UPDATE()
        BREAK            !Break if not held
    END
END
RELEASE(StoreStock)

Regards

Johan de Klerk

[Bruce]

I've never used HOLD myself - it seems a very dangerous thing to do.
So I don't really have any advice here - perhaps others can chip in.

cheers
Bruce

[bshields]

Hi Johan,

Like Bruce I avoid LOCK.

If your backend is SQL get the SQL engine to do it:

StoreStock{PROP:SQL} = 'UPDATE StoreStock SET QNTYForBookout = QNTYForBookout - '&p_web.GSV('OrgQNTYRequested')+BranchStoItmReq:QNTYRequested&' WHERE GUID='''&CLIP(BranchStoItmReq:StoreStockGuid)&''''

This still requires "BranchStoItmReq:QNTYRequested" to be in memory, you can also place the loading of it into SQL, which essentially makes it an atomic transaction.

Regards
Bill

[Johan de Klerk]

Hi Bill,

Thanks for your reply.

Due to the nature of the problem I have it seems my only solution is to Hold the record until it is doe and to be able to detect if it is being Held before processing the record.

These are all TPS files used by my NetTalk Web app.

Regards

Johan de Klerk

[RichCPT]

Maybe LOGOUT / COMMIT is preferable over LOCK and HOLD?

LOGOUT does tend to create program hangs, at least when I tried it with a dozen people creating records in topspeed files at exactly the same time.  But maybe LOCK and HOLD create hangs too?

Before issuing the LOGOUT command, I have the program delay a random amount of time.  So, if an instructor in a classroom tells the students to hit the "Create" button at the same time there is a lower chance of workstations hanging. 

I know the documentation for LOCK and HOLD talks about "deadly embrace".  That applies to LOGOUT as well, so I designed a common routine that I call to issue the LOGOUT,  I always list a particular Control file first and then I always list the Parent and Child tables in a specific order.  But, listing the files always in the same sequence and always including a common table, first, was not enough to avoid program hangs.  Hence I put in the random delay.

Using the common routine to issue the LOGOUT command allows me to display a "Retry, Cancel" window if the program is not able to get the LOGOUT successfully within a timeout period.  BTW, don't use MESSAGE as the Retry/Cancel prompt, as that blocks other parts of the program from operating.

P.S.  I forgot to mention I was using the TopSpeed driver underneath the IP Driver.  So, it is possible the hung workstations had more to do with the IP Driver driver not working very well with LOCK / HOLD and-or LOGOUT.