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?

Seamark

Yes, Sequence is designed for concurrent users. Each time it will give you next value. – Deviate

I believe you'll need to rethink your consumer - yes, there is potential for overtaking on the producer side (you might see some reduction if inline your local, i.e. INSERT INTO dbo.MyTable(..) VALUES(NEXT VALUE FOR ...). The consumer should perhaps consider tagging completed rows as "Processed" and then search for unprocessed rows, rather than being tightly coupled to the actual sequence id? – Bullwhip

@Bullwhip I will try to get the sequence inline. Consumer relies on getting unprocessed rows at the stored procedure level, after getting it in the application it checks for the id. – Seamark

I'd revisit your assumptions. Having code that actually depends on the numeric value assigned to auto-populated columns is inherently fragile, whether that population is via IDENTITY or SEQUENCE. Treat the values as opaque blobs that happen to fit in numeric columns, you'll be okay. Rely on particular properties of the values assigned and you'll have issues (as you have here). – Roadability

@Roadability Treat the values as opaque blobs that happen to fit in numeric columns, I couldn't get this part, can you guide me for a concrete example? – Seamark

They're a 8/16/32/64 bit blob of data. Inspecting their internals, or actually treating them as a number is the starting point of the kind of mistakes people make with them. Just treat them as a set of bits that SQL Server will guarantee to make unique and you won't have issues. – Roadability

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.

Referee