Using HOLD(XXX,1) to Lock a Record

1

Hi,

Sorry for the long post.
I am having a problem in my NetTalk Web 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

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

2

I generally avoid LOCK and so do the templates. I would suggest WATCH() where the HOLD is done by the Driver on the PUT. It does a HOLD then REGET, checks if the record changed on disk since you WATCHed it and errors, else the PUT updates and releases. it is probably optimized for the driver. Something like this, ABC removed so get ErrorCode():

ACCESS:StoreStock.UseFile() !go GET/PUT work
LOOP Try=5 TO 1 BY -1         !Loop to avoid hang        
    WATCH(StoreStock)  !Must do before GET/NEXT
    StoreStock:Guid = BranchStoItmReq:StoreStockGuid
    GET(StoreStock,StoreStock:Guid_Key) !aka ACCESS:StoreStock.FETCH(StoreStock:Guid_Key) !Get and hold the record
    IF ERRORCODE() THEN
        Message('Unexpected GET Failed: ' & ERROR()) 
        Try=0 ; BREAK
    END
    StoreStock:QNTYForBookout -= p_web.GSV('OrgQNTYRequested')
    StoreStock:QNTYForBookout += BranchStoItmReq:QNTYRequested
    PUT(StoreStock) !aka ACCESS:StoreStock.UPDATE()
    IF ~ERRORCODE() THEN BREAK. 
    !Would see Error 89 if another user updated in this window, other errors possible
    IF Try=1 THEN
         message('Cannot update: ' & ERROR()) ; try=0 ; break
    END
END
IF Try=0 THEN DO FailedToUpdate.

I have HOLD used a lot in some software I maintain where it HOLD’s the record while the FORM is open. What I do is have a special Hold file, I never hold the real file. This file is actually an alias of the Hold1 file so I can use it for multiple files and also know who has it held.

Hold1_PRF100    FILE,DRIVER('TOPSPEED'),NAME('Hold1.TPS'),PRE(Hld1P100),CREATE !Hold Records using this file and not real file
ByFileID          KEY(Hld1P100:FileName,Hld1P100:RecordID),PRIMARY,NOCASE
record            RECORD
FileName            STRING(16) !File name to Hold
RecordID            STRING(16) !Unique ID fiels from the File e.g. PETRAN:AutoNo
Description         STRING(80)
UserNo              LONG !Super Security UserNo_
UserName            STRING(40)
PCName              STRING(32)
PCUser              STRING(32)
DateHeld            LONG
TimeHeld            LONG
                  END
                END
3

Hi Carl,

Thank you very much for your suggestion and detailed description.

So WATCH and HOLD only work on Legacy/Clarion PUT & GET Commands?
Not with ACCESS:StoreStock.FETCH(StoreStock:Guid_Key) and ACCESS:StoreStock.UPDATE()

Regards

Johan de Klerk

4

They work with Fetch and Update. Those methods use GET and PUT internally.

5

Hi Rick,

Thank you very much for the good news.

Regards

Johan de Klerk