主页 / dba / 问题 / 312246
Accepted
the Ben B
Asked: 2022-05-18 06:54:46 +0800 CST

最小化表锁的持续时间

  • 772

我有一个需要每天更新的 SQL 表。在更新发生时，可能有也可能没有对该表的查询。它大约有 500,000 行。

当更新表的作业与针对它的查询同时运行时，我们遇到了锁定冲突的问题。

所以我重写了更新表的过程如下：

ALTER procedure [dbo].[Table_Generate] as

declare @d datetime = getdate(), @c as int

--Check temp tables
IF OBJECT_ID('tempdb..#final') IS NOT NULL
    DROP TABLE #final

IF OBJECT_ID('tempdb..#base') IS NOT NULL
    DROP TABLE #base

--Get source data from linked server
select
    ID, 
    Reference, 
    StartDate, 
    EndDate, 
    Description,
    SomeCode
into #base


from    [LinkedServer].[Database].dbo.[A_View]

--Generate row_hash
select 
    ID, 
    Reference, 
    StartDate, 
    EndDate, 
    Description,
    SomeCode,
    hashbytes('SHA2_256',(
     select
        ID, 
        Reference, 
        StartDate, 
        EndDate, 
        Description,
        SomeCode 
     from #base sub where sub.ID = main.ID for xml raw)) as row_hash
    into #final
    from #base main

select @c = count(*) from #final
if @c >0 begin

merge [The_Table_Staging] as target
using #final as source
on source.ID = target.ID

--New rows
when not matched by target then
insert (    RunDate,
            ID, 
            Reference, 
            StartDate, 
            EndDate, 
            Description,
            SomeCode,
            Row_Hash
        ) values (
            @d,
            source.ID, 
            source.Reference, 
            source.StartDate,
            source.EndDate, 
            source.Description,
            source.SomeCode,
            source.row_hash)

--Existing changed rows
when matched and source.row_hash != target.row_hash then update set
     target.RunDate         = @d
    ,target.Reference       = source.Reference
    ,target.StartDate       = source.StartDate
    ,target.EndDate         = source.EndDate
    ,target.Description     = source.Description
    ,target.SomeCode        = source.SomeCode
    ,target.row_hash        = source.row_hash

--Deleted rows
when not matched by source then delete;

--Existing unchanged rows
update [The_Table_Staging] set RunDate = @d where RunDate != @d

--Commit changes
begin transaction

exec sp_rename 'The_Table_Live', 'The_Table_Staging_T'
exec sp_rename 'The_Table_Staging', 'The_Table_Live'
exec sp_rename 'The_Table_Staging_T', 'The_Table_Staging'

commit transaction
end

这个想法是为了减少不必要的行更新，同时也尽量减少对活动表的锁定。我不太喜欢重命名表，但更新/插入需要 5-10 秒，而表重命名几乎是即时的。

所以我的问题是：这种方法可以吗，和/或我可以改进它吗？

谢谢！

编辑以回应 JD：

嗨，JD，请不要道歉 - 我在这里接受建设性的批评。

  1. 我不知道MERGE有问题。我自己从来没有遇到过问题，但是谢谢
  2. 我可能会将这部分重写为单独的INSERT/UPDATE/DELETE语句
  3. 我通常同意。我这样做的原因是，如果我TRUNCATE/INSERT在那个时候从 staging 开始，它需要 6-10 秒，而sp_rename需要不到一秒。所以锁定表的时间更少
  4. 这不会影响表锁定，因为它首先将数据放入临时表。我别无选择，只能使用链接服务器或 SSIS，在这种情况下，我更喜欢链接服务器将所有 SQL 保存在一个地方
  5. 我总是使用XML而不是CONCAT因为否则 'a', 'bc' 的哈希值与 'ab', 'c' 相同，这是不正确的

从登台到填充活动表的所有处理都很好 - 我只想尽量减少最终活动表的锁定时间，

sql-server locking

1 个回答

  1. Best Answer

    不幸的是，我一眼就看到了你的代码中的很多罪恶：

    1. 声明中存在许多已知的错误，MERGE即在生产代码中确实应该完全避免它。

    2. MERGE也被认为不如编写个人INSERT,UPDATE和DELETE语句的表现; 这可能是您的一些阻塞问题的原因。

    3. 虽然很诱人，但使用该sp_rename功能来最小化阻塞问题实际上会导致更严重的阻塞问题，正如 Kendra Little 的为什么你应该切换临时表而不是重命名它们中所讨论的那样。（如果我没记错的话，这讨论了使用分区切换作为更好的解决方案。）

    4. 链接服务器有时也被称为性能瓶颈（用于固定基数估计并在处理之前将所有数据带到网络中）。如果您以前在代码的事务中依赖它，则可以只将该部分保留在事务之外，以最大限度地减少本地表的锁定时间。

    5. 此外，我确实喜欢使用该HASHBYTES()函数来生成行哈希，这是我过去使用过的东西。CONCAT()但是您可能还会发现，通过在每一列中使用安全的列分隔符（例如双管道||）作为参数，而不是在利用 XML 的子查询周围使用它，在行本身上调用它会更高效。

    6. 话虽如此，正如 Erik Darling 所指出的，HASHBYTES()它本身有时也容易受到大规模性能问题的影响。如果这是您的瓶颈，您可以尝试在计算列或索引视图中具体化函数的结果（它是确定性的），或者使用链接帖子中提到的替代方法，例如 CLR。

    • 8

相关问题