Danger of using 'IF EXISTS… UPDATE .. ELSE ..

Question

This question is related with Occasionally Getting SqlException: Timeout expired. Actually, I am using IF EXISTS... UPDATE .. ELSE .. INSERT heavily in my app. But user Remus Rusanu is saying that you should not use this. Why I should not use this and what danger it include. So, if I have

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

How to rewrite this statement to make it work?

Answer 1

Use MERGE

Your SQL fails because 2 concurrent overlapping and very close calls will both get "false" from the EXISTS before the INSERT happens. So they both try to INSERT, and of course one fails.

This is explained more here: Select / Insert version of an Upsert: is there a design pattern for high concurrency? THis answer is old though and applies before MERGE was added

Answer 2

Merge, is in the first case created to compare 2 tables, so if that is the case you could use merge.

Take a look at the following, which is also another option.

In this case unfortunately you have the possibility of getting a problem with concurrency.

--Just update a row 
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
   -- If 0 rows are updated ...
  IF @@ROWCOUNT=0
--Insert Row
INSERT INTO Table1 VALUES (...)

In this blog its explained more.

Additionally this interesting blog is about concurrency.

Answer 3

The problem with IF EXISTS ... UPDATE ... (and IF NOT EXISTS ... INSERT ...) is that under concurrency multiple threads (transactions) will execute the IF EXISTS part and all reach the same conclusion (eg. it does not exists) and try to act accordingly. Result is that all threads attempt to INSERT resulting in a key violation. Depending on the code this can result in constraint violation errors, deadlocks, timeouts or worse (lost updates).

You need to ensure that the check IF EXISTS and the action are atomic. On pre SQL Server 2008 the solution involved using a transaction and lock hints and was very very error prone (easy to get wrong). Post SQL Server 2008 you can use MERGE, which will ensure proper atomicity as is a single statement and the engine understand what you're trying to do.

Answer 4

Sample MERGE statement in this case would be:

MERGE INTO Table1 t1
USING (SELECT 'SomeValue' as Column_id FROM dual) t2 ON
(t1.column_id = t2.column_id)
WHEN MATCHED THEN
    UPDATE SET(...)
WHEN NOT MATCHED THEN
    INSERT (t1.column_id)
    VALUES ('SomeValue');