是什么导致主键异常？ [英] What could be causing the primary key exception?

问题描述

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

My ASP pages store session variables in SQL Server with the following stored procedure:

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

但是偶尔，我会遇到一个主键异常（消息2627）：违反了主键约束'PK_Variables'。无法在其中插入重复键对象 dbo.Variables。
注意：不涉及任何触发器。

But once in a while, I get a primary key exception (Msg 2627): "Violation of PRIMARY KEY constraint 'PK_Variables'. Cannot insert duplicate key in object 'dbo.Variables'". Note: There are no triggers involved.

谢谢！

推荐答案

假设您的PK位于 sessionId，variable 上，然后使用相同的 @ sessionId，@ variable 可以做到这一点。

Assuming your PK is on sessionId,variable then concurrent executions of the stored procedure with the same @sessionId,@variable could do this.

都执行

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

行同时并发至插入。

只有在不存在带有 sessionId的记录时，才会发生这种情况，变量组合，则 DELETE 会被阻止。

This could only occur if there is no pre-existing record with the sessionId,variable combination as then the DELETEs would block.

这篇关于是什么导致主键异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！