什么可能导致主键异常？

Question

我的ASP页面使用以下存储过程将会话变量存储在SQL Server中：

CREATE PROCEDURE [dbo].[MyProcedure]
        @sessionId varchar(512),
        @variable varchar(350),
        @value image
AS
BEGIN   
        BEGIN TRAN
                DECLARE @result int = 0;
                DECLARE @locked bit;

                IF (SELECT COUNT(*) FROM Sessions WHERE id = @sessionId) = 0
                BEGIN
                        SET @result = -1;
                END
                ELSE BEGIN
                        DELETE Variables WHERE sessionId = @sessionId AND variable = @variable
                        IF @value IS NOT NULL
                        BEGIN
                                INSERT Variables VALUES(@sessionId, @variable, @value, 0)
                        END                                                  
                END    
    COMMIT TRAN
    RETURN @result
END

但偶尔会出现主键异常（Msg 2627）：“违反 PRIMARY KEY 约束条件 'PK_Variables'。无法将重复键插入对象 'dbo.Variables'。” 注意：没有涉及触发器。

谢谢！

- Jorgito Gutierrez

---

我们能否有“变量”表的结构？你的“变量”表的主键是否自增？ - bAN

Answer 1

假设您的主键是基于sessionId,variable，那么使用相同的@sessionId,@variable并发执行存储过程可能会出现这种情况。两者都会执行。

DELETE Variables WHERE sessionId = @sessionId AND variable = @variable

同时执行并行操作，然后两者都继续进行插入。

只有当没有先前使用sessionId,variable组合的记录时，才会发生这种情况，因为此时DELETE将被阻止。

- Martin Smith

---

但是 BEGIN/COMMIT 不能防止这种情况发生吗？如果不行，我该如何锁定记录（而无需锁定整个表）？感谢您的时间。 - Jorgito Gutierrez

@JorgitoGutierrez - 当记录不存在时，就没有什么可以锁定的了。您需要使用serializable隔离级别或等效的锁提示来锁定范围，但是当前行为有什么问题吗？如果您有两个并发执行具有相同的sessionid,variable，那么哪一个“获胜”是相当任意的，因此为什么不让失败者引发PK冲突而不是阻止它，以便立即覆盖另一个执行？ - Martin Smith

我现在对如何实现解决方案感到困惑...我从未使用过可序列化隔离。如果我执行“SELECT * FROM WHERE sessionId = @sessionId AND variable = @variable”，会有什么帮助锁定数据（如果不存在）吗？ - Jorgito Gutierrez

@JorgitoGutierrez - 我不会在整个程序中使用 serializable。只需将 HOLDLOCK 添加到 DELETE 语句中即可锁定该范围。例如：DELETE Variables WITH(HOLDLOCK) WHERE sessionId = @sessionId AND variable = @variable - Martin Smith