问题描述
我的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 DELETE
s would block.
这篇关于是什么导致主键异常?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!