合并数据并备份时，如何锁定源表以阻止写入操作？

需求背景 #

需要对持续更新（如每分钟一次）的源表poc_a执行以下操作，全程需锁定poc_a禁止任何变更，确保数据不丢失：

1. 将poc_a数据按w、p分区去重（保留每组最新TimeStamp的行）后，MERGE到目标表poc_c
2. 将poc_a的所有数据备份到poc_b
3. 清空poc_a

现有SQL代码 #

begin tran merg
MERGE poc_c as Target
USING (
    
    select id,w,p,[TimeStamp] from (
     SELECT     id,w,p,[TimeStamp],ROW_NUMBER() OVER (PARTITION BY w, p 
       ORDER BY [TimeStamp] DESC) AS RN 
     FROM poc_a with (tablockx) 
                 ) DUPL_FILTER -- 从N条重复行中筛选第一行
    WHERE RN = 1 
     ) Source
ON
  Source.w = Target.w   AND   Source.p = Target.p 
  
  WHEN MATCHED THEN 
  update set
    Target.[id]       = Source.[id],
    Target.[w]       = Source.[w],
    Target.[p]            = Source.[p],
    Target.[TimeStamp]       = Source.[TimeStamp]

WHEN NOT MATCHED BY Target THEN
    insert ([id],[w],[p],[TimeStamp])   values(Source.id,Source.w,Source.p,Source.[TimeStamp])
  ;
  --WAITFOR DELAY '00:00:15'

insert into poc_b select * from poc_a --此处或后续truncate操作是否会存在数据丢失？
truncate table poc_a

 commit tran merg

数据丢失风险判断 #

现有代码不会导致数据丢失，核心依据如下：

• 事务原子性保障：所有操作（MERGE、备份插入、TRUNCATE）都在同一个事务中，要么全部执行成功，要么全部回滚。如果任何一步失败，poc_a的数据都会保留，不会出现部分执行导致的丢失。
• 排他锁阻止外部写入：在MERGE的USING子查询中，poc_a WITH (TABLOCKX)会给poc_a加上排他表锁，该锁会持有到事务提交为止，期间任何外部会话无法对poc_a执行插入、更新、删除操作。因此MERGE、备份、TRUNCATE操作的都是同一批数据，不会出现新数据遗漏的情况。
• 备份先于清空：备份操作insert into poc_b select * from poc_a在TRUNCATE之前执行，且处于同一锁保护下，确保备份的是完整的源表数据，之后再清空poc_a，不会丢失需要备份的内容。

优化建议 #

1. 冗余更新去除：MERGE的UPDATE语句中更新了w和p字段，但这两个字段是ON条件的匹配字段，匹配时Source.w与Target.w、Source.p与Target.p必然相等，更新操作完全冗余，建议删除以提升性能：

   WHEN MATCHED THEN 
   update set
     Target.[id]       = Source.[id],
     Target.[TimeStamp]       = Source.[TimeStamp]
   

2. 提前锁定表：可以在事务开头就给poc_a加上排他锁，避免MERGE子查询执行前的短暂窗口可能出现的写入（虽然概率极低，但更严谨）：

   begin tran merg
   -- 提前锁定poc_a
   SELECT TOP 1 1 FROM poc_a WITH (TABLOCKX)
   -- 后续MERGE、备份、TRUNCATE操作
   

3. 审计日志可选：如果需要记录MERGE的具体操作，可以添加OUTPUT子句将变更记录到日志表，方便后续审计：

   MERGE poc_c as Target
   USING (...) Source
   ON (...)
   WHEN MATCHED THEN UPDATE ...
   WHEN NOT MATCHED THEN INSERT ...
   OUTPUT $action, inserted.*, deleted.* INTO poc_merge_log; -- 新增日志输出
   

内容的提问来源于stack exchange，提问作者Ashwin Mohan