SQL Server 序列线程安全吗? [英] Are SQL Server sequences thread safe?

问题描述

标题太宽泛，但我找不到更具体的，请随时更换更好的.

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.

消费者应用程序有一个规则，它不处理id(身份列值)小于最后自己处理的id的行

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.

这里是我的insert sp长什么样子，是生产者常用的；

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

所以我在想，生产者2和生产者3调用存储过程的时候，首先拿到的是序列值.然后，当涉及到插入时，不知何故，生产者 3 的插入发生得比生产者 2 快.然后消费者在处理较小的 id 之前处理较大的 id，因此永远不会处理 id 26.

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?

推荐答案

这称为竞争条件.从这个意义上说，序列(以及在持久化之前检索值的任何其他方法)是不安全的.

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

您希望将序列的下一个值作为 ID 列的默认约束:

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]

有了这个，新的序列值将在记录被持久化时生成.这本质上与 IDENTITY() 子句相同.

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

这篇关于SQL Server 序列线程安全吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！