SQL Concurrent test update question
- by ptoinson
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.
