Row locking within stored procedure

Question

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table:

declare @val as int
SELECT @val = LongValue
from tblSystem
WHERE [Key] = @Key;

SET @val = @val + 1;

UPDATE tblSystem
set LongValue = @val
where [Key] = @Key

select @val;

Will running this within a Stored Procedure avoid any possible concurrent reads and avoid having a caller receive the same number? Should I use a transaction instead?

Answer 1

Any time you want to access a value more then once and you need to be sure that that value did not change in the mean time, you need to include the two accesses in a single transaction. But even more, you also need to make sure that appropriate locks are held.

If you are doing a SELECT followed by an UPDATE in a separate statement, you need to either switch the transaction isolation level to repeatable read or serializable or, even better, supply the WITH(UPDLOCK) hint on the first select. The UPDLOCK hint expresses the intend to later update this row. It will allow concurrent reads but prevent any other type of update request.

In SQL Server, other than in some competing RDBMSs, a stored procedure does not automatically provide transaction scope, so even with statements inside a procedure you still need to manage a surrounding transaction, either by always calling the procedure inside a transaction or by opening and committing a transaction inside the procedure.

All that being said however, in your case you don't even need multiple statements. Just combine the UPDATE with an OUTPUT clause:

UPDATE dbo.tblSystem
   SET LongValue += 1
OUTPUT INSERTED.LongValue
 WHERE [Key] = @Key;

Each statement in SQL Server is automatically executed within a "statement transaction" (no matter if called inside a procedure or not). Because you are using the UPDATE statement, SQL Server also takes care of the appropriate locking level. Therefore this is "threadsafe".

As an added bonus it should also execute faster.

Here is a SQL Fiddle to demonstrate this

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.tblSystem([Key] INT PRIMARY KEY CLUSTERED, LongValue BIGINT);

INSERT INTO dbo.tblSystem VALUES(42,0);

GO
CREATE PROCEDURE dbo.GetNextValue
  @Key INT
AS
BEGIN
  UPDATE dbo.tblSystem
     SET LongValue += 1
  OUTPUT INSERTED.LongValue
   WHERE [Key] = @Key;
END;

Query 1:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         1 |

Query 2:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         2 |

Query 3:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         3 |

Query 4:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         4 |