SQL Server Simplify Racing condition query

Question

We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,

I wanted to simplify the following query, so I do not have to select for customerId twice, a local variable is not working in the Exists statement

CREATE TABLE dbo.Customer
(
    RowId bigint IDENTITY(1,1) NOT NULL,
    CustomerId guid NOT NULL,
    Name varchar(255) NOT NULL,
    CONSTRAINT PK_RowId PRIMARY KEY CLUSTERED([RowId] ASC)
)
create unique nonclustered index [UN_CustomerId] ON [dbo].[Customer] ([CustomerId] ASC) include (Name)
create nonclustered index [UN_Name] ON [dbo].[Customer] ([Name] ASC) include (CustomerId)

BEGIN TRANSACTION

IF NOT EXISTS
(
    SELECT top 1 CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
    WHERE Name = @Name
)
BEGIN
    INSERT dbo.Customer(CustomerId, [Name])
    OUTPUT @CustomerId AS CustomerId
    VALUES (@CustomerId, @Name);
END
ELSE
BEGIN
    SELECT CustomerId FROM dbo.Customer WHERE [Name] = @Name;
END

COMMIT TRANSACTION;

Option with local variable not working:

declare @CustomerIdVar uniqueidentifier

BEGIN TRANSACTION

IF NOT EXISTS
(
    SELECT @CustomerIdVar = CustomerId  --- this was causing compilation error
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
    WHERE Name = @Name 
)
BEGIN
    INSERT dbo.Customer(CustomerId, [Name])
    OUTPUT @CustomerId AS CustomerId
    VALUES (@CustomerId, @Name);
END
ELSE
BEGIN
    SELECT @CustomerIdVar 
END

COMMIT TRANSACTION;

Paul White · Accepted Answer · 2018-04-13T02:00:12.863

This is not valid syntax:

IF NOT EXISTS
(
    SELECT @CustomerIdVar = CustomerId  --- this was causing compilation error

You could try:

DECLARE @CustomerIdVar uniqueidentifier;

BEGIN TRANSACTION;

    SELECT @CustomerIdVar = @CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
    WHERE [Name] = @Name

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT dbo.Customer(CustomerId, [Name])
        OUTPUT @CustomerId AS CustomerId
        VALUES (@CustomerId, @Name);
    END;
    ELSE
    BEGIN
        SELECT @CustomerIdVar AS CustomerId;
    END;

COMMIT TRANSACTION;

Or try the merge option I posted in answer to your previous question SQL Server Concurrent Updlock Serializable vs Try Catch.

But these sorts of micro-optimizations are not always as useful as you might think. Test carefully to make sure you actually get better performance.

And avoid nonsense like:

IF NOT EXISTS
(
    SELECT top 1 CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
    WHERE Name = @Name
)

The EXISTS already implies finding at most one row. The top 1 and CustomerId are entirely pointless, and the same as:

IF NOT EXISTS
(
    SELECT *
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
    WHERE Name = @Name
)

SQL Server Simplify Racing condition query

1 Answers1