Testing an Insert for Race Conditions with Ostress.exe

Whenever we have multiple sessions modifying data, things get tricky. When we have a pattern of “check if the data exists and then do a thing,” multiple sessions get even more complicated.

It’s not very practical to try to open a bunch of sessions in SQL Server Management Studio to run commands in a loop. It’s hard to manage and if you’re like me, you find a way to crash SSMS doing it.

It’s much easier to test for race conditions using the free OStress.exe tool from Microsoft. Download OStress.exe as part of the RML Utilities for SQL Server (x64) here.

Our Example: Insert a Row if It Doesn’t Exist Already

We have a simple table named dbo.RaceCondition with two columns: RaceConditionId and RaceConditionValue. RaceConditionId is the clustered Primary Key. A unique index on RaceConditionValue prevents duplicate rows from being inserted.

Here’s how to create it:

IF DB_ID('RaceConditionTesting') is not null
BEGIN
    USE MASTER;
    ALTER DATABASE RaceConditionTesting SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE RaceConditionTesting;
END
GO

CREATE DATABASE RaceConditionTesting;
GO

use RaceConditionTesting;
GO

CREATE TABLE dbo.RaceConditionTable
(
    RaceConditionId int IDENTITY(1,1) NOT NULL,
    RaceConditionValue varchar(50) NOT NULL,
    CONSTRAINT pk_RaceConditionTable PRIMARY KEY CLUSTERED (RaceConditionId ASC)
);
GO

CREATE UNIQUE INDEX ix_RaceConditionTable_RaceConditionValue on 
    dbo.RaceConditionTable(RaceConditionValue);
GO

/* Insert one row */
INSERT dbo.RaceConditionTable (RaceConditionValue) VALUES ('Foo');
GO

Our First Attempt: A Single Select Statement

Here’s the code to insert rows into dbo.RaceConditionTable. dbo.InsertIfNotExists contains just one INSERT/SELECT statement.

Note that this is all one statement. There’s no “IF” statement. There’s also no hints.

IF OBJECT_ID('dbo.InsertIfNotExists') IS NULL
    EXEC ('CREATE PROCEDURE dbo.InsertIfNotExists as RETURN 0;');
GO
ALTER PROCEDURE dbo.InsertIfNotExists (
    @RaceConditionValue varchar(50)
)
AS
    SET NOCOUNT ON;

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;
GO

Our Simple Test Harness: dbo.RaceConditionTest and Ostress.exe

Now we need a way to test this.  dbo.RaceConditionTest is a procedure that looks at RaceCondition table, picks the highest value for RaceConditionId in the table, adds one to it and turns that into a character value for RaceConditionValue.

We are going to run this procedure in OStress.exe across multiple threads. Those threads will all be generating values for RaceConditionValue using the same table. Will the simple INSERT/SELECT statement use the right locks to prevent them from generating the same values? If so, they’ll fail, because our unique index does not allow duplicate values.

IF OBJECT_ID('dbo.RaceConditionTest') IS NULL
    EXEC ('CREATE PROCEDURE dbo.RaceConditionTest as RETURN 0;');
GO
ALTER PROCEDURE dbo.RaceConditionTest 
AS
    DECLARE @namevarchar varchar(50)
    SELECT @namevarchar = CAST(MAX(RaceConditionId)+1 AS varchar(50))
    FROM dbo.RaceConditionTable

    EXEC dbo.InsertIfNotExists @RaceConditionValue=@namevarchar;
GO

I’ll run OStress.exe with this command:

ostress.exe -Q"exec RaceConditionTesting.dbo.RaceConditionTest" -n6 -r100 -o"c:\\ostressoutput"

This tells OStress to run the RaceConditionTest procedure on six threads, try to run it 100 times on each thread, and to put output files in c:\ostressoutput.

Test Results: The Simple Insert/Select

OStress.exe spews insert failures with our first INSERT statement. The message “Cannot insert duplicate key row in object ‘dbo.RaceConditionTable’ with unique index ‘ix_RaceConditionTable_RaceConditionValue’” is everywhere:

Here’s the insert command we’ve been using, to review:

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;

The OStress.exe results show that the locks on the LEFT JOIN to race condition to check if a row exists can be processed and released before the INSERT runs. This means that the following can happen:

• Session A sees that key 266 does not exist and prepares to insert
• Session B sees that key 266 does not exist and prepares to insert
• Session A runs its  insert
• Session B attempts to run its insert, but fails because of a duplicate key error

Revised Code: Higher Locks with UPDLOCK

What if we tell SQL Server to take out a higher lock when it reads the row? If we block when we read, maybe that will solve the problem by making others wait. Let’s test it. Here’s our revised code:

IF OBJECT_ID('dbo.InsertIfNotExists') IS NULL
    EXEC ('CREATE PROCEDURE dbo.InsertIfNotExists as RETURN 0;');
GO
ALTER PROCEDURE dbo.InsertIfNotExists (
    @RaceConditionValue varchar(50)
)
AS
    SET NOCOUNT ON;

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p WITH (UPDLOCK) on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;
GO

The only change here is that the LEFT JOIN now has a hint telling SQL Server to take out an update lock (UPDLOCK) when it reads the row.

Test Results: UPDLOCK

Re-running OStress.exe, I can see that I haven’t fixed the issue. I’ve still got unique key violations galore:

Even though I’m taking out UPDLOCKS, the following race condition pattern can still occur

• Session A takes out an UPDLOCK, sees that key 266 does not exist, releases its UPDLOCK, and prepares to insert
• Session B takes out an UPDLOCK, sees that key 266 does not exist, releases its UPDLOCK, and prepares to insert
• Session A runs its  insert
• Session B attempts to run its insert, but fails because of a duplicate key error

We need to hold that lock.

More Revised Code: Higher Locks with UPDLOCK, Lock Duration with HOLDLOCK

We can protect against race conditions like this by telling SQL Server to take out more locks when it processes the existence check and to hold them for the duration of the transaction. Here’s the revised code:

IF OBJECT_ID('dbo.InsertIfNotExists') IS NULL
    EXEC ('CREATE PROCEDURE dbo.InsertIfNotExists as RETURN 0;');
GO
ALTER PROCEDURE dbo.InsertIfNotExists (
    @RaceConditionValue varchar(50)
)
AS
    SET NOCOUNT ON;

    INSERT dbo.RaceConditionTable (RaceConditionValue) 
    SELECT x.newval
    FROM (SELECT @RaceConditionValue as newval) as x
    LEFT JOIN dbo.RaceConditionTable as p WITH (UPDLOCK, HOLDLOCK) on 
        x.newval=p.RaceConditionValue
    WHERE p.RaceConditionValue IS NULL;
GO

Now the LEFT JOIN now has a hint telling SQL Server is taking out an (UPDLOCK) when it reads the row AND to protect that key range for the duration of the transaction (HOLDLOCK). So we’re using Serializable Isolation level and telling it to lock it like we’re modifying, no matter what.

Test Results: UPDLOCK, HOLDLOCK

Re-running OStress.exe, I get nice clean output this time:

No key insert violations! I get the same thing running across 20 threads, 1,000 executions per thread.

There Are Downsides to Taking Out High Locks and Holding Them Longer

Higher locks that are held longer can mean more blocking, and of course that can slow you down. If new rows come in relatively rarely, I probably want to make my code a bit more complex and only take out the higher locks when I really have to. I also want to make sure my indexes are optimized to help me read and lock the smallest range possible for speed. Check out a sample of that scenario in my prior post.