如何在合并和备份期间锁定源表以阻止插入?

huangapple go评论58阅读模式
英文:

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

问题

执行合并操作的代码,确保备份和清理源数据表,将源表锁定以防止更改(插入、删除等)。所用表名为poc_a、poc_b、poc_c,结构相同,包括[id, w, p, TimeStamp]这四列。合并操作基于w和p列进行分区,并保留最新的一行数据。匹配时更新目标表,不匹配时插入新行。备份源表数据到poc_b,然后清空poc_a表。

英文:

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.

= Source.

, Target.[TimeStamp] = Source.[TimeStamp] WHEN NOT MATCHED BY Target THEN insert ([id],[w],

,[TimeStamp]) values(Source.id,Source.w,Source.p,Source.[TimeStamp]) ; --WAITFOR DELAY &#39;00:00:15&#39; insert into poc_b select * from poc_a --will there be data loss here or below truncate? truncate table poc_a commit tran merg

答案1

得分: 1

你可以在开始事务前添加这行代码:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

这将使合并仅读取在合并事务开始之前已提交的数据,这不会锁定用于插入的表,但合并事务在其他事务提交之前不会获取这些更改。

如果你想完全锁定你的事务使用的表,你可以执行:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

这是可能的最高隔离级别,将完全锁定表,直到你的合并事务被提交。

英文:

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.

huangapple
  • 本文由 发表于 2023年4月20日 00:37:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/76056918.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定