Revisions to SQL Server Concurrent Updlock Serializable vs Try Catch

deleted 9 characters in body

Source Link

edited Aug 18 at 11:32

90.3k
30
424
663

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, UPDLOCK) 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;

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, UPDLOCK) 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;

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;

deleted 3 characters in body

Source Link

edited Aug 18 at 6:25

Paul White ♦

90.3k
30
424
663

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 doomed transaction.

Aaron Bertrand wrote about the overhead 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, UPDLOCK) 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;

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;

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, UPDLOCK) 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;

changed to slight mod in question, Also had include indexes in real code, did not write to reduce complexity, but learn mistake, Thanks for all the help, this answer is great

Source Link

edit approved Apr 13, 2018 at 1:02

user129291

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 CustomerIDName = @CustomerId@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;

Your current indexes are not ideal for the code presented. Ensure they are covering to avoid unnecessary lookups. For example:

create nonclustered index [UN_Name] 
ON [dbo].[Customer] ([Name] ASC)
INCLUDE (CustomerId);

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.CustomerIdName = C.CustomerIdName
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;

For that solution, the customer id index should be made covering:

create unique nonclustered index [UN_CustomerId] 
on [dbo].[Customer] ([CustomerId] ASC)
INCLUDE ([Name]);

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 CustomerID = @CustomerId
    )
    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;

Your current indexes are not ideal for the code presented. Ensure they are covering to avoid unnecessary lookups. For example:

create nonclustered index [UN_Name] 
ON [dbo].[Customer] ([Name] ASC)
INCLUDE (CustomerId);

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.CustomerId = C.CustomerId
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;

For that solution, the customer id index should be made covering:

create unique nonclustered index [UN_CustomerId] 
on [dbo].[Customer] ([CustomerId] ASC)
INCLUDE ([Name]);

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;

Added local variable example

Source Link

edited Apr 13, 2018 at 0:14

Paul White ♦

90.3k
30
424
663

Loading

Source Link

created Apr 12, 2018 at 18:39

Paul White ♦

90.3k
30
424
663

Loading

Stack Exchange Network

Return to Answer