IF NOT EXISTS doesn't appear to be working

Question

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

Why does it run the script, if there is a duplicate key..? I am confused.. Any help would be greatly appreciated.

Many thanks..

Not sure why you get that error, but I'd recommend using the "MERGE" statement for this work, as it performs check/update it in one pass. — Paul Grimshaw, Jul 12 '13 at 12:25
What happens if you make ` ... WHERE NOT EXISTS ( SELECT ...)` part of the insert statement rather than a conditional branch? — Tim, Jul 12 '13 at 12:30
@Tim - [That won't prevent the race condition without additional locking hints](http://stackoverflow.com/q/3407857/73226) — Martin Smith, Jul 12 '13 at 12:32

score 4 · Answer 1 · answered Jul 12 '13 at 12:26

4

Two concurrent overlapping processes will both pass the NOT EXISTS check and try to INSERT.

That is, the NOT EXISTS is a separate query to the INSERT

Both the NOT EXISTS and the INSERT can be written into a single 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);

answered Jul 12 '13 at 12:26

gbn

422,506
82
585
676

could you explain this further..? How would I rewrite the script to avoid this issue? – neojakey Jul 12 '13 at 12:29
@openshac: I don't need one with MERGE: this is the point of MERGE to do a single call and remove the need for one – gbn Jul 12 '13 at 12:34
I know you don't need one with MERGE :-) I was responding to @neojakey who asked if he could rewrite the script. – openshac Jul 12 '13 at 12:35
@gbn - If I was to remove SELECT RegionID FROM Region WHERE Domain = Domain from the script and got the regionid before I ran this script.. would that solve the problem..? – neojakey Jul 12 '13 at 13:06
@neojakey: no. The problem is the NOT EXISTS..INSERT. Use Merge like I said – gbn Jul 12 '13 at 13:20
It appears that this query will fail to insert anything if a matching row is not found in the Region table. I doubt that's the desired behavior. – JLRishe Jul 12 '13 at 14:04
@JLRishe: This was based on OPs select. Given we don't have the table definition and can see if it allows NULLs, either of us could be right. – gbn Jul 12 '13 at 14:06
OP's query has concurrency issues, but it does not have the issue I am describing. It's not a matter of whether the SessionData table allows NULLs. If there is no matching row in the Region table, then `SELECT ... FROM Region WHERE Domain = @Domain` will produce 0 rows, meaning that `src` will have 0 rows, and there will be nothing to insert. – JLRishe Jul 12 '13 at 14:09
If SessionData.RegionID is NOT NULL then a row *must* exist on the Region table. As it stands, I'm fixing concurrency based on incomplete information.. – gbn Jul 12 '13 at 14:12

JLRishe · Answer 2 · 2013-07-12T14:23:25.710

-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.

edited Jul 12 '13 at 14:23

answered Jul 12 '13 at 13:42

JLRishe

99,490
19
131
169

1

-1: still not safe for concurrency. The NOT EXISTs can still give true. Why I use MERGE with SERIALIZABLE... See my answers here http://stackoverflow.com/search?tab=votes&q=user%3a27535%20jfdi – gbn Jul 12 '13 at 14:07
You can ofc use locking hints as per http://stackoverflow.com/a/3407890/27535 (see comments) to make it safe. But your code is what MERGE does internally anyway... Also see http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/ – gbn Jul 12 '13 at 14:19

IF NOT EXISTS doesn't appear to be working

2 Answers2