atomic if not exists() and insert or update

_{Wednesday, May 5, 2010 1:24 AM}

Hi,

My questions is related to a very common issue: I would like to insert a record into table but only if it does not exists.

I'd see two solutions:

1. checking if the record exists before inserting it, as follows

if not exists(select ....)
 insert into ...

Is this operation GUARANTEED to be atomic on a multi CPU/Core system? If so is there any study from Microsoft?

2. blindly go and do the insert within try\catch block and mute out the duplication error if it happenss

Assuming that there is a unique index that would be violated, is this approach recommended in a high-volumn OLTP system?
If so is there any study from Microsoft?

Thank you

_{Wednesday, May 5, 2010 2:58 PM ✅Answered}

You would have to use locking hints to guarantee no inserts from other processes. See the following links for good discussions:

http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

Plamen Ratchev

_{Wednesday, May 5, 2010 1:58 AM}

Your first scenario is two statements and it is not atomic. But if you only need to insert not existing, then you can use a single statement:

INSERT INTO Foo (...)

SELECT ...

FROM Bar

WHERE NOT EXISTS (SELECT *

                               FROM Foo

                               WHERE <match predicate>);

On SQL Server 2008 this is easier, simply use the MERGE statement:

MERGE INTO Foo

USING Bar

   ON <match predicate>

WHEN NOT MATCHED

  INSERT ... ;

Plamen Ratchev

_{Wednesday, May 5, 2010 2:00 AM}

Forgot to note that with MERGE you can do both INSERT and UPDATE in a single statement:

MERGE INTO Foo

USING Bar

   ON <match predicate>

WHEN NOT MATCHED

  INSERT ...

WHEN MATCHED

  UPDATE ...;

Plamen Ratchev

_{Wednesday, May 5, 2010 3:52 AM}

Hello,

If you are working on SQL SERVER 2008 then you can go with Plamen Ratchev. If it is SQL SERVER 2005 then you dont have MERGE, for that use below process.

First update the rows which ever match and then Insert the rows which are not present in the entity.

UPDATE F
SET F.ColumName = B.ColumnName
 ....
FROM Foo F
INNER JOIN Bar B ON B.KeyID = F.KeyID

INSERT INTO Foo
SELECT B.*
FROM Bar B
LEFT JOIN Foo F On F.KeyID = b.KeyID
WHERE F.KeyID IS NULL

Hope its clear & helpful...

Pavan Kokkula (Technology Analyst) Infosys Technologies Limited.

_{Wednesday, May 5, 2010 4:18 AM}

Thank you for your reply.

Good idea with the merge. Something to consider.

As for the other suggestion to put it all together in one statement as below

INSERT INTO Foo (...)

SELECT ...

FROM Bar

WHERE NOT EXISTS (SELECT *

                               FROM Foo

                               WHERE <match predicate>);

I am trying to udnerstand why this would work. SQL Server will execute the where clause with the select statement and keep the shared locks on it until the whole statement finishes (including the insert). If there is no match it would then insert a new record. What's the mechanism which ensures that another user is not going to insert a record between the end fo the select and the insert? What I mean is, if the 'where not exists' yields no records there will be no shared locks and as a result there is nothing to stop another user to insert a record. If the select in the 'where not exists' had a table hint 'serializable' then I believe SQL server would place a range lock which would prohibit another user from inserting a record. I am not an expert, I am probably wrong, just trying to understand the process.

In the past I used a transaction and isolation levels for the first scenario if (exists (..)). The code would look like:

begin

tran

if

not (exists(select * from table(SERIALIZABLE) where ...))

                  insert ...

other statements

commit

The thinking behind this was that a range lock is placed on a table as I check for existence of a record for the duration of the whole transaction not just a statement. It never failed me, but it's a heavy solution.

_{Thursday, May 6, 2010 2:12 AM}

Thanks