时间戳

首页 > 解决方案 > Parallel merge strategy without deadlock

sql-server - Parallel merge strategy without deadlock

问题描述

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.

标签： sql-servermergesql-server-2016database-deadlocks

解决方案

应用程序锁将序列化尝试运行此过程的会话。它应该如下所示：

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

这个模板中有一些微妙的东西。首先，它没有 catch 块，并且在发生错误时依赖 xact_abort 来释放 applock。并且您希望显式释放应用程序锁，以防在长时间运行的事务的上下文中调用此过程。最后将锁的主体设置为，dbo以便非 dbo 用户无法获得冲突锁。这也需要运行该过程execute as owner，因为应用程序用户通常不会这样做dbo。

推荐阅读