问题描述
我有一个序列，我想从中自动检索多个连续值.
I have a sequence and I want to atomically retrieve several sequential values from it.
据我所知，序列不支持事务.
As far as I know, sequences do not support transactions.
标准下一个值 语法 帮不上忙，而在其他数据库中可以这样做.
Standard NEXT VALUE FOR syntax can not help, while in other databases it can be done like that.
我找到了 sp_sequence_get_range 函数，但我不确定它是否是原子的.测试此类案例非常棘手.
I found sp_sequence_get_range function, but I'm not sure if it is atomic. Testing such cases is quite tricky.
有可能吗?
推荐答案
简而言之，是的，序列具有您正在寻找的属性.
In short, yes, sequences have the property you're looking for.
现在稍微解释一下原因.在它们的基础上，序列只是系统表中的一行，其中(除其他外)有一列表示序列中的当前值.通常，当您调用 NEXT VALUE FOR 时，SQL Server 获取当前值，将该值增加 1，最后返回获取的值.当您使用 sp_sequence_get_range 时，它会做同样的事情，只是它会根据您要求的范围的大小增加值(例如，如果您使用 @range_size = 10 调用它)，它将值增加 10).
Now a little bit as to why. At their base, a sequence is just a row in a system table that has (among other things) a column that represents the current value in the sequence. Normally, when you call NEXT VALUE FOR, SQL Server fetches that current value, increments that value by 1, and finally returns the fetched value. When you use the sp_sequence_get_range, it does the same thing except it increments the value by the size of the range you asked for (e.g. if you called it with @range_size = 10, it increments the value by 10).
我很好奇的一件事是，包含 sp_sequence_get_range 过程调用的事务的回滚是否也回滚了该范围的分配.我很高兴地报告它没有.因此，您不必担心对该过程的两次调用获得相同的值.
One thing that I was curious about was whether or not a rollback of a transaction that contained the sp_sequence_get_range procedure call also rolled back the allocation of that range. I'm happy to report that it does not. So, you needn't worry about two calls to that procedure getting the same value.
最后，我提醒您注意无间隙序列的谬误.如果您获得一个范围并且该线程因任何原因而终止(例如，您的应用程序终止、SQL Server 预期或意外关闭)，则该序列将永远丢失.也就是说，没有将未使用"的范围放回队列中，以便下一次对范围的调用获得该范围.
Lastly, I caution you against the fallacy of a gapless sequence. If you get a range and that thread dies for whatever reason (e.g. your application terminates, the SQL Server shuts down expectedly or unexpectedly), that sequence is lost forever. That is, there's no putting an "unused" range back in the queue so that the next call for a range gets that range.
这篇关于从 SEQUENCE 原子检索顺序范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！