title: Are SQL Server sequences thread safe?
body: <p>Title is too broad but I couldn't find a more specific one, please feel free to change with better one.</p>
  
  <p>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.</p>
  
  <p>Consumer application has a rule that it does not process the row whose id (<em>identity column value</em>) is smaller than the id which lastly processed by itself</p>
  
  <p>The problem is,  although I have never presumed to happen, my consumer application is falling into this rule while it is running. (<em>Rule developed for other purposes</em>). To visualize;</p>
  
  <p><a href="https://i.stack.imgur.com/GvgvC.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GvgvC.jpg" alt="problem"></a></p>
  
  <p>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.</p>
  
  <p>Here what my insert sp looks like which is commonly used by producers;</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>