Howdy Folks,

I have a SQLServer 2008 database in which I have a table for Tags. A tag is just an id and a name. The definition of the tags table looks like:

CREATE TABLE [dbo].[Tag](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](255) NOT NULL
 CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, 
       STATISTICS_NORECOMPUTE  = OFF, 
       IGNORE_DUP_KEY = OFF,      
       ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON)
)

Name is also a unique index. further I have several processes adding data to this table at a pretty rapid rate. These processes use a stored proc that looks like:

ALTER PROC [dbo].[lg_Tag_Insert]
    @Name varchar(255)
AS


DECLARE  @ID int
SET @ID = (select ID from Tag where Name=@Name )

if @ID is null
    begin
            INSERT Tag(Name)
            VALUES (@Name)

            RETURN SCOPE_IDENTITY()

    end
else
    begin
        return @ID
    end

My issues is that, other than being a novice at concurrent database design, there seems to be a race condition that is causing me to occasionally get an error that I'm trying to enter duplicate keys (Name) into the DB. The error is:

Cannot insert duplicate key row in object 'dbo.Tag' with unique index 'IX_Tag_Name'.

This makes sense, I'm just not sure how to fix this. If it where code I would know how to lock the right areas. SQLServer is quite a different beast.

First question is what is the proper way to code this 'check, then update pattern'? It seems I need to get an exclusive lock on the row during the check, rather than a shared lock, but it's not clear to me the best way to do that. Any help in the right direction will be greatly appreciated. Thanks in advance.