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;
Best Answer
The
UPDLOCK, SERIALIZABLE
pattern is all about avoiding incorrect results (including false key violation errors) due to race conditions, when performing a particularly common operation known as anUPSERT
- update an existing row if it exists; insert a new row otherwise.You seem to be looking for a magic combination of hints that will allow a database system to perform highly concurrent operations without conflict. In general, there is no such thing. The only way to avoid deadlocks completely is to always modify objects in the same order.
This can be difficult, even impossible, to achieve in a foreign key relationship. Consider that when inserting a new object, you must add the parent row before the child row(s). When deleting an object, you must remove the child(ren) before the parent.
This is not to say that careful locking will not still prevent race conditions, but it cannot guarantee to avoid deadlocks in all situations.
As far as the question goes, yes, using the
UPDLOCK, SERIALIZABLE
hint will protect fromUPSERT
race conditions, but no, it will not prevent deadlocks. It may even contribute to increasing their frequency. This is tricky to assess in advance, and even experienced SQL Server database designers can get it wrong.For more complex requirements involving multiple objects, a common solution is to always explicitly exclusively lock the parent row before modifying the children, even where the natural order is to process the child first.
For example, when deleting an Order:
The extra step of exclusively locking the parent row will help eliminate deadlocks, if all modifications follow the same modification order: parent first, then child(ren). If any process accesses objects in the reverse order, the possibility of a deadlock due to incompatible locks occurs.
Note that can be important to actually modify something in the parent row, since SQL Server may not honour the exclusive lock (
XLOCK
) in some situations, if it can tell that it is not necessary for the operation being performed.A second implementation of roughly the same idea is to use application locks, as mentioned in the Q & A Implementing application locks within SQL Server (Distributed Locking Pattern) (see the documentation links there).
This is easier in some respects, but again, you do have to ensure that all code that modifies the protected objects uses the same scheme. As soon as anything can access the underlying object(s) without taking the required application lock(s), the whole scheme breaks down.
An example below shows how we might take an exclusive application lock on a particular order number before processing elements of the order:
Concurrency is hard; sorry about that.