Locking needed for this query?

Question

This is my scalar function:

ALTER FUNCTION [dbo].[GetInstrumentID]
(
    @ParentDocumentID   int
)
RETURNS varchar(14)
AS
BEGIN

    Declare @InstrumentID   varchar(14) = ''
    Declare @FilingNumber   int = 0
    Select @InstrumentID = InstrumentID
        From Documents
            Where DocumentID = @ParentDocumentID

    Select Top 1 @FilingNumber = FilingNumber
        From Documents Where ParentDocumentID = @ParentDocumentID
            Order By FilingNumber desc

    Set @InstrumentID = Left(@InstrumentID,9) + Right(
        '000' + Cast((IsNull(@FilingNumber,1) + 1) as varchar(3))

    , 3) + '00'

    Return @InstrumentID
END

I am worried if 100s of users call this function, would it be possible that many of them receive the same return value? If yes, then how can I ensure that at a time only 1 user gets to execute this function and the rest of them wait?

Answer 1

You can use UPDLOCK hint. UPDLOCK hint tells the SQL Server engine "Don't allow any other read if this row to acquire an UPDATE(lock"U") because I will. UPDLOCK hold lock until the end of the statement or transaction.

Select statement in function body:

Select Top 1 @FilingNumber = FilingNumber
From Documents WITH(UPDLOCK)
Where ParentDocumentID = @ParentDocumentID
Order By FilingNumber desc

Answer 2

There is absolutely nothing in your function that would make it return a different value for two identical concurrent calls. If you need to prevent concurrency, you can use application locks. This is for SQL Server 2005, there might be something newer in SQL Server 2012.