SQL Server Concurrent Insert/Update and Storing Data

Question

From: SQL Server 2014 Concurrent input issue

Follow up question:

Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have multiple line items.

Orders can be updated, so we first check if OrderLineID is present in the table, and insert or update accordingly.

Due to customer modifications, line issue, server busy, etc, the OrderLineId can be sent more than once. We have a time Lastmodified column. If the incoming timestamp is older, it has no effect on the table. We work in a multithreading, parallel processing environment. Utilize SQL 2016 Read Committed Snapshot Isolation level.

Previous question gave this response:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT * 
    FROM OrderLine WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orderline () VALUES ()
ELSE
    UPDATE Orderline
    SET ... 
    WHERE OrderLineID=@OrderLineID 
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

New question: Sometimes I do not have the OrderLineId initially, so we search by customer LastName, FirstName and ProductDescription and other columns (we have an old legacy system, don't bother explaining). I want to validate setting variables during these transactions will not affect anything. I will need the @OrderLineId for a future sql item, and wanted to hold value. I executed sp_lock to test, seems to work. I may just save the value after transaction is complete, but did not want to relook it up again, for optimization purpose.

Method 1: Save variable all at once

begin transaction

declare @OrderLineId int

set @OrderLineId = 
( SELECT OrderLineId 
  FROM Orders WITH (UPDLOCK, SERIALIZABLE)
  WHERE LastName = @LastName
  AND FirstName = @FirstName
  AND WebLoginDate = @WebLoginDate
  AND ProductName = @ProductName )

IF @OrderLineId is null
BEGIN
   INSERT INTO Orderline () VALUES ()
   SET @OrderLineId = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE Orderline 
    SET ... 
    WHERE OrderLineID=@OrderLineID 
    AND LastModified<@CreatedTime;
END
COMMIT TRANSACTION;

Method 2: is this method safer?

BEGIN TRANSACTION;

declare @OrderLineId int
IF NOT EXISTS
(
    SELECT * 
    FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
    WHERE LastName = @LastName
    AND FirstName = @FirstName
    AND WebLoginDate = @WebLoginDate
    AND ProductName = @ProductName
)
BEGIN
   INSERT INTO Orderline () VALUES ()
   SET @OrderLineId = SCOPE_IDENTITY()
END
ELSE
BEGIN

set @OrderLineId = 
(  SELECT OrderLineId WITH (UPDLOCK, SERIALIZABLE)
   FROM Orderline
   WHERE LastName = @LastName
   AND FirstName = @FirstName
   AND WebLoginDate = @WebLoginDate
   AND ProductName = @ProductName) 

    UPDATE Orderline 
    SET ... 
    WHERE OrderLineID=@OrderLineID 
    AND LastModified<@CreatedTime;
END
COMMIT TRANSACTION;

Answer 1

The updated scripts in your question are equally safe.

Expanding on the answer to your other question, you could use the natural key in both WHERE clauses and assign the @OrderLineID variable in the UPDATE statement. Perform the conditional insert first if that is the most common case, otherwise perform the update first. This method will eliminate the SELECT query.

BEGIN TRANSACTION;

DECLARE @OrderLineId int;
INSERT INTO Orderline (...)
    SELECT ...
    WHERE NOT EXISTS (
        SELECT * 
        FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
        WHERE LastName = @LastName
            AND FirstName = @FirstName
            AND WebLoginDate = @WebLoginDate
            AND ProductName = @ProductName
    );
IF @@ROWCOUNT > 0
BEGIN
    SET @OrderLineId = SCOPE_IDENTITY();
END
ELSE
BEGIN
    UPDATE Orderline 
    SET
         ...
         , @OrderLineId = OrderLineId
    WHERE LastName = @LastName
        AND FirstName = @FirstName
        AND WebLoginDate = @WebLoginDate
        AND ProductName = @ProductName
        AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

Be aware that when you mix using the natural key and surrogate key to locate rows, deadlock likelihood is increased.