Parallel merge strategy without deadlock

Question

Using SQL Server 2016, I wish to merge data from a SourceTable to a DestinationTable with a simple procedure containing a simple insert/update/delete on the same table.

The SourceTable is filled by several different applications, and they call the MergeOrders stored procedure to merge their uploaded rows from SourceTable into DestinationTable.

There can be several instances of MergeOrders stored procedure running in parallel.

I get a lot of lock, but that's normal, the issue is that sometimes I get "RowGroup deadlocks", which I cannot afford.

What is the best way to execute such merge operation in this parallel environment.

I am thinking about TABLOCK or SERIALIZABLE hints, or maybe application locks to serialize the access, but interested if there is better way.

Answer 1

An app lock will serialize sessions attempting to run this procedure. It should look like this:

create or alter procedure ProcWithAppLock 
with execute as owner
as
begin
  set xact_abort on;
  set nocount on;
  begin transaction;
  declare @lockName nvarchar(255) = object_name(@@procid) + '-applock';
  exec sp_getapplock @lockName,'Exclusive','Transaction',null,'dbo';


  --do stuff
  waitfor delay '00:00:10';
  select getdate() dt, object_name(@@procid);

  exec sp_releaseapplock @lockName, 'Transaction', 'dbo';
  commit transaction;
end

There are a couple of subtle things in this template. First off it doesn't have a catch block, and relies on xact_abort to release the applock in case of an error. And you want to explicitly release the app lock in case this procedure is called in the context of a longer-running transaction. And finally the principal for the lock is set to dbo so that no non-dbo user can acquire a conflicting lock. This also requires that the procedure be run with execute as owner, as the application user would not normally be dbo.