SQL Server 2012 potential race condition on select or insert

Question

I need to run a SQL Server query that either "gets" or "creates" a record. Pretty simple, but I am not sure if this will create any race conditions.

I have read a few articles regarding locking during insert/updates e.g. http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx, but I'm not sure if this is relevant to me given I'm using SQL Server 2012, and I don't have contention over modification per se, only over ensuring 'one off creation' of my record.

Also, I will have a primary key constraint over the Id column (not an identity column), so I know the race condition at worst could only ever create an error, and not invalid data, but I also don't want the command to throw an error.

Can someone please shed some light on how I need to solve this? Or am I over thinking this and can I simply do something like:

IF EXISTS(SELECT * FROM Table WHERE Id = @Id)
BEGIN
      SELECT Id, X, Y, Z FROM Table WHERE Id = @Id
END
ELSE
BEGIN
      INSERT INTO Table (Id, X, Y, Z)
      VALUES (@Id, @X, @Y, @Z);

      SELECT @Id, @x, @Y, @Z;
END

I've been in document database land for a few years and my SQL is very rusty.

Answer 1

Your code definitely has race conditions.

One approach is to merge the conditions into one insert, but I'm not 100% sure that this protects against all race conditions, but this might work:

INSERT INTO Table(Id, X, Y, Z)
    SELECT @Id, @X, @Y, @Z
    WHERE NOT EXISTS (SELECT 1 FROM Table WHERE ID = @ID);

SELECT Id, X, Y, Z FROM Table WHERE ID = @Id;

The issue is about the underlying locking mechanism during the duplicate detection versus the insert. You can try to use more explicit locking, but a table lock will surely slow down processing (to learn about locks, you can turn to the documentation or a blog post such as this).

The simplest way that I can think to make this work consistently is to use try/catch blocks:

BEGIN TRY
    INSERT INTO Table(Id, X, Y, Z)
        SELECT @Id, @X, @Y, @Z;
END TRY
BEGIN CATCH
END CATCH;

SELECT Id, X, Y, Z FROM Table WHERE ID = @Id;

That is, try the insert. If it fails (presumably because of a duplicate key, but you can explicitly check for that), then ignore the failure. Return the values for the id afterwards.

Answer 2

GET occurs far more frequently than CREATE, so it makes sense to optimize for that.

IF EXISTS (SELECT * FROM MyTable WHERE Id=@Id)
    SELECT Id, X, Y, Z FROM MyTable WHERE Id=@Id
ELSE
BEGIN
    BEGIN TRAN
        IF NOT EXISTS (SELECT * FROM MyTable WITH (HOLDLOCK, UPDLOCK) WHERE Id=@Id )
        BEGIN
        --  WAITFOR DELAY '00:00:10'
            INSERT INTO MyTable (Id, X, Y, Z) VALUES (@Id, @X, @Y, @Z)
        END
        SELECT Id, X, Y, Z FROM MyTable WHERE Id=@Id
    COMMIT TRAN
END

If your record does not exist, begin an explicit transaction. Both HOLDLOCK and UPDLOCK are required (thanks @MikaelEriksson). The table hints hold the shared lock and update lock for the duration of the transaction. This properly serializes the INSERT and reliably avoids the race condition.

To verify, uncomment WAITFOR and run two or more queries at the same time.

Answer 3

Why do you need two different SELECTs?

IF NOT EXIST (SELECT TOP 1 1 FROM Table WHERE Id = @Id)
    INSERT INTO Table(Id, X, Y, Z)
    VALUES (@Id, @X, @Y, @Z)

SELECT Id, X, Y, Z FROM Table WHERE ID = @Id

As long as you don't specify WITH (NOLOCK) when you're checking existence, you should be fine.