SQL Server Concurrent Inserts and Deletes

Question

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;

Answer 1

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 an UPSERT - update an existing row if it exists; insert a new row otherwise.

...while avoiding racing conditions, deadlocks, etc?

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 from UPSERT 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:

DECLARE @OrderID bigint = 12345;

BEGIN TRANSACTION;

    -- EXTRA STEP
    -- Dummy update with XLOCK to exclusively lock the parent row
    UPDATE TOP (1) dbo.Orders WITH (XLOCK)
    SET Lastfiledatetime = NULL
    WHERE OrderId = @OrderID;

    -- Remove children
    DELETE dbo.OrderLineDetail
    WHERE OrderId = @OrderID;

    -- Remove parent
    DELETE dbo.Orders
    WHERE OrderId = @OrderID;

COMMIT TRANSACTION;

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:

BEGIN TRANSACTION;

    -- The order number we want exclusive access to
    DECLARE @OrderID integer = 12345;

    -- Compute the locking resource string
    DECLARE @Resource nvarchar(255) = N'dbo.Order' + CONVERT(nvarchar(11), @OrderID);

    -- Return code from sys.sp_getapplock
    DECLARE @RC integer;

    -- Build dynamic SQL
    DECLARE @SQL nvarchar(max) =
        N'
        EXECUTE @RC = sys.sp_getapplock
            @Resource = ' + QUOTENAME(@Resource) + N',
            @LockMode = Exclusive, 
            @LockTimeout = -1;'

    -- Try to acquire the lock
    EXECUTE sys.sp_executesql
        @SQL,
        N'@RC integer OUTPUT',
        @RC = @RC OUTPUT;

    -- Do something with the return code value if necessary
    SELECT @RC;

    --- Sensitive operations go here

    -- Release the application lock early if you can
    -- using sys.sp_releaseapplock

ROLLBACK TRANSACTION;

Concurrency is hard; sorry about that.

Answer 2

And utilizing Paul White's answer, luckily I will have less deadlock chance. I will delete the child Orderlinedetails, but will never delete the Parent Order. I will always insert/modify the Parent Order, and then delete and insert the Child Orderlinedetails. I will need to run all transactions in this order.

So my final solution below is keep everything in one transaction,

The (UPDLOCK, SERIALIZABLE) LOCK will escalate into an Exclusive Lock for both insert and modify, (and locks can escalate but never deescalate in transaction) -- So I can insert/modify the Order table with a new Exclusive lock

Then I can delete and insert the child Orderline details (which will also obtain exclusive locks)

I will keep everything in this order. All other transactions will be cut off until this completes, because it has an exclusive lock in transaction.

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT * 
    FROM Order WITH (UPDLOCK, SERIALIZABLE)
    WHERE Order ID=@OrderID
)
    INSERT INTO Order () VALUES ()
ELSE
BEGIN
    UPDATE Order
    SET ... 
    WHERE OrderID=@OrderID 
    AND LastFileDatetime<@CreatedTime
END

DELETE OrderLineDetail
WHERE OrderId=@OrderId
AND LastFileDatetime<@CreatedTime;

INSERT INTO OrderLineDetail () VALUES ()


COMMIT TRANSACTION;