选择刚刚插入的具有复合主键的记录

Question

我有一个带有复合主键的表，排列如下：

CREATE TABLE [dbo].[mytable]
(
    [some_id] [smallint] NOT NULL,
    [order_seq] [smallint] NOT NULL,
    -- etc...
)

这两列都是主键的一部分（它实际上是真实表上的 4 部分 PK，但为了示例起见，我已将其简化）。没有一列是身份。我正在编写一个存储过程，它在给定 some_id 的下一个 order_seq 处插入新记录：

CREATE PROCEDURE some_proc
(
    @some_id smallint,
    @newSeq smallint OUTPUT
)
AS
BEGIN
    insert into mytable (some_id, order_seq)
    values 
    (
         @some_id, 
         (select max(order_seq) + 1 from mytable where some_id = @some_id)
    )

    set @newSeq = /* order_seq of the newly-inserted row */
END

我需要知道如何设置 @newSeq。我想避免在插入后运行选择查询，因为我不想遇到并发问题——禁止我锁定表或使用事务（不要问）。

据我所知，我无法使用 SCOPE_IDENTITY() ，因为没有一个列是身份。如何正确设置newSeq？

Answer 1

首先，如果 PK 包含四列，则每个插入必须包含所有四列。 则可以查看 Output 子句

Declare @NewSeqTable Table( Order_Seq int not null )

Insert MyTable( some_id, order_seq, otherPkCol, otherPkCol2 )
Output inserted.order_seq Into @NewSeqTable
Select @some_id, Max( order_seq ) + 1, otherPkCol, otherPkCol2
From MyTable
Where some_id = @some_id

Select Order_Seq
From @NewSeqTable

其次，如果您使用的是 SQL Server 2005+ OUTPUT 子句， （事务 SQL）

Answer 2

这里的答案取决于系统中的大小/并发问题。如果您不确定并发问题，请假设访问是多线程的。

单线程

如果您的系统较小，或者您可以确定一次只有一个线程会触及此功能，那么类似以下的操作将起作用：

CREATE PROCEDURE some_proc ( @KeyPart1 smallint, @newSeq smallint OUTPUT ) 
AS

DECLARE @KeyPart1 int
DECLARE @KeyPart2 int


SET @KeyPart1 = (SELECT <whatever your logic is here>)
SET @KeyPart2 =  select max(order_seq) + 1 from mytable where some_id = @KeyPart1

insert into mytable (some_id, order_seq)
values  ( @KeyPart1, @KeyPart2 )

set @newSeq = @KeyPart2

多线程访问

如果您不能确保只有一个线程会访问该过程，那么您的代码中需要一个事务。从您分享的内容来看，您似乎需要一个SERIALIZABLE交易。 SERIALIZABLE 是 SQL Server 中可用的并发最少（且保护性最强）的事务。由于您执行的读取标识了 max，因此您需要可序列化以防止会改变结果的幻像插入。

尽管您可能需要错误处理，但像下面这样的过程应该可以工作......

CREATE PROCEDURE some_proc ( @KeyPart1 smallint, @newSeq smallint OUTPUT ) 
AS

DECLARE @KeyPart1 int
DECLARE @KeyPart2 int

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SET @KeyPart1 = (SELECT <whatever your logic is here>)
SET @KeyPart2 =  select max(order_seq) + 1 from mytable where some_id = @KeyPart1

insert into mytable (some_id, order_seq)
values  ( @KeyPart1, @KeyPart2 )

set @newSeq = @KeyPart2

COMMIT TRAN

Answer 3

除非我弄错了，否则由于“select max(order_seq) + 1 from mytable”语句，您已经遇到了并发问题。我想说你提出的问题（无法锁定或进行交易）是不可能的。

如果 order_seq 不是smallint，我会说生成一个非常大的随机数作为 order_seq，并在（可能很少见）插入异常时重新生成。但这是对基本上不可行的情况的极端解决方案。

唯一的其他选择（我警告你这很奇怪）是制作一个确实有标识列的小虚拟表，然后基本上将生成的 id 复制到 newSeq 中。

Answer 4

为什么不先分配给 @newSeq，然后在插入中使用 @newSeq 变量？