From: SQL Server 2014 Concurrent input issue
Follow up question:
How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we still use (UPDLOCK, SERIALIZABLE
) or another lock?
We have two tables: Order
and OrderLineDetail
. Table Order
is the parent which stores general information; and OrderLineDetail
is the child table, since orders have multiple detail line items.
Orders can be updated, so we first check if OrderId
is present in the table, and insert or update accordingly.
Due to customer modifications, line issue, server busy, etc, the Orders files can be sent more than once. We have a time Lastfiledatetime
column. If the incoming timestamp is older, it has no effect on the table.
For the OrderLineDetail
table, sometimes we don't have natural keys or surrogate keys in our files, so we delete all the child OrderLineDetail
items, and repopulate (this is an xml file older legacy system).
Create Table Orders
(OrderId bigint primary key, -- this is in xml files
Lastfiledatetime datetime null
)
Create Table OrderLineDetail
(OrderLineDetailid bigint primary key identity(1,1), -- this is Not in the xml files
OrderLineDescription varchar(50),
OrderLineQuantity int,
OrderId bigint foreign key references Orders(OrderId),
Lastfiledatetime datetime
)
We work in a multithreading, parallel processing environment, using SQL Server 2016 Read Committed Snapshot Isolation level.
To update the Order
parent, we do this:
BEGIN TRANSACTION;
IF NOT EXISTS
(
SELECT *
FROM Order WITH (UPDLOCK, SERIALIZABLE)
WHERE Order ID=@OrderID
)
INSERT INTO Order () VALUES ()
ELSE
UPDATE Order
SET ...
WHERE OrderID=@OrderID
AND LastFileDatetime<@CreatedTime;
COMMIT TRANSACTION;
New question
How do we Delete and Reinsert the child OrderLineDetail
table in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc?
Do we still use (UPDLOCK, SERIALIZABLE
) or another lock?
BEGIN TRANSACTION;
IF EXISTS
(
SELECT *
FROM OrderLineDetail WITH (UPDLOCK, SERIALIZABLE)
WHERE Order ID=@OrderID
)
DELETE OrderLineDetail
WHERE OrderId=@OrderId
AND LastFileDatetime<@CreatedTime;
INSERT INTO OrderLineDetail () VALUES ()
COMMIT TRANSACTION;