sql - IF NOT EXISTS 似乎不起作用

Question

我有以下脚本:

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

有时网站会产生如下错误:

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

为什么它运行脚本，如果有重复的键..？我很困惑.. 任何帮助将不胜感激。

非常感谢..

Answer 1

两个并发重叠进程都将通过 NOT EXISTS 检查并尝试 INSERT。

也就是说，NOT EXISTS 是对 INSERT 的单独查询

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);