1

I need to read and increment a value atomically in SQL Server 2008, using c#.

For example, I have to insert items of a "lot", for this i need the number of the last lot and be sure, that no one else get this number.

So i have a table only with the last lot number, and find a way to get and update the number in only one step.

How can i do this?

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
Keetah
  • 261
  • 1
  • 5
  • 15
  • 2
    Is it essential that the lot numbers be sequential? Why not just use an `identity`? – Martin Smith Sep 13 '11 at 21:02
  • Maybe the quirky update methodology here http://stackoverflow.com/questions/7184410/sql-server-update-mytable-set-col1-value-col2-col1 or here http://stackoverflow.com/questions/6653962/ms-sql-query-update-record-retrieved-via-stored-procedure could help. – Aaron Bertrand Sep 13 '11 at 21:05

2 Answers2

2

Is it essential that the lot numbers be sequential? Why not just use an identity? This is better in terms of concurrency as otherwise you need to block concurrent insert attempts in case they get rolled back and would leave a gap in the sequence.

If it absolutely is a requirement however you can do

CREATE TABLE dbo.Sequence 
  (
     OneRow CHAR(1) DEFAULT('X') PRIMARY KEY CHECK(OneRow = 'X'),
     val    INT
  )

Insert a row with an initial seed. 

INSERT INTO dbo.Sequence 
            (val)
VALUES     (1)

Then to allocate a range of sufficient size for your insert (call it in the same transaction obviously)

CREATE PROC dbo.GetSequence
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequence 
SET @val = val = (val + @n);
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Just please be cautious of this "quirky update" syntax. My comment in the first linked question above leads to more info... – Aaron Bertrand Sep 13 '11 at 21:06
  • Documented where? And to last through to what version? I'm not saying it's a bad solution, it just doesn't pass my future-proofing test. – Aaron Bertrand Sep 13 '11 at 21:09
  • 1
    @Aaron `@variable = column = expression` is specifically allowed in the [`UPDATE Grammar`](http://msdn.microsoft.com/en-us/library/ms177523.aspx) and in the comments it says *SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.* – Martin Smith Sep 13 '11 at 21:16
  • 1
    I wonder if that comment has been added in recent years. While I admit I haven't reviewed the docs in ages, I know this was something to stay away from for a long time. And it is important to note that issues can still occur if you're trying to do this for multiple rows... – Aaron Bertrand Sep 13 '11 at 21:43
  • 1
    @Aaron - Yep agreed that the use for running totals has lots of issues. I've added a check constraint to the table definition to ensure it can't contain more than one row. – Martin Smith Sep 13 '11 at 21:52
0

The easiest way would be to just alter the table so that the lot number is an identity field and will auto-increment itself. Then you don't have to worry about incrementing in code.

PaulStock
  • 11,053
  • 9
  • 49
  • 52