Concurrent access to database - preventing two users from obtaining the same value

Question

I have a table with sequential numbers (think invoice numbers or student IDs).

At some point, the user needs to request the previous number (in order to calculate the next number). Once the user knows the current number, they need to generate the next number and add it to the table.

My worry is that two users will be able to erroneously generate two identical numbers due to concurrent access.

I've heard of stored procedures, and I know that that might be one solution. Is there a best-practice here, to avoid concurrency issues?

Edit: Here's what I have so far:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetNextOrderNumber]
AS
BEGIN
    BEGIN TRAN

DECLARE @recentYear INT
DECLARE @recentMonth INT
DECLARE @recentSequenceNum INT

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- get the most recent numbers
SELECT @recentYear = Year, @recentMonth = Month, @recentSequenceNum = OrderSequenceNumber
FROM dbo.OrderNumbers 
WITH (XLOCK)
WHERE Id = (SELECT MAX(Id) FROM dbo.OrderNumbers)

    // increment the numbers
IF (YEAR(getDate()) > IsNull(@recentYear,0))
    BEGIN
        SET @recentYear = YEAR(getDate());
        SET @recentMonth = MONTH(getDate());
        SET @recentSequenceNum = 0;
    END
ELSE
    BEGIN
        IF (MONTH(getDate()) > IsNull(@recentMonth,0))
            BEGIN
                SET @recentMonth = MONTH(getDate());
                SET @recentSequenceNum = 0;
            END
        ELSE
            SET @recentSequenceNum = @recentSequenceNum + 1;
    END 

-- insert the new numbers as a new record   
INSERT INTO dbo.OrderNumbers(Year, Month, OrderSequenceNumber)
VALUES (@recentYear, @recentMonth, @recentSequenceNum)

COMMIT TRAN
END

This seems to work, and gives me the values I want. So far, I have not yet added any locking to prevent concurrent access.

Edit 2: Added WITH(XLOCK) to lock the table until the transaction completes. I'm not going for performance here. As long as I don't get duplicate entries added, and deadlocks don't happen, this should work.

Answer 1

you know that SQL Server does that for you, right? You can you a identity column if you need sequential number or a calculated column if you need to calculate the new value based on another one.

But, if that doesn't solve your problem, or if you need to do a complicated calculation to generate your new number that cant be done in a simple insert, I suggest writing a stored procedure that locks the table, gets the last value, generate the new one, inserts it and then unlocks the table.

Read this link to learn about transaction isolation level

just make sure to keep the "locking" period as small as possible

Answer 2

Here is a sample Counter implementation. Basic idea is to use insert trigger to update numbers of lets say, invoices. First step is to create a table to hold a value of last assigned number:

create table [Counter]
(
   LastNumber int
)

and initialize it with single row:

insert into [Counter] values(0)

Sample invoice table:

create table invoices 
(
  InvoiceID int identity primary key, 
  Number varchar(8), 
  InvoiceDate datetime
)

Stored procedure LastNumber first updates Counter row and then retrieves the value. As the value is an int, it is simply returned as procedure return value; otherwise an output column would be required. Procedure takes as a parameter number of next numbers to fetch; output is last number.

create proc LastNumber (@NumberOfNextNumbers int = 1)
as
begin
   declare @LastNumber int

   update [Counter] 
      set LastNumber = LastNumber + @NumberOfNextNumbers -- Holds update lock
   select @LastNumber = LastNumber 
     from [Counter]
   return @LastNumber 
end

Trigger on Invoice table gets number of simultaneously inserted invoices, asks next n numbers from stored procedure and updates invoices with that numbers.

create trigger InvoiceNumberTrigger on Invoices
after insert
as
   set NoCount ON

   declare @InvoiceID int
   declare @LastNumber int
   declare @RowsAffected int

   select @RowsAffected = count(*)
     from Inserted
   exec @LastNumber = dbo.LastNumber @RowsAffected

   update Invoices
    -- Year/month parts of number are missing
      set Number = right ('000' + ltrim(str(@LastNumber - rowNumber)), 3)
     from Invoices
        inner join
        ( select InvoiceID, 
                 row_number () over (order by InvoiceID desc) - 1 rowNumber
            from Inserted
        ) insertedRows
          on Invoices.InvoiceID = InsertedRows.InvoiceID

In case of a rollback there will be no gaps left. Counter table could be easily expanded with keys for different sequences; in this case, a date valid-until might be nice because you might prepare this table beforehand and let LastNumber worry about selecting the counter for current year/month.

Example of usage:

insert into invoices (invoiceDate) values(GETDATE())

As number column's value is autogenerated, one should re-read it. I believe that EF has provisions for that.