sql - 如何获取序列中的下一个数字

Question

我有一个这样的表:

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
+----+-----------+------+-------+--+

如何在 Model 3 的 Stomach 之后插入带有下一个序列的另一条记录。因此，新表应如下所示:

+----+-----------+------+-------+--+
| 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 之后给出下一个数字。另外，寻找并发安全的东西。

Answer 1

如果您不维护计数器表，有两种选择。在事务中，首先使用下表提示之一选择 MAX(seq_id):

1. WITH(TABLOCKX, HOLDLOCK)
2. WITH(ROWLOCK、XLOCK、HOLDLOCK)

TABLOCKX + HOLDLOCK 有点矫枉过正。它会阻塞常规的 select 语句，即使事务很小，也可以认为这些语句很重。

ROWLOCK、XLOCK、HOLDLOCK 表提示可能是一个更好的主意(但是:请进一步阅读带有计数器表的替代方案)。优点是它不会阻止常规 select 语句，即当 select 语句未出现在 SERIALIZABLE 事务中时，或者当 select 语句不提供相同的表提示时。使用 ROWLOCK、XLOCK、HOLDLOCK 仍会阻止插入语句。

当然，您需要确保程序的其他部分没有这些表提示(或在 SERIALIZABLE 事务之外)选择 MAX(seq_id)，然后使用此值插入行。

请注意，根据以这种方式锁定的行数，SQL Server 可能会将锁定升级为表锁定。了解有关锁升级的更多信息 here .

使用 WITH(ROWLOCK, XLOCK, HOLDLOCK) 的插入过程如下所示:

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

<小时/>

另一种可能更好的想法是拥有一个计数器表，并在计数器表上提供这些表提示。该表如下所示:

CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq_id INT);

然后您可以按如下方式更改插入过程:

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表，从而阻塞选择语句。

您可以测试所有这些并亲自查看效果，方法是在从 counter_seq 选择序列后放置一个 WAITFOR DELAY '00:01:00'，然后摆弄第二个 SSMS 选项卡中的表。

<小时/>

PS1:使用ROW_NUMBER() OVER (PARTITION BY model ORDER BY ID)不是一个好方法。如果行被删除/添加，或者 ID 发生变化，序列就会改变(考虑发票 ID 永远不应该改变)。另外，就性能而言，在检索单行时必须确定所有先前行的行号也是一个坏主意。

PS2:当 SQL Server 已经通过隔离级别或细粒度表提示提供锁定时，我永远不会使用外部资源来提供锁定。