如何获取序列中的下一个数字 [英] How to get the next number in a sequence
问题描述
我有一张这样的桌子:
+----+-----------+------+-------+--+
| id | Part | Seq | Model | |
+----+-----------+------+-------+--+
| 1 | Head | 0 | 3 | |
| 2 | Neck | 1 | 3 | |
| 3 | Shoulders | 2 | 29 | |
| 4 | Shoulders | 2 | 3 | |
| 5 | Stomach | 5 | 3 | |
+----+-----------+------+-------+--+
如何在 Stomach 之后为 Model 3 插入下一个 seq 的另一条记录.所以这里是新表的样子:
How can I insert another record with the next seq after Stomach for Model 3. So here is what the new table suppose to look like:
+----+-----------+------+-------+--+
| id | Part | Seq | Model | |
+----+-----------+------+-------+--+
| 1 | Head | 0 | 3 | |
| 2 | Neck | 1 | 3 | |
| 3 | Shoulders | 2 | 29 | |
| 4 | Shoulders | 2 | 3 | |
| 5 | Stomach | 5 | 3 | |
| 6 | Groin | 6 | 3 | |
+----+-----------+------+-------+--+
有没有一种方法可以制作一个插入查询，该查询将仅在 Model 3 的最高 seq 之后给出下一个数字.另外，寻找并发安全的东西.
Is there a way to craft an insert query that will give the next number after the highest seq for Model 3 only. Also, looking for something that is concurrency safe.
推荐答案
如果不维护计数器表，有两种选择.在一个事务中，首先选择 MAX(seq_id) 和下表提示之一:
If you do not maintain a counter table, there are two options. Within a transaction, first select the MAX(seq_id) with one of the following table hints:
WITH(TABLOCKX, HOLDLOCK)WITH(ROWLOCK, XLOCK, HOLDLOCK)
TABLOCKX + HOLDLOCK 有点矫枉过正.它会阻止常规的 select 语句，即使事务很小，也可以将其视为重.
TABLOCKX + HOLDLOCK is a bit overkill. It blocks regular select statements, which can be considered heavy even though the transaction is small.
ROWLOCK, XLOCK, HOLDLOCK 表提示可能是一个更好的主意(但是:请进一步阅读带有计数器表的替代方案).优点是它不会阻塞常规的 select 语句，即当 select 语句没有出现在 SERIALIZABLE 事务中时，或者当 select 语句不提供相同的表提示时.使用 ROWLOCK, XLOCK, HOLDLOCK 仍然会阻塞插入语句.
A ROWLOCK, XLOCK, HOLDLOCK table hint is probably a better idea (but: read the alternative with a counter table further on). The advantage is that it does not block regular select statements, ie when the select statements don't appear in a SERIALIZABLE transaction, or when the select statements don't provide the same table hints. Using ROWLOCK, XLOCK, HOLDLOCK will still block insert statements.
当然，您需要确保没有这些表提示(或在 SERIALIZABLE 事务之外)，程序的任何其他部分都不会选择 MAX(seq_id) 然后使用此值插入行.
Of course you need to be sure that no other parts of your program select the MAX(seq_id) without these table hints (or outside a SERIALIZABLE transaction) and then use this value to insert rows.
请注意，根据以这种方式锁定的行数，SQL Server 可能会将锁定升级为表锁定.在此处阅读有关锁升级的更多信息.
Note that depending on the number of rows that are locked this way, it is possible that SQL Server will escalate the lock to a table lock. Read more about lock escalation here.
使用 WITH(ROWLOCK, XLOCK, HOLDLOCK) 的插入过程如下所示:
The insert procedure using WITH(ROWLOCK, XLOCK, HOLDLOCK) would look as follows:
DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Spine';
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @max_seq INT=(SELECT MAX(seq) FROM dbo.table_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE model=@target_model);
IF @max_seq IS NULL SET @max_seq=0;
INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@max_seq+1,@target_model);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
<小时>
另一种可能更好的主意是拥有一个 counter 表，并在计数器表上提供这些表提示.该表如下所示:
An alternative and probably a better idea is to have a counter table, and provide these table hints on the counter table. This table would look like the following:
CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq_id INT);
然后您将更改插入过程如下:
You would then change the insert procedure as follows:
DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Spine';
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @new_seq INT=(SELECT seq FROM dbo.counter_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE model=@target_model);
IF @new_seq IS NULL
BEGIN SET @new_seq=1; INSERT INTO dbo.counter_seq(model,seq)VALUES(@target_model,@new_seq); END
ELSE
BEGIN SET @new_seq+=1; UPDATE dbo.counter_seq SET seq=@new_seq WHERE model=@target_model; END
INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@new_seq,@target_model);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
优点是使用较少的行锁(即dbo.counter_seq中每个模型一个)，并且锁升级不能锁定整个dbo.table_seq表从而阻塞选择语句.
The advantage is that fewer row locks are used (ie one per model in dbo.counter_seq), and lock escalation cannot lock the whole dbo.table_seq table thus blocking select statements.
您可以测试所有这些并自己查看效果，方法是在从 counter_seq 中选择序列后放置一个 WAITFOR DELAY '00:01:00'，然后摆弄第二个 SSMS 选项卡中的表格.
You can test all this and see the effects yourself, by placing a WAITFOR DELAY '00:01:00' after selecting the sequence from counter_seq, and fiddling with the table(s) in a second SSMS tab.
PS1:使用 ROW_NUMBER() OVER (PARTITION BY model ORDER BY ID) 不是一个好方法.如果行被删除/添加，或 ID 更改，序列将更改(考虑永远不应更改的发票 ID).同样在性能方面，在检索单行时必须确定所有先前行的行号也是一个坏主意.
PS1: Using ROW_NUMBER() OVER (PARTITION BY model ORDER BY ID) is not a good way. If rows are deleted/added, or ID's changed the sequence would change (consider invoice id's that should never change). Also in terms of performance having to determine the row numbers of all previous rows when retrieving a single row is a bad idea.
PS2:当 SQL Server 已经通过隔离级别或细粒度表提示提供锁定时，我永远不会使用外部资源来提供锁定.
PS2: I would never use outside resources to provide locking, when SQL Server already provides locking through isolation levels or fine-grained table hints.
这篇关于如何获取序列中的下一个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
