sql - Only inserting a row if it's not already there -

i had used similar following achieve it:

insert thetable select     @primarykey,     @value1,     @value2     not exists     (select         null             thetable             primarykey = @primarykey) 

...but once under load, primary key violation occurred. statement inserts table @ all. mean above statement not atomic?

the problem impossible recreate @ will.

perhaps change following:

insert thetable     (holdlock,     updlock,     rowlock) select     @primarykey,     @value1,     @value2     not exists     (select         null             thetable             (holdlock,         updlock,         rowlock)             primarykey = @primarykey) 

although, maybe i'm using wrong locks or using locking or something.

i have seen other questions on stackoverflow.com answers suggesting "if (select count(*) ... insert" etc., under (perhaps incorrect) assumption single sql statement atomic.

does have ideas?

what "jfdi" pattern?

begin try    insert etc end try begin catch     if error_number() <> 2627       raiserror etc end catch 

seriously, quickest , concurrent without locks, @ high volumes. if updlock escalated , whole table locked?

read lesson 4