How to avoid Deadly embrace

[terryd]

The problem I am having is nothing to do with Nettalk but hopefully someone can point me to the best way to eliminate the issue.
In my Capture form I enter various fields and then save the form.
Because I want sequential job numbers with no numbers missing I then call this routine from the PostInsert Embed:-

GetNextJobNumber       ROUTINE
    Access:NextNumber.Open()
    Access:NextNumber.UseFile()
    NEXT:NumberType = 'JOBNO'
    Access:NextNumber.Fetch(NEXT:PK_NumberType)
    JobNo = CLIP(NEXT:Prefix) & FORMAT(NEXT:NextNumber,@N06)
    NEXT:NextNumber += 1
    Access:NextNumber.Update()
    Access:NextNumber.Close()
    EXIT
    
I have made it as small as I can to avoid clashes when multiple users are adding records but on Friday I had a deadly embrace. The table was locked presumably by 2 people adding a record and calling this procedure at the same millisecond.
What is the best way to allow one or another of the records to give way gracefully and then retry until successful.
[UPDATE]
Just looked up deadly Embrace in the Help. It deals with a view and a loop. How would I modify this to use in my situation?
ViewOrder  VIEW(Customer)  !Declare VIEW structure
           PROJECT(Cus:AcctNumber,Cus:Name)
           JOIN(Hea:AcctKey,Cus:AcctNumber)      !Join Header file
            PROJECT(Hea:OrderNumber)
            JOIN(Dtl:OrderKey,Hea:OrderNumber)   !Join Detail file
             PROJECT(Det:Item,Det:Quantity)
             JOIN(Pro:ItemKey,Dtl:Item)          !Join Product file
              PROJECT(Pro:Description,Pro:Price)
             END
            END
           END
          END
CODE
OPEN(Customer,22h)
OPEN(Header,22h)
OPEN(Detail,22h)
OPEN(Product,22h)
SET(Cus:AcctKey)
OPEN(ViewOrder)
 LOOP                 !Process records Loop
  LOOP                !Loop to avoid "deadly embrace"
  HOLD(ViewOrder,1)  !Arm Hold on view, primary record only,try for 1 second
   NEXT(ViewOrder)    !Get and hold the record
    IF ERRORCODE() = 43 !If someone else has it
     CYCLE            ! try again
    ELSE
     BREAK            !Break if not held
    END
  END
  IF ERRORCODE() THEN BREAK END !Check for end of file
  !Process the records
  RELEASE(ViewOrder)    !release Primary held record
 END
CLOSE(ViewOrder)

[kevin plummer]

Hi Terry,

are you using TPS or SQL?

edit in both cases it should be in a transaction frame but in SQL there are some other options in how you can construct the statement use stored procedures etc

[terryd]

Hi Kevin
MSSQL.
The table I am using has only one function to give me the next sequential number.

[kevin plummer]

Hi Terry,

I have a similar table in my app and I created a stored procedure as follows to avoid a similar problem.

HTH's


CREATE Procedure [dbo].[ds_NewTran]
@Type NvarChar(10), @L_Value Int OUTPUT

AS
 
Declare @SaveError Int
Declare @L_NewValue Int

--Begin Transaction


---CASE STATEMENT TO UPDATE RELEVANT FIELD-----------------------------

If @Type  = 'RecordID'
  Begin

            Update TRANS
         Set @L_Value = TRA_RecordID = TRA_RecordID + 1
          Where TRA_ID = 1
            --Commit Transaction
         Return @L_Value
  End

If @Type  = 'Tran'
  Begin
            Update TRANS
         Set @L_Value = TRA_TRAN = TRA_TRAN + 1
          Where TRA_ID = 1
            --Commit Transaction
         Return @L_Value
  End

[terryd]

Thanks Kevin.
I'll give it a try.