Skip to content

Concurrent Inserts

February 6, 2013

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

From → Dev, SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: