SQL Server – Concurrent Updlock Serializable vs Try Catch

concurrencyinsertsql server

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 hear there are two methods to handle this.
Which is the better way, I heard UPDLOCK, SERIALIZABLE is the standard way. However, try catch prevents checking the additional If statement. Are both ways full proof, and will stop duplicate inserts? What is best coding practice wise, and performs better?

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)

Method 1:

IF NOT EXISTS
(
    SELECT * 
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE) 
    WHERE Name = @Name
)
BEGIN
    INSERT INTO dbo.Customer(CustomerId, Name) VALUES (@CustomerId, @Name)
    SELECT @CustomerId
END
ELSE
BEGIN
    SELECT CustomerId FROM dbo.Customer WHERE Name = @Name
END

Method 2:

BEGIN TRY
    INSERT INTO dbo.Customer(CustomerId, Name) VALUES (@CustomerId, @Name)
    SELECT @CustomerId
END TRY
BEGIN CATCH
    SELECT CustomerId FROM dbo.Customer WHERE Name = @Name
END CATCH

Best Answer

Are both ways full proof, and will stop duplicate inserts?

Method 2 is not safe under concurrency as written. There is no guarantee that the row that caused the insert to fail will continue to exist when the select in the catch clause runs.

In addition, the catch clause could execute for errors other than a duplicate key violation, because the code does not check the error number.

You should also be aware of the potential for a doomed transaction.

Aaron Bertrand wrote about the overhead of try/catch. The overhead is usually higher than checking first.

What is best coding practice wise, and performs better?

Method 1 is a common pattern, but needs a transaction to be safe. Performance depends on local factors, so you should conduct your own testing. As a side note, you can avoid one query by using the output clause instead:

DECLARE 
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

BEGIN TRANSACTION;

    IF NOT EXISTS
    (
        SELECT * 
        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;

As an alternative, you may want to compare the performance of a safe merge solution:

DECLARE 
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

MERGE dbo.Customer WITH (SERIALIZABLE) AS C
USING (VALUES(@CustomerId, @Name)) AS I (CustomerId, [Name])
    ON I.Name = C.Name
WHEN NOT MATCHED 
    THEN INSERT (CustomerId, [Name])
    VALUES (I.CustomerId, I.[Name])
WHEN MATCHED THEN UPDATE 
    SET @CustomerId = C.CustomerId,
        @Name = C.[Name]
OUTPUT @CustomerId AS CustomerId;
Related Question