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

+----+-----------+------+-------+--+
| 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     |  |
+----+-----------+------+-------+--+

如果您不保留计数器表,则有两个选项.在事务中,首先使用以下表格提示之一选择MAX(seq_id)：

> WITH(TABLOCKX,HOLDLOCK)
> WITH(ROCKLOCK,XLOCK,HOLDLOCK)

TABLOCKX HOLDLOCK有点过分.它会阻止常规的select语句,即使事务很小也可以认为很重.

一个ROWLOCK,HOLDLOCK表提示可能是一个更好的主意(但是：进一步阅读另一个计数器表).优点是它不阻止常规的select语句,即select语句不会出现在SERIALIZABLE事务中,或select语句不提供相同的表提示时.使用ROWLOCK,HOLDLOCK仍将阻止插入语句.

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

请注意,根据以这种方式锁定的行数,sql Server可能会将锁升级到表锁.阅读更多关于锁升级here.

使用WITH(ROWLOCK,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,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,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,@new_seq,@target_model);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

优点是使用较少的行锁(即dbo.counter_seq中的每个模型都有一个),并且锁升级无法锁定整个dbo.table_seq表,从而阻止select语句.

您可以测试所有这些,并通过在从counter_seq中选择序列后放置WAITFOR DELAY’00：01：00′,并在第二个SSMS选项卡中与表对齐,看到效果.

PS1：使用ROW_NUMBER()OVER(PARTITION BY型号ORDER BY ID)不是一个好办法.如果删除/添加行,或更改ID更改序列将更改(请考虑不应更改的发票ID).另外,在检索单个行时必须确定所有先前行的行号的性能方面是一个坏主意.

PS2：当sql Server已经通过隔离级别或细粒度的表提示提供锁定时,我永远不会使用外部资源来提供锁定.