SQL Server Concurrent Inserts and Deletes
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 &amp; 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.