sql server - Do I need lock-hints in my select-insert procedure? -

i have stored procedure inserts row table if row key doesn't exist. looks this:

create proc employeeinsertifnotexists      (@id int, @name varchar(50))   begin     set xact_abort on      begin transaction      if not exists(select * tbl id = @id)         insert tbl(id, name)          values(id, name)      commit transaction end 

this stored procedure 2 statements, select , possible insert. both statements inside of transaction nothing can happen in between them cause exception. id column primary key, want ensure don't insert same id twice.

my question is: enough precaution prevent problems? need put hints in select statement? if so, need holdlock, tablockx? new material me.

edit: suggested answer

create proc employeeinsertifnotexists     (@id int, @name varchar(50))   begin     set xact_abort on     set transaction isolation level serializable      begin transaction      if not exists(select * tbl id = @id)         insert tbl(id, name)          values(id, name)      commit transaction end 

you want mark transaction isolation level serializable. otherwise can insert row same id half-way through transaction. known "phantom row".

you don't need lock whole table. using correct isolation level, sql server can smarter how applies locks.