Insert after failed update, transaction is not locking

Question

I've got a small table containing user settings like window position, just a simple name=value pair table where the name needs to be unique:

CREATE TABLE dbo.[Settings]
(
  [Name] [nvarchar](100) NOT NULL,
  [Value] [nvarchar](4000) NOT NULL,
  CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED
  (
    [Name] ASC
  ) ON [PRIMARY]
) ON [PRIMARY]

When I store a changed setting, I start a transaction, try to update the value, if the update fails (because the record does not exist yet) I insert a new record:

// Begin Transaction

cmd.CommandText = "UPDATE dbo.[Settings] SET [Value] = @value WHERE [Name] = @name";
cmd.Parameters.AddWithValue("@value", value);
cmd.Parameters.AddWithValue("@name", name);
int cmdrc = cmd.ExecuteNonQuery();
if (cmdrc != 1)
{
  cmd.CommandText = "INSERT INTO dbo.[Settings] ([Name], [Value]) VALUES (@name, @value)";
  cmd.ExecuteNonQuery();
}

// Commit Transaction

My problem: if two threads happen to try and store a value for the same name at the same time (which does happen very rarely, but still), there will be a conflict:

Both threads try and update, both threads get cmdrc==0, both threads try and insert the new value, only one will succeed, the other one will get a SqlException.

I thought the transaction would lock the record, but it seems, since there is no record found on the Update statement, it does not get locked, so the other thread trying to update the same 'name' value will not be blocked, race condition happens.

Is there a way to get SQL Server to lock the keyword even though there is no record found for it?

score 1 · Accepted Answer · answered Dec 29 '11 at 15:31

1

You can use

UPDATE dbo.[Settings] 
WITH (HOLDLOCK, UPDLOCK)
SET [Value] = @value WHERE [Name] = @name

So the lock is held but why bother? Why not just ignore the error as it is arbitrary which one will "win" anyway.

answered Dec 29 '11 at 15:31

Martin Smith

438,706
87
741
845

Honestly, I asked mostly out of curiosity. Thanks for the pointer! – Sam Dec 29 '11 at 15:41
@Sam - [You might find the discussion here of interest](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) – Martin Smith Dec 29 '11 at 15:43
Ah yes, his problem is similar (but he seems to expect more inserts than updates, other than me). Interesting information, thanks! – Sam Dec 29 '11 at 15:49
1

@Sam - BTW I didn't mention this as it is covered in the earlier link but if you are on 2008 you can use `MERGE` to do this in one statement. [Though you still need locking hints.](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx) – Martin Smith Dec 29 '11 at 16:02

Insert after failed update, transaction is not locking

1 Answers1