Category Archives: Dev

Concurrent Inserts

Beware of adding/inserting with parallel processes – to avoid duplicate inserts you need to keep a range lock open when you check for the existence of the ID

CREATE PROCEDURE dbo.Insert_Or_Update_Foo

@ID int,

@Bar int

AS

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) WHERE ID = @ID)

BEGIN

UPDATE dbo.Foo

SET bar = @bar

WHERE ID = @ID

END

ELSE

BEGIN

INSERT INTO dbo.Foo (ID, Bar)

VALUES (@ID, @Bar)

END

COMMIT

RETURN @@ERROR

http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

also  consider sp_getapplock

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

to develop…¬† use of merge on SQL2008

 

Advertisements