Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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?

share|improve this question

marked as duplicate by Max Vernon, Jon Seigel, RolandoMySQLDBA, Mike Walsh, Paul White Oct 28 '13 at 23:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Which SQL Server version are you using? –  Sebastian Meine Oct 28 '13 at 19:11
    
@SebastianMeine good question! I am not sure what production is running, will find out, but it definitely is > 2005. –  Jonas Stawski Oct 28 '13 at 19:15
    
@SebastianMeine it seems to be 2008, according to the Compatibility level of the backup I received –  Jonas Stawski Oct 28 '13 at 19:26
2  
See my question here for many other great answers: dba.stackexchange.com/questions/36603/… –  Max Vernon Oct 28 '13 at 19:29

1 Answer 1

up vote 2 down vote accepted

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 |
share|improve this answer
    
Sebastian, thanks for the detailed answer. The table has a trigger so this solution didn't quite work. I was getting The target table 'tblSystem' of the DML statement cannot have any enabled triggers --if the statement contains an OUTPUT clause without INTO clause. I had to change it to DECLARE @val as table(val int); --...; OUTPUT INSERTED.LongValue into @val; –  Jonas Stawski Oct 28 '13 at 19:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.