Are SQL Server sequences thread safe?

Question

Title is too broad but I couldn't find a more specific one, please feel free to change with better one.

I have a table which is working with sequences instead identity. I have three producer applications which are concurrently insert into table, and a consumer application select from table whose status are not processed and then process them and finally update rows as processed.

Consumer application has a rule that it does not process the row whose id (identity column value) is smaller than the id which lastly processed by itself

The problem is, although I have never presumed to happen, my consumer application is falling into this rule while it is running. (Rule developed for other purposes). To visualize;

Each application sends new data to the database and under normal circumstances, each one should be selected by consumer and processed (polling), however sometimes (inside a working period) I always have an unprocessed data in my table.

Here what my insert sp looks like which is commonly used by producers;

CREATE PROCEDURE spInsData
    @Data VARCHAR(MAX)
AS
BEGIN
    SET @Id = NEXT VALUE FOR dbo.sequenceId

    INSERT INTO dbo.MyTable(Id, Data, Status)
    VALUES (@Id, @Data, 0)
END

So I am thinking of that when Producer 2 and Producer 3 calls the stored procedure, they first get the sequence values. Then, when it comes to insert, somehow Producer 3 insertion is occured faster than the Producer 2. Then the consumer processes the the bigger id before the smaller one, so id 26 is never processed.

I hope I am clear about the problem. Could it be the problem as I explain or could it be something else? If it is about sequence, can I lock the whole process - get sequence and insert - for each consumer?

Answer 1

This is called race condition. In this sense sequences (and any other method which retrieves the value before persisting it) is not safe.

You want to have the next value of the sequence as the default constraint for your ID column:

[id] INT NOT NULL CONSTRAINT [DF_MyTable_ID] DEFAULT NEXT VALUE FOR [dbo].[MySequence]

With this the new sequence value will be generated when the record is being persisted. This is essentially the same as an IDENTITY() clause.