Concurrent SQL Tranasactions Fail

Hi all,
I am having trouble trying to get an efficient way of checking if a
value pair exists in a table, and if not inserting the value pair and
returning the new id in the identity column. The old method I have used
has been fine for some time but the table has grown to a very large
size and the current code is causing a bottleneck.
The table consists the three columns, An identity column and two
nvarchar(15) columns. Instead of replicating the nvarchar(15) columns
over the database I have placed them in a lookup table and use the ID
from the identity column to reference the nvarchar values. I do store
multiple languages in the database so an nvarchar is necessary.
I admit the current code I use below is poor, but all of the
enhancements I have tried to make have resulted in the system failing,
as multiple copies of the procedure run and two (or more) procedures
try and insert the same data into the table (there is a unique
clustered index on the two varchar columns, and the identity column is
the primary key).
As our server application is multithreaded, it is highly likely
(certain) that two or more of these procedures will be running at once
so I have to ensure data integrity whilst checking and updating. Im
just not sure that a TABLOCKX,HOLDLOCK lock hint is the best way.
CREATE PROCEDURE dbo.CreateNewContextItem
@.CONTEXT NVARCHAR(15),
@.DATAITEM NVARCHAR(15),
@.ID INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRANSACTION
SET @.ID = (SELECT id FROM contextitem WITH (TABLOCKX,HOLDLOCK) WHERE
context = @.CONTEXT AND dataitem=@.DATAITEM)
IF @.ID IS NULL
BEGIN
INSERT INTO
contextitem (context,dataitem)
VALUES
(@.CONTEXT,@.DATAITEM)
IF @.@.ERROR<>0
GOTO Errhandler
SET @.ID = Scope_Identity()
IF @.@.ERROR<>0
GOTO Errhandler
END
COMMIT TRANSACTION
RETURN 0
Errhandler:
ROLLBACK TRANSACTION
RETURN 50063 -- application specific error code
GO
Is there a way of speeding up this procedure whilst still allowing
multiple copies of it to be run at once safely? Any help would be
greatly appreciated.
Thanks in advance.How about UPDLOCK instead of the TABLOCKX hint?
ML
http://milambda.blogspot.com/|||That seems to have worked a treat!
Thanks for your help ML