Updlock hint in stored procedure If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time?

CREATE PROCEDURE [dbo].[PayoutInvestment] 
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END;

    COMMIT TRANSACTION;
END


I know it works 100% if I do it with updlock hint. But would like to know if it is necessary to do it that way.

CREATE PROCEDURE [dbo].[PayoutInvestment] 
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments WITH (
                    ROWLOCK
                    ,UPDLOCK
                    )
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END

    COMMIT TRANSACTION;
END


I am using Read Committed isolation level.
Each execution of the stored procedure gets a separate copy of the variable, so that is not the risk you need to worry about. The value of the variable is 'private' to your transaction in the sense that no other concurrent transaction can see it or change its value (they have their own variable).

The concurrency problem you need to worry about is that the data in the database may change between statements, or even while a statement is executing.

Using an update locking hint on the investments table only prevents the touched row from being changed by a concurrent session before your transaction completes. It does not necessarily prevent other changes in the database (such as the user that owns that investment changing or the user's id being changed), nor does it prevent other transactions reading that value.

Rather than adding random locking hints that require an expert detailed analysis to assess (and are still easy to get wrong), it is usually simpler, and more sensible, to think about the level of isolation your transaction requires.

If you need the transaction to have persistent effects as if it were the only transaction running on the system (so eliminating worries about concurrent changes), you could run it at the serializable isolation level. If snapshot isolation (not RCSI!) is available to you, and write conflicts are expected to be rare, that could be a suitable isolation level to use as well. Another serialization-type solution would be to use application locks.

For example, using serializable isolation:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DECLARE @amount decimal(18,2);

UPDATE dbo.investments
SET date_stop = GETUTCDATE(),
    @amount = amount
FROM dbo.investments AS I
JOIN dbo.users AS U
    ON U.userid = I.userid
WHERE 
    I.idi = @idi;

IF @amount IS NOT NULL
BEGIN
    UPDATE dbo.users
    SET balance += @amount
    WHERE userid = @userid;
END;

COMMIT TRANSACTION;


You will need to add error handling to detect and resolve any deadlocks or write conflicts that may occur.

For more information, see my series of articles on SQL Server isolation levels:

SQL Server Isolation Levels : A Series