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;