存储过程在与相同参数并发调用时引发违反主键约束

Question

CREATE TABLE dbo.SomeTable
(
    ID int NOT NULL,
    SomeText varchar(10) NOT NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.SomeTable 
    ADD CONSTRAINT PK_SomeTable 
    PRIMARY KEY CLUSTERED (ID)
         WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
              ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[InsertSomeText]
    @ID int,
    @SomeText varchar(10)
AS
BEGIN
    BEGIN TRANSACTION
    DELETE FROM SomeTable WHERE ID = @ID
    INSERT INTO SomeTable (ID, SomeText)
    VALUES (@ID, @SomeText)
    COMMIT TRANSACTION      
END

上面的存储过程有时会引发错误

违反主键约束

同时调用相同的@ID值时。

删除/插入操作在事务中，所以我认为这将是一个原子操作，但仍然有一段时间它会引发异常。

Answer 1

实际上，避免竞争条件的万无一失的方法很少，除非您指定锁定级别，否则使用事务不是其中之一。不幸的是，我从未见过使用删除和插入来管理的更新插入，因此我找不到任何文档或测试来显示竞争条件发生的方式和原因。

与其修复一个有点奇怪的方法（删除/插入），我建议简单地使用 MERGE WITH (HOLDLOCK) ，这是线程安全的：

CREATE PROCEDURE [dbo].[InsertSomeText]
    @ID int,
    @SomeText varchar(10)
AS
BEGIN
    MERGE dbo.SomeTable WITH (HOLDLOCK) AS t
    USING (VALUES (@ID, @SomeText)) s (ID, SomeText)
        ON s.ID = t.ID
    WHEN MATCHED THEN
        UPDATE SET SomeText = s.SomeText
    WHEN NOT MATCHED THEN 
        INSERT (ID, SomeText) VALUES (s.ID, s.SomeText);
END;

Answer 2

尝试将隔离级别更改为可序列化（最严格）以避免任何并发问题。有关详细信息，请参阅 MSDN：https://msdn.microsoft.com/en-IN/library/ms173763.aspx