Sql Server 2005 - Insert if not exists

Question

There is lots of information in the internet regarding this common "problem".

Solutions like:

IF NOT EXISTS() BEGIN INSERT INTO (...) END

are not thread-safe in my opinion and you will probably agree.

However could you confirm that putting the exist into the where clause of one single select would solve the problem of the highest concurrency in sql engine? Is it enough?

insert into Table (columns)
select column1, column2, column3
where not exists (select top 1 1 from Table where something)

Should be there also added some higher transaction level or can this be executed on a default one: committed?

Would this work under uncommitted level?

Thanks!

//Added later

Can i assume that both sql' are correct:

1) set transaction isolation level repeatable read

   IF NOT EXISTS() BEGIN INSERT INTO (...) END

2) set transaction isolation level repeatable read

insert into Table (columns)
select column1, column2, column3
where not exists (select top 1 1 from Table where something)

Answer 1

To answer the updated question repeatable read would still not be sufficient.

It is holdlock / serializable level that you would need.

You are trying to prevent phantoms (where on the first read no rows met the criteria so the NOT EXISTS returns true but subsequently a concurrent transaction inserts a row meeting it)

Answer 2

With TRY/CATCH you can avoid the extra read

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

• A NOT EXISTS will read the table, whether in the IF or WHERE
• The INSERT requires a read to check uniqueness

If you can discard duplicates, this is a highly scalable technique

Links:

• See my answers here: Only inserting a row if it's not already there and SQL Server 2008: INSERT if not exits, maintain unique column
• If you requires UPDATEs too: Which is the best choice in delete-insert vs if-update else-insert?