7

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)
Improve this question
15
  • 1
    No it won't work at either readcommitted or uncommitted level. You would need some additional locking hints. possible duplicate of Only inserting a row if it's not already there Commented May 25, 2011 at 7:24
  • 1
    You don't need to "help out" exists clauses - they're smart enough to finish after they've seen 1 row. you can just do EXISTS (SELECT * FROM... and it does the right thing. Commented May 25, 2011 at 7:26
  • it will do the right thing, but in case of the cocurrency it is not thread-safe enough and primary violation errors may occur under high load. So according to the link of @Martin, repeatable read isolation transaction should be added. Commented May 25, 2011 at 7:35
  • @Paul - I meant you don't need to do select top 1... in an exists clause - I wasn't commenting on the rest of the code. Commented May 25, 2011 at 7:37
  • @Damien_The_Unbeliever Aaa ok sorry for misunderstanding. I have always thought that writing: "top 1 1" doesn't make the server to fetch values of the columns.. Commented May 25, 2011 at 7:41

2 Answers 2

Reset to default
6

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Well ok, but if i Should insert or update then: Could I do: if error_number() = 2627 begin update ... end?
@Paul: see my last link then for dealing with UPDATEs
Is it possible to raise default error in begin catch block? or Do I have to explixity get the error_number and the message to satisfy raiserror parameters?
@Paul: You'd have to read ERROR_NUMBER() and ERROR_MESSAGE() etc and build your own message. Example. However, the number then becomes 50000. The ability to rethrow the same error is added next version: THROW
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)

Improve this answer

2 Comments

It seems that I will start being paranoic when writing sql statements :)
@Paul - serializable would also give you the risk of deadlock, This could be avoided with UPDLOCK, HOLDLOCK. I would use TRY...CATCH myself though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.