How to lock a source table to block inserts during merge and backup?

Question

To perform a merge from a source table which gets updated continuously (ex: like every 1min) as well as copying the data from the source table to a backup table and then to delete the source table. Basically the data on which merge occurred should be backed up and cleaned up from source. The source table should be locked so that no changes (inserts,deletes etc) are made.

Assume the tables are called poc_a, poc_b, poc_c all with same structure [id,w,p,TimeStamp<-- 4 columns] I want to do something like this:

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 -- filter first row from N duplicate rows
    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 --will there be data loss here or below truncate?
truncate table poc_a

 commit tran merg

Answer 1

You could add this line before begin transaction

SET TRANSACTION ISOLATION LEVEL READ COMMITED;

Which will make merge read ONLY data that was commited before the merge transaction started, this will not lock the table for insterts, but the merge transaction will not acquire those changes before other transactions are commited.

If you want to completely lock access to a table that your transaction is using you could do

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Which is the highest level of isolation possible and will completely lock table for other transactions until your merge transaction is commited.