Hi everyone.

I have a function that may end up being called from a number of transformations at the same time, and within the function there's some Embedded SQL to first check if a local table has an entry, and then adds the entry if it doesn't exist.

To prevent a race condition where the function is called by two transformations and they both end up attempting to insert the same value, I'm looking to use the table hint "WITH TABLOCK" on the insert, but this seems to be failing the syntax checks within vscode.

Are table hints supported with embedded sql?

If not, is there a way to prevent the scenario from unfolding? The code I'm working with for the function is:

Class Example.Functions Extends Ens.Rule.FunctionSet
{

ClassMethod VisitIDShortener(inVisitID As %String, SourceSystem As %String) As %String
{
    //Firstly,  check to see if the table we're working with exists | This should only be an issue on first deployment to a new environment
    Set tSC = $SYSTEM.SQL.Schema.TableExists("Example.VisitIDLookup")
    If tSC'=1{
        //Buid table if it doesn't exist
        &SQL(
            CREATE TABLE Example.VisitIDLookup
            (SourceSystem varchar(10), SourceVisitID varchar(250), TargetVisitID varchar(15))
        )
    }
    //Secondly, check to see if the Global we're using for our increment exist, and create if it doesn't exist
    Set GBLCHK = $DATA(^$GLOBAL("^EMPVisitID"))
	If GBLCHK = 0{
		Set ^EMPVisitID = 0
		}

    //Check table for entry
    &SQL(
        Select TargetVisitID into :pTargetID
        FROM Example.VisitIDLookup
        Where SourceVisitID = :inVisitID And SourceSystem = :SourceSystem
    )
    //If SQLCODE is 0, we have an entry and we wnat to simply return the result
    If SQLCODE=0 {
        Set outVisitID = pTargetID
    }
    Else{
        //Increment the Global
        Do $INCREMENT(^EMPVisitID)
        
        //Use incremented value for output (with a prefix)
        Set outVisitID = "TIEGEN"_^EMPVisitID
        //Add new entry to table
        &SQL(
            Insert INTO Example.VisitIDLookup
            Set SourceSystem=:SourceSystem, SourceVisitID = :inVisitID, TargetVisitID = :outVisitID
        )
    }

    Quit outVisitID
}

}