SQL Concurrency Testing Question

Question

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

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) ) 

The name is also a unique index. Next, I have several processes adding data to this table pretty quickly. These processes use the proc stored procedure, which looks like this:

 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 problems are that, in addition to being new to parallel database design, there seems to be a race condition due to which I sometimes get an error message that I try to enter in the database with double keys (Name). Error:

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

It makes sense, I just don't know how to fix it. If this is where the code is, I would know how to lock the correct areas. SQLServer is a completely different beast.

The first question is the correct way to code this "check and then update the template"? It seems I need to get an exclusive row lock during validation, not a general lock, but I don’t understand how to do this. Any help in the right direction would be greatly appreciated. Thanks in advance.

Answer 1

I prefer the output parameters (so I encoded it this way), but this should be done faster, with the least number of hits in the table:

 ALTER PROC [dbo].[lg_Tag_Insert] @Name varchar(255) ,@ID int OUTPUT AS BEGIN TRY SET @ID=NULL INSERT Tag (Name) VALUES (@Name) SET @ID=SCOPE_IDENTITY() END TRY BEGIN CATCH SELECT @ID=ID from Tag where Name=@Name END CATCH IF @ID IS NULL BEGIN RETURN 1 END RETURN 0 GO 

Answer 2

The correct code is:

• In SP, preferably working with a serializable transaction
• First make a selection in the tag table to get the identifier
• If null, insert.

Transaction isolation will ensure transaction serialization.

Client side cache tags, so you don’t insert when the client already knows that they are. Performance will be minimal.

You seem to be doing this, so the only issue may be the level of transaction isolation.

What can you do:

• Use separate connections to insert tags.
• When you get a repeating error, ignore it, request an identifier, use id. Because you are on a separate connection that doesn't matter.

Answer 3

I found the best results in tables with heavy inserts to set a limit on “Ignore duplicates” and let the cheaters fall to the floor when capturing new inserts. for your sproc, you can completely eliminate the test and return SCOPE_IDENTITY () or null after insertion.