I have the following script:
我有以下脚本:
BEGIN
IF NOT EXISTS (SELECT SessionID FROM SessionData WHERE SessionID = @SessionID)
BEGIN
SELECT @RegionID = RegionID
FROM Region
WHERE Domain = @Domain
INSERT INTO SessionData (
SessionID,
SystemID,
RegionID,
RegionDomain,
RemoteAddr,
CreatePage)
VALUES (
@SessionID,
@SystemID,
@RegionID,
@RegionDomain,
@RemoteAddr,
@CreatePage)
END
END
Occasionally the site produces an error as follows:
有时,该网站会产生如下错误:
Violation of PRIMARY KEY constraint 'PK_SessionData'. Cannot insert duplicate key in object 'sbuser.SessionData'. The duiplicate key value is (1h6l61h069srw1nmw73j). Source: Microsoft OLE DB Provider for SQL Server Number: -2147217873
违反PRIMARY KEY约束'PK_SessionData'。无法在对象'sbuser.SessionData'中插入重复键。 duiplicate键值为(1h6l61h069srw1nmw73j)。源:用于SQL Server的Microsoft OLE DB提供程序号:-2147217873
Why does it run the script, if there is a duplicate key..? I am confused.. Any help would be greatly appreciated.
如果有重复的密钥,为什么它会运行脚本..?我很困惑..任何帮助将不胜感激。
Many thanks..
2 个解决方案
#1
4
Two concurrent overlapping processes will both pass the NOT EXISTS check and try to INSERT.
两个并发重叠进程都将通过NOT EXISTS检查并尝试INSERT。
That is, the NOT EXISTS is a separate query to the INSERT
也就是说,NOT EXISTS是对INSERT的单独查询
Both the NOT EXISTS and the INSERT can be written into a single MERGE
NOT EXISTS和INSERT都可以写入单个MERGE
MERGE INTO
SessionData WITH (SERIALIZABLE) S
USING (
SELECT
@SessionID AS SessionID ,
@SystemID AS SystemID ,
RegionID,
@RegionDomain AS RegionDomain ,
@RemoteAddr AS RemoteAddr ,
@CreatePage AS CreatePage
FROM Region
WHERE Domain = @Domain
) src ON S.SessionID = src.SessionID
WHEN NOT MATCHED THEN
INSERT (
SessionID,
SystemID,
RegionID,
RegionDomain,
RemoteAddr,
CreatePage)
VALUES (
src.SessionID,
src.SystemID,
src.RegionID,
src.RegionDomain,
src.RemoteAddr,
src.CreatePage);
#2
-1
How about this:
这个怎么样:
INSERT INTO SessionData (
SessionID,
SystemID,
RegionID,
RegionDomain,
RemoteAddr,
CreatePage)
SELECT
@SessionID,
@SystemID,
(SELECT TOP 1 RegionID FROM Region WHERE Domain = @Domain),
@RegionDomain,
@RemoteAddr,
@CreatePage)
WHERE
NOT EXISTS (SELECT SessionID FROM SessionData WITH (UPDLOCK, HOLDLOCK)
WHERE SessionID = @SessionID)
That's a concise way to get an atomic operation without any MERGEs or transactions.
这是一种在没有任何MERGE或事务的情况下获得原子操作的简明方法。
#1
4
Two concurrent overlapping processes will both pass the NOT EXISTS check and try to INSERT.
两个并发重叠进程都将通过NOT EXISTS检查并尝试INSERT。
That is, the NOT EXISTS is a separate query to the INSERT
也就是说,NOT EXISTS是对INSERT的单独查询
Both the NOT EXISTS and the INSERT can be written into a single MERGE
NOT EXISTS和INSERT都可以写入单个MERGE
MERGE INTO
SessionData WITH (SERIALIZABLE) S
USING (
SELECT
@SessionID AS SessionID ,
@SystemID AS SystemID ,
RegionID,
@RegionDomain AS RegionDomain ,
@RemoteAddr AS RemoteAddr ,
@CreatePage AS CreatePage
FROM Region
WHERE Domain = @Domain
) src ON S.SessionID = src.SessionID
WHEN NOT MATCHED THEN
INSERT (
SessionID,
SystemID,
RegionID,
RegionDomain,
RemoteAddr,
CreatePage)
VALUES (
src.SessionID,
src.SystemID,
src.RegionID,
src.RegionDomain,
src.RemoteAddr,
src.CreatePage);
#2
-1
How about this:
这个怎么样:
INSERT INTO SessionData (
SessionID,
SystemID,
RegionID,
RegionDomain,
RemoteAddr,
CreatePage)
SELECT
@SessionID,
@SystemID,
(SELECT TOP 1 RegionID FROM Region WHERE Domain = @Domain),
@RegionDomain,
@RemoteAddr,
@CreatePage)
WHERE
NOT EXISTS (SELECT SessionID FROM SessionData WITH (UPDLOCK, HOLDLOCK)
WHERE SessionID = @SessionID)
That's a concise way to get an atomic operation without any MERGEs or transactions.
这是一种在没有任何MERGE或事务的情况下获得原子操作的简明方法。