SqlRaceConditionSandbox

Delete

SET XACT_ABORT ON
SET NOCOUNT ON

WHILE 1=1
BEGIN
    DELETE tbl_Foo

    WAITFOR DELAY '00:00:00.1'
END

InsertWhereNotExists

SET XACT_ABORT ON
SET NOCOUNT ON

WHILE 1=1
BEGIN
    INSERT  tbl_Foo(A, B)
    SELECT  v.A, v.B
    FROM    (
                VALUES
                (1,0)
            ) v(A, B)
    WHERE   NOT EXISTS (
                SELECT  *
                FROM    tbl_Foo f2
                        --WITH (UPDLOCK)
                        --WITH (HOLDLOCK)
                        --WITH (UPDLOCK, HOLDLOCK)
                WHERE   f2.A = v.A
            )

    IF @@ROWCOUNT <> 0
    PRINT 'y'
END

InsertWhereNotExistsViaMerge

SET XACT_ABORT ON
SET NOCOUNT ON

WHILE 1=1
BEGIN

    MERGE   tbl_Foo
            --WITH (UPDLOCK)
            --WITH (HOLDLOCK)
            --WITH (UPDLOCK, HOLDLOCK)
            AS f
    USING   (
                VALUES
                (1,0)
            ) fNew(A, B)
    ON      f.A = fNew.A
    WHEN NOT MATCHED THEN
            INSERT (A, B)
            VALUES (fNew.A, fNew.B);

    IF @@ROWCOUNT <> 0
    PRINT 'y'
END

tbl_Foo.sql

SET XACT_ABORT ON
SET NOCOUNT ON

DROP TABLE IF EXISTS tbl_Foo

CREATE TABLE tbl_Foo(
    A INT PRIMARY KEY,
    B INT,
)

Upsert

SET XACT_ABORT ON
SET NOCOUNT ON

WHILE 1=1
BEGIN
    BEGIN TRAN
        IF EXISTS (
                SELECT  *
                    FROM    tbl_Foo f
                            --WITH (UPDLOCK)
                            --WITH (HOLDLOCK)
                            --WITH (UPDLOCK, HOLDLOCK)
                    WHERE   f.A = 1
            )
        BEGIN
            UPDATE  x
            SET     x.B = 0
            FROM    tbl_Foo x
            WHERE   x.A = 1
        END
        ELSE
        BEGIN
            INSERT  tbl_Foo(A, B)
            VALUES
            (1,0)
            PRINT 'y'
        END
    COMMIT
END

UpsertViaMerge

SET XACT_ABORT ON
SET NOCOUNT ON

WHILE 1=1
BEGIN

    MERGE   tbl_Foo
            --WITH (UPDLOCK)
            --WITH (HOLDLOCK)
            --WITH (UPDLOCK, HOLDLOCK)
            AS f
    USING   (
                VALUES
                (1,0)
            ) fNew(A, B)
    ON      f.A = fNew.A
    WHEN MATCHED THEN
            UPDATE SET f.B = fNew.B
    WHEN NOT MATCHED THEN
            INSERT (A, B)
            VALUES (fNew.A, fNew.B);

    IF @@ROWCOUNT <> 0
    PRINT 'y'
END